Search code examples
laravelphpdebugbar

Laravel - Query being run twice

I'm making a wedding planning application where users can add their guests to certain tables, groups, etc...

I'm trying to list all the groups and inside each group show every guest on that group(in the same page like and accordion)

Seems easy enough, and it works but while using DebugBar I can see it's making 2 queries that are exactly the same and I can't for the life of me find out why.

An Event can have many groups and each group can have many guests.

$event = Event::find($id_event);
$event->load('group');
$event->group->load([
    "guest" => function ($q) {
       return $q->paginate(10);
    }
]);

The result: duplicate queries

Edit

Forgot to mention, if I add any other relations to this it's will also duplicate them.

$event->group->load([
    'guest.plusone',
    "guest" => function ($q) {
       $q->paginate(10);
    }
]);

Edit 2

In trying to do what you suggested I found what causes it, but still don't know why.

When I try to do what you suggested I get the same outcome

$event->load(['group', 'group.guest' => function ($query) {
    $query->paginate(10);
}]);

But if I remove the callback for the pagination I only get one query. Is this normal? I know pagination does one extra query to get the total results but in this case it's making 3 queries and 2 of them are exactly the same

Solution

  • First of all for what you are doing, the result of 2 queries is very expected. See below for the explanation:

    Explanation:

    $event = Event::query()->find($id_event);
$event->load(['group.guest' => fn ($with) $with->paginate(10)]);

    when calling paginate you are forcing a query to be run, the same query that has been built by the relation definitions up to that point. The same would happen calling $with->get(). But the closure does not expect you to run a query, nor does it care for that matter, only modify the query provided within. So after a query runs via paginate, note that it's result is not used anywhere, the closure ends and Laravel runs the relationship eager loading query as it normally would.

    For Laravel 11:

    for what you want to achive, limit the loaded relation models to 10 per group, you need to use a limit. Limit does not run a query, but modifies the query object to include a limit when it runs.

    $event = Event::query()->find($id_event);
$event->load(['group.guest' => fn ($with) $with->limit(10)]);

    Before Laravel 11:

    while you can do the same in Laravel 10, the result you will get is not the same. The limit will apply to the whole loaded guests, not per group. So if you have group A and B with 10 guests each. The resulting data would include the 10 guests of group A but 0 guests on group B. This is because only 10 guests where loaded in total, not 10 for each group.

    $event = Event::query()->find($id_event);
$event->load(['group.guest' => fn ($with) $with->limit(10)]);

    To achieve the same functionality you need to include https://github.com/staudenmeir/eloquent-eager-limit into your project and follow it's instructions. (In Laravel 11 this package has been merged)