Lumen: how to get subcategory lists using Eloquent?

Question

I have the following database tables

• Cities
• Streets
• House

They have one, many relationships with each other, so Towns can have many Streets and Streets can have several Houses on it.

I have a Town identifier, and I just want to get a list of all Houses in the city from it. So at the moment I'm doing it.

 //This gives me streets and houses in nested arrays $towns = Town::with(['streets', 'streets.houses' => function($query) { $query->where('active', 1) }])->find($town_id); //But I only want a list of houses so I do $houses = $towns->streets->map(function($street) { return $street->houses; }); 

This works, but is there a better way to do this? It seems to me that I should just get a list of houses using only the eloquent one. After that, the incorrect display seems incorrect.

Answer 1

You are right that lazy loading is inefficient in this case.

You just need a list of houses, I would just create the right query with joints.

Here is an example:

 House::query() ->join('street', 'house.id', 'street.id') ->join('town', 'street.town_id', '=', 'town.id') ->where('town.id', $town_id) ->get(); 

Underline as necessary.

Sometimes it makes sense to use the query designer over eloquence.