CODEWITHSUNDEEP
laraveleloquent
daninthemix
daninthemix

Reputation: 2570

Eager loading not working

The purpose of eager loading is to reduce the number of DB queries, if I'm not mistaken. But if I add the following to my query builder instance, it generates an additional SQL query for each record returned:

        ->with([
            'firstEarmark' => function($q) {
                $q
                ->select('earmarks.*')
                ->join('locations', 'locations.id', '=', 'earmarks.location')
                ->select('earmarks.*', 'locations.location AS earmarked_location')
                ->where('date', '>=', date('m/d/Y'))->orderBy('date', 'asc')
                ->get();
            }

It does this with or without the join statement.

So am I missing the point of eager loading, or am I doing it wrong?

My second (slightly unrelated) problem is that, if I include the commented ->select() statement, this sub query produces no results for some reason.

In fact, Laravel is generating the same SQL query for each record. If there are two Laptop results, I get two identical queries to pull the first Earmark record for each:

      113 Prepare   select `earmarks`.*, `locations`.`location` as `earmarked_location` from `earmarks` inner join `locations` on `locations`.`id` = `earmarks`.`location` where `earmarks`.`laptop_id` in (?, ?) and `date` >= ? order by `date` asc
      113 Execute   select `earmarks`.*, `locations`.`location` as `earmarked_location` from `earmarks` inner join `locations` on `locations`.`id` = `earmarks`.`location` where `earmarks`.`laptop_id` in (1, 2) and `date` >= '11/04/2016' order by `date` asc
      113 Close stmt    
      113 Prepare   select `earmarks`.*, `locations`.`location` as `earmarked_location` from `earmarks` inner join `locations` on `locations`.`id` = `earmarks`.`location` where `earmarks`.`laptop_id` in (?, ?) and `date` >= ? order by `date` asc
      113 Execute   select `earmarks`.*, `locations`.`location` as `earmarked_location` from `earmarks` inner join `locations` on `locations`.`id` = `earmarks`.`location` where `earmarks`.`laptop_id` in (1, 2) and `date` >= '11/04/2016' order by `date` asc

Those queries are identical!

Upvotes: 0

Views: 332

Answers (1)

Eric Tucker
Eric Tucker

Reputation: 6345

It's generating multiple queries because of your get() at the end of the subquery which executes it. You don't execute subqueries in Laravel as they will be executed after the first query is ran to attach the relationships. Replace that with take(1) and wit will solve your N+1 issue.

Upvotes: 1

Related Questions