I am asking if there is a way to sort by a second-order table without triggering the static analysis.
I have several related tables and I'm trying to sort the results by a related column. For my models, an Encounter belongs to a Site. An Encounter has many Notes. (model definitions below). I have two queries I want to use. First, I want to sort notes by the last name of the encounter (encounters.last_name) and then I want to sort notes by the name of the site (site.name). In SQL, I would do the following:
SELECT * FROM notes n
JOIN encounters e ON n.encounter_id=e.id
JOIN sites s ON e.site_id=s.id ...
(1) ORDER BY s.name
(2) ORDER BY e.last_name
In Laravel, I do the following using Subquery Ordering
(1) Note::with(['encounter','encounter.site'])
->orderBy(Site::select('name')->whereHas('encounters',
fn ($q) => $q->whereColumn('encounter_id', 'encounters.id')));
(2) Note::with(['encounter','encounter.site'])
->orderBy(Encounter::select('last_name')
->whereColumn('encounter_id', 'encounters.id'))
So this works, but it is ugly and it gives me a phpstan error:
Parameter #1 $column of method Illuminate\Database\Eloquent\Builder<App\Note>::orderBy() expects
Closure|Illuminate\Contracts\Database\Query\Expression|Illuminate\Database\Eloquent\Builder<App\Note>|Illuminate\Database\Query\Builder|string,
Illuminate\Database\Eloquent\Builder<App\Encounter> given.
Is there a "Laravel" way to have a proper query that doesn't trigger the static analysis?
What I tried
One solution I came up with is to use withAggregate()
, but it only seems to be able to search one table deep. For example, I could do this:
Note::with(['encounter','encounter.site'])->withAggregate('encounter', 'last_name')->orderBy('encounter_last_name');
But then this doesn't work:
Note::with(['encounter','encounter.site'])->withAggregate('encounter.site', 'name')->orderBy('encounter_site_name');
What I don't want
- I could recreate the SQL statement using
->join()
, but I'm looking to do it in the Laravel way. - I could download the notes unsorted and use Laravel's
->sortBy()
, but that is inefficient because of pagination.
Models
class Encounter extends Model
{
public function site(): BelongsTo
{
return $this->belongsTo(Site::class)->withTrashed();
}
}
class Note extends Model
{
public function encounter()
{
return $this->belongsTo(Encounter::class);
}
}