Convert Laravel query to Eager Loading

Question

I've been leaning Laravel and trying to implement something and I did, it worked but I heard that it might be possible to do the same in an easier way using Eager Loading.

Imagine that we have 4 tables: garages, cars, securities, places.

garages is where you can find the cars, securities is the security that is keeping that car safe inside of a garage, and places is where you can find garages similar to that one.

What I want is to list garages and join the three tables that is cars, securities and places like this:

Garage 1 has 2 cars with 3 securities and has 3 more garages similar
Garage 2 has 1 cars with 1 securities and has 2 more garages similar

And here is the query:

select 
    g.garage_name, 
    count(distinct c.car_id) as count_cars,
    count(distinct s.security_id) as count_securities,
    count(distinct p.place_id) as count_places
from garages g
left join cars c on c.garage_id = g.garage_id
left join securities s on s.car_id = c.car_id
left join places p on p.garage_id = g.garage_id
group by g.garage_name
order by g.garage_name;

It's working, as you can see here.

I converted it to:

$garages = Garages::select('garage_name',
DB::raw('count(distinct cars.car_id) as count_cars'),
DB::raw('count(distinct securities.security_id) as count_securities'),
DB::raw('count(distinct places.place_id) as count_places'))
->leftJoin('cars', 'cars.garage_id', '=', 'garages.garage_id')
->leftJoin('securities', 'securities.car_id', '=', 'cars.car_id')
->leftJoin('places', 'places.garage_id', '=', 'garages.garage.id')
->groupBy('garages.garage_name')
->orderBy('garages.garage_name')
->get();

As I said above, it's working but I'm wondering if there's an easier way for doing this using Eager Loading and how to convert?

When I say easier I mean more readable, separated, the right way instead of that big query.

Raftalks · Answer 1 · 2014-12-13T21:48:32.590

Lets say you do have the 4 Eloquent Model classes for the entities. If that's the case, you can try eager loading through the Eloquent ORM.

$Garages = Garage::with(['cars','places', 'securities'])->get();

Then you may prepare the necessary output as you like using the returned result set which is a collection (Illuminate\Support\Collection). You can transform the items in a collection as any way you like. Here is an example.

In your case, I am assuming this would be the way to do it.

$Garages->transform(function($garage) {
   return array(
       'garage_name' => $garage->garage_name,
       'count_cars' => $garage->cars->count(),
       'count_securities' => $garage->securities->count(),
       'count_places' => $garage->places->count(),
       // add as many custom details you want here
   );
});

return $Garages->toArray(); // see the output

Now I hope you got the idea.

Keep in mind that your model class files should have the relationships. It should be like the following.

class Garage extends Eloquent {

   public function cars() {
     return $this->hasMany('Car');
   }

   public function securities()
   {
     return $this->hasMany('Security');
   }

   public function places()
   {
     return $this->hasMany('Place');
   }

}

class Car extends Eloquent {

  // relationships here
}

class Security extends Eloquent {

  // relationships here
}

class Place extends Eloquent {

  // relationships here
}

This is just a simple example to help you understand.

Enjoy ! :D

It returns the total of `cars` and `places` but `securities` still always in 0. — yayuj, Dec 14 '14 at 01:58
I solved it myself using `hasManyThrough`. - But now I got an error when I pass the array to a blade template: `Trying to get property of non-object`. How to solve this? — yayuj, Dec 14 '14 at 02:41
Try debug through the object collection to see if the relations are loaded, the error tells you that whatever the object->property that you are trying to get is not available on the object. — Raftalks, Jan 12 '15 at 13:20

Convert Laravel query to Eager Loading

1 Answers1