Laravel Sort by column in related table

32 Views Asked by At

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

  1. I could recreate the SQL statement using ->join(), but I'm looking to do it in the Laravel way.
  2. 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);
  }
}
0

There are 0 best solutions below