Laravel Question posted in
The official Laravel Documentation can be found here.

Iterating over joined query data in Laravel Lumen

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",
      },
    ],
]
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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);
    }

  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search