Level 56
Specify which fields you want to retrieve :
$query = $this->model->select('areas.*');
$query->join('addresses','areas.id','=','addresses.area_id');
$query->orderBy( ['id','desc'] );
But why join then eager loading ??
Jul 7, 2015
6
Level 7
Eager loading wrong data
I have a simple select query which eager loads associated data. I put a join on the initial select so I could better search the data but it causes incorrect relationship data to be retrieved. The queries being generated are as follows
CORRECT WITHOUT JOIN
select * from `areas` order by `name` asc limit 10 offset 0
select * from `addresses` where `addresses`.`area_id` in ('100', '16', '7', '81', '37', '76', '55', '43', '86', '11')
INCORRECT WITH JOIN ON INITIAL SELECT
select * from `areas` inner join `addresses` ON `areas`.`id` = `addresses`.`area_id` order by `name` asc limit 10 offset 0
select * from `addresses` where `addresses`.`area_id` in ('110', '26', '17', '91', '47', '86', '65', '53', '96', '21')
Basically, I want to search through the "address" details when retrieving "area" details. If you look closely when a join is added the value "100" from the first query, which is the primary key of the area row is swopped out for "110" which is the primary key of the related address row.
A simplified version of my query reads as follows
$query = $this->model->orderBy( ['id','desc'] );
$query->join('addresses','areas.id','=','addresses.area_id');
$query->with('address');
// Paginate and return the results.
return $query->paginate($pagination);
Level 7
As always, thanks @pmall.
I'm sure I have a lot to learn but I'm passing the model(s) to my view and displaying some of the related data. For example; An Area "hasOne" Address. How could I retrieve the related Address in one query without eager loading so I can use the following syntax inside my view :
{!! $area->address->latitude !!}
Cheers.
Level 56
Here you join then eager load the addresses, it is useless. Just eager load the area. Two queries, thats ok.
$areas = $this->model->with('address')->orderBy( ['id','desc'] )->get();
foreach ($areas as $area) {
echo $area->address->latitude;
}
Level 7
I wish to search on the address when making the initial query. I'm creating an "area search form" and the options include searching by address amongst other relationships.
My idea was to join on to the address table so it's columns are available inside the initial search query.
Am I reinventing the wheel?
Level 56
You can do it like this. You can also use whereHas, check the docs (querying relationships).
$areas = $this->model->with('address')->whereHas('address', function ($query) use ($search_terms) {
// Adapt this to what you want
$query->where('latitude', '=', $search_terms['latitude'];
})->orderBy( ['id','desc'] )->get();
It uses a subquery.
1 like
Level 7
Thankyou!
Please or to participate in this conversation.