Iterating over joined query data in Laravel Lumen

Question

Categories and courses are two tables. Based on Category, an 'n' number of data can be inserted into the course. While fetching the API response, it should be like below.

Controller

public function catlist($catid)
{
    $category = 
    DB::table('courses')
    ->join('categories', 'courses.category_id', '=', 'categories.id')// joining the Categories table , where category_id and categories id are same
    ->select(
    'categories.id as catid',
    'categories.name as catname',
    'courses.id as courseid',
    'courses.title as coursename'
    )
    ->where('courses.category_id', '=', $catid)
    ->get();

    foreach ($category as $cat) 
    {
        $data = [
            'category'=>[
                "id" => $cat->catid,
                "name" => $cat->catname,
                ],
            'courselist'=>[
                "id" => $cat->courseid,
                "name" => $cat->coursename,
            ]
        ];
    }
    return response()->json([
        'success' => true,
        'data' => $data,
    ],200);
} 

The actual result is:

  "data": {
    "category": {
        "id": 1,
        "name": "Test Category"
    },

    "courseList": {
        "id": 2,
        "title": "Course 2"
    }
}

Expected results will be:

data=[
    category: {
      id: 1,
      name: "Test Category"
      },

      courseList: [
      {
        id: 1,
        title: "Course 1",
      },
      {
        id: 2,
        title: "Course 2",
      },
    ],
]

Answer 1

I found myself after doing a lot of search. Now it is working now.

   $category = DB::table('categories')->where('id', $catid)->first();
    // Retrieve course list
    $courseList = DB::table('courses')->whereIn('category_id', [$catid])->get();

    if ($category === null) 
    {
        return response()->json([
            'success' => false,
            'message' => 'No such a Category',
        ], 404);
    }else{
        // Construct the response array
        $data = [
            'category' => $category,
            'courseList' => $courseList,
        ];
    
        return response()->json([
            'success' => true,
            'data' => $data,
        ],200);
    }

Answer 2

The query you have is returning a row for each course and category, you should use either normal laravel relationships, or you need to use the sql groupBy categories.id method on your query.

https://laravel.com/docs/11.x/eloquent-relationships