I have read several posts on why eager loading uses multiple queries rather than joining here, here, and here. However, I have a situation that I think warrants using a join instead of eager loading. How would I construct the query so that I can access the sub-object as if it were a normally eager loaded object?
Example: Getting people that live on a certain street, and loading that address at the same time. I don't want to load all the person's addresses, incase he lives at many places; just the places that match the query. With eager loading and whereHas I have to duplicate the query, and it's not always this simple of a query:
People::with(['address', function($query) use ($street_name){
return $query->where('street_name', $street_name);
}])->whereHas('address', function($query) use ($street_name){
return $query->where('street_name', $street_name);
})->get();
could be
People::query()->join('address', function($join){
$join->on(...)
})->where('street_name', $street_name);
The problem then is that I cannot use this:
$person->address
Is there a way to take the join and massage it (using selects?) so that it appears like it was eager loaded?
You could do the following:
People::join('addresses', 'addresses.people_id', '=', 'people.id')
->selectRaw('people.*')
->where('street_name', $street_name)
->with('addresses')
->get();
This uses a join and eager loading.
With the join alone, you can access the following though:
$person->street_address