Laravel order results by column on polymorphic table

Question

Short question

Using Laravel's Eloquent models; how do I order a query by a column on a has morphMany polymorphic relationship?

Longer question

Say I have the given table structure:

// table: images
+ -- + ------------ + -------------- + ----- + ----------- +
| id | imageable_id | imageable_type | views | upload_date |
+ -- + ------------ + -------------- + ----- + ----------- +
| 1  | 1            | User           | 100   | 2016-16-06  |
| 2  | 3            | User           | 200   | 2016-16-06  |
+ -- + ------------ + -------------- + ----- + ----------- +

// table: users
+ -- + ---- +
| id | name |
+ -- + ---- +
| 1  | Bob  |
| 2  | Kat  |
+ -- + ---- +

...and I have an Eloquent User model with a morphMany polymorphic relationship.

How can I order users by the number of views the image they uploaded today receives?

I have experimented with this issue myself and haven't been able to find a solution. The closest I had come to the correct ordering was when I used a similar query to this:

User::with([
   'images' => function ($query) {
       $query->where('upload_date', Carbon::today()->toDateString())
           ->orderBy('views', 'DESC');
   }
])->get();

But, after trying this, it was evident to me that the users are now incorrectly ordered by their incrementing id, not the views column on the images table.

Thank you in advance to anybody who helps me on my journey to figuring this out.

Answer 1

I figured it out thanks to a comment on this post http://laravel.io/forum/02-21-2014-order-and-paginate-a-collection-by-a-releted-field-using-eloquent.

User::leftJoin('images', function ($join) {
    $join->on('users.id', '=', 'images.imageable_id')
        ->where('imageable_type', '=', 'User')
        ->where('upload_date', '=', Carbon::today()->toDateString());
})
    ->orderBy('images.views')
    ->select(['players.*']);

I tried something similar to this before but was encountering problems because the ids, and other columns, were clashing between the two joined tables. The difference this time being ->select(['users.*']). Now, only the user's data is returned.

Answer 2

Alternatively, you can also use collections' sortBy method.

$users = User::with([
   'images' => function ($query) {
       $query->where('upload_date', Carbon::today()->toDateString());
   }
])->get()->sortByDesc(function ($user) {
    return $user->images->views;
});