Ordering a pagination by a relationship (Laravel 5.3)

Question

In my app I have posts. I wish to show all the posts on the homepage, but order them higher if the post's user has uploaded an image. I can determine if the post's user has uploaded an image by checking if the user has a relationship with a row in the images table, like this:

$post->user->image 

My code currently looks like this:

$posts = Post::where('subject_id', '=', $subject->id)
        ->approved()
        ->orderBy('created_at', 'desc')
        ->paginate(18);

Currently, I am simply ordering it by created at, but ideally all posts whose related user has an image will come first, then the rest. I've been looking for a way to do this efficiently and in a way that doesn't just work on the first page.

How should I go about this?

Answer 1

Try this:

$posts = Post::where('subject_id', '=', $subject->id)
        ->approved()
        ->select(['*', DB::raw('(SELECT count(images.id) FROM images INNER JOIN users ON users.image_id = images.id WHERE posts.user_id = users.id) as count_images'])
        ->orderBy('count_images', 'desc')
        ->orderBy('created_at', 'desc')
        ->paginate(18);