Laravel's ORM query using with() not working with paginate()

The problem here is with eager loading—you’re trying to use it in a way it wasn’t designed for :). Let me explain what’s happening behind the scenes with eager loading in Laravel.

When you use with for eager loading, Laravel does two things:

1. Fetch Parent Models
   First, it runs a query to get all the parent models (in this case, departments):

   SELECT * FROM departments WHERE user_id = ?;
   

2. Fetch Related Models with ids from all parent models
   Then it runs another query to grab all the related models (meetings), but for all the parent models combined:

   SELECT * FROM meetings WHERE department_id IN (1, 2, 3); -- 1,2,3 are obteined by using something like $ids = array_map(function ($d) { return $d->id; }, $departments);
   

This is efficient for loading all related meetings in one go. However, it doesn’t support pagination per department because Laravel is fetching everything in a single query for all the departments. Pagination requires limiting the query with something like:

SELECT * FROM meetings WHERE department_id = 1 LIMIT 10 OFFSET 0;

But eager loading can’t do this for each department individually—it fetches everything in bulk. That’s why what you’re trying to do isn’t possible with eager loading.

Solution 1: Paginate Manually Using map

Since eager loading doesn’t work for this use case, you can handle pagination manually for each department. Here’s how:

'auth' => [
    'user' => $request->user(),
    'departments' => $request->user()
        ? $request->user()->departments->map(function ($department) {
            $meetings = $department->meetings()->paginate(10); // Paginate meetings manually
            return [
                'id' => $department->id,
                'department' => $department->name,
                'meetings' => $meetings->items(),
                'pagination' => [
                    'current_page' => $meetings->currentPage(),
                    'last_page' => $meetings->lastPage(),
                    'per_page' => $meetings->perPage(),
                    'total' => $meetings->total(),
                ],
            ];
        })
        : [],
],

Now, each department gets its own paginated meetings list, along with pagination metadata.

But… Here’s the Catch (N+1 Query Problem)

This solution works fine if you only have a few departments. But if you have a lot of departments, you’ll run into the N+1 query problem.

Here’s why:

• 1 Query: Fetch all departments:

  SELECT * FROM departments WHERE user_id = ?;
  

• N Queries: For each department, run a query to fetch its meetings:

  SELECT * FROM meetings WHERE department_id = 1 LIMIT 10 OFFSET 0;
  SELECT * FROM meetings WHERE department_id = 2 LIMIT 10 OFFSET 0;
  SELECT * FROM meetings WHERE department_id = 3 LIMIT 10 OFFSET 0;
  ...
  

So if you have 100 departments, you’ll run 101 queries. That’s fine for small datasets, but it’s going to hurt performance if you have a lot of departments.

Solution 2: Use SQL Window Functions (Avoid N+1)

If you have a lot of departments, you can optimize this by fetching the first 10 meetings for all departments in a single query using SQL window functions.

Here’s how it works:

SQL Query

WITH ranked_meetings AS (
    SELECT 
        m.*, 
        ROW_NUMBER() OVER (PARTITION BY m.department_id ORDER BY m.start_date ASC) AS rn
    FROM 
        meetings m
    WHERE 
        m.department_id IN (SELECT id FROM departments WHERE user_id = ?)
)
SELECT * FROM ranked_meetings WHERE rn <= 10;

• What’s Happening:
  • ROW_NUMBER() assigns a rank to each meeting within its department, ordered by start_date.
  • rn <= 10 ensures we only get the first 10 meetings for each department.

Laravel Implementation for your case

1. Fetch Departments:

   $departments = $request->user()->departments;
   

2. Fetch Meetings with SQL:
   Use a raw query to get the top 10 meetings per department:

   $rankedMeetings = DB::select("
       WITH ranked_meetings AS (
           SELECT 
               m.*, 
               ROW_NUMBER() OVER (PARTITION BY m.department_id ORDER BY m.start_date ASC) AS rn
           FROM 
               meetings m
           WHERE 
               m.department_id IN (SELECT id FROM departments WHERE user_id = ?)
       )
       SELECT * FROM ranked_meetings WHERE rn <= 10
   ", [$request->user()->id]);
   

3. Group Meetings by Department:

   $meetingsByDepartment = collect($rankedMeetings)->groupBy('department_id');
   

4. Combine Results:

   $departments = $departments->map(function ($department) use ($meetingsByDepartment) {
       return [
           'id' => $department->id,
           'department' => $department->name,
           'meetings' => $meetingsByDepartment[$department->id] ?? [],
       ];
   });
   

Now it depends on whether you can afford n+1 query or not.