Lumen: how to get sub table lists using Eloquent?

Question

I have the following database tables

• Towns
• Streets
• Houses

They have a one to many relationships with each other so Townscan have many Streets and Streets can have multiple Houses on it.

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

//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 of doing this? I feels as though I should just be able to get the list of houses using just eloquent. Having to do some mapping afterwards doesn't seem right.

Answer 1

You are right that lazy loading is not efficient in this case.

You just need the list of houses, I would just build the proper 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();

Tweak as needed.

Sometimes it makes more sense to use query builder over eloquent.