Ordering of nested relation by attribute with laravel

Question

Hi there i'm trying to sort a collection by attribute of the relation.

This is my model

class Song extends \Eloquent {
    protected $fillable = ['title', 'year'];
    public function artist(){
            return $this->hasOne('Artist','id', 'artist_id');
    }
}

class SongDance extends \Eloquent {
    protected $table = 'song_dances';
        protected $fillable = ['rating'];
    public function dance(){
        return $this->belongsTo('Dance', 'dance_id');
    }
    public function song(){
        return $this->belongsTo('Song', 'song_id');
    }
}

class Dance extends \Eloquent {
        protected $fillable = ['name'];
    public function song_dances(){
        return $this->hasMany('SongDance','dance_id','id');
    }
    public function songs(){
        return $this->belongsToMany('Song', 'song_dances', 'dance_id', 'song_id');
    }
}

this is how far i'm by now:

$dance = Dance::find(1);
$songs = $dance->songs()
    ->with('artist')->whereHas('artist', function ($query) {
        $query->where('urlName','LIKE','%robbie%');})
    ->where('song_dances.rating', '=', $rating)
    ->orderBy('songs.title','asc')
    ->where('songs.year', '=', 2012)
    ->get();

Yeah i just could add a ->sortBy('artist.name'); to the query, but he result-collection can be quite big (about 6000 items) therefore i would prefer a databased sorting.

is there a possibility to do this?

score 1 · Accepted Answer · answered Jul 13 '14 at 03:59

Since Eloquent's relations are all queried separately (not with JOINs), there's no way to achieve what you want in the database layer. The only thing you can do is sort the collection in your app code, which you've already dismissed.

If you feel you must sort it in the database then you should write your own join queries instead of relying on Eloquent's relations.

Ordering of nested relation by attribute with laravel

1 Answers1