I solved it shortly after I posted this.
In Company.php
, I changed the signature of positions()
from:
/**
* @return \Illuminate\Database\Eloquent\Relations\HasMany
*/
public function positions() {
return $this->hasMany(Position::class)
->with(['person'=> function($query) {
$query->orderBy('last_name', 'asc')
->orderBy('first_name', 'asc');}]
);
}
to:
/**
* @return \Illuminate\Database\Eloquent\Relations\HasMany
*/
public function positions() {
return $this->hasMany(Position::class)
->with('person')
->join('people', 'people.id', '=', 'positions.person_id')
->orderBy('people.last_name', 'asc')
->orderBy('people.first_name', 'asc');
}
That correctly results in only two queries:
select * from `positions` inner join `people` on `people`.`id` = `positions`.`person_id` where `positions`.`company_id` = '124' and `positions`.`company_id` is not null order by `people`.`last_name` asc, `people`.`first_name` asc;
select * from `people` where `people`.`id` in ('1054', '1145', '1074', '1018', '1075', '1113', '1070', '1130', '1108', '1111', '1110', '1069', '1066', '1118', '1057');
I also updated positions()
in Person.php
so they'll be sorted by the Company name in ascending order:
/**
* @return \Illuminate\Database\Eloquent\Relations\HasMany
*/
public function positions() {
return $this->hasMany(Position::class)
->with('company')
->join('companies', 'company.id', '=', 'positions.company_id')
->orderBy('company.name', 'asc');
}
Thanks this idea of doing a join to filter the hasMany by the contents of another table was very useful to me.
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community