Laravel reducing queries

Question

I use Laravel Eloquent and I have this code:

<?php
$bulk = Bulk::where('id', 'bulkid1');
echo $bulk->info;
foreach($bulk->models as $model) {
  echo $model->info;
  $lastreport = $model->reports()->getQuery()->orderBy('created_at', 'desc')->first();
  echo $lastreport->message;
}    
?>

What I want to achieve is that the $lastreport is preloaded. In this piece of code, the query will be executed too many times (every bulk has 1000 models, resulting in 1000 subqueries). While in plain sql I could just do:

SELECT * FROM bulk
LEFT JOIN models ON bulk.id = models.bulk_id
LEFT JOIN ( SELECT *, MAX(created_at) AS created_at
   FROM
     reports
   GROUP BY
     model_id )
lastreport ON models.id = lastreport.model_id
WHERE bulk.id = 'bulkid1'

Database pseudocode:

TABLE bulks
 id, info

TABLE models
id, bulk_id, info

TABLE reports
id, model_id, message

You mean the relations? 1 bulk has * models and every model has * reports. I added some pseudocode. — sridesmet, Jan 21 '19 at 13:26

score 3 · Accepted Answer · answered Jan 21 '19 at 13:38

This is the N+1 selects problem The laravel solution for this problem is eager loading

In your case you'd do:

<?php
$bulk = Bulk::with(['models', 'models.reports' => function ($query) { 
      return $query->orderBy('created_at', 'desc');           
   } 
])->where('id', 'bulkid1')->first();
echo $bulk->info;
foreach($bulk->models as $model) {
  echo $model->info;
  $lastreport = $model->reports->first();
  echo $lastreport->message;
}

This should ensure that (a) all models are loaded with only 1 additional query and (b) all model reports are loaded with another additional query. The downside with this is that there are more data being loaded than necessary because of the orderBy clause which can't really be represented as a query time condition.

Thank you for your quick answer. This is exactly what i'm looking for! — sridesmet, Jan 21 '19 at 13:46

Laravel reducing queries

1 Answers1