Laravel Eloquent Order By Relationship Count

Question

I have a small blogging app which allows users to upload photos and audio. Users can also search for other blogs. While searching, I'd like to add a ranking to my query with the following precedence:

1) Users with most photos
2) Users with most audio

User model is constructed as such:

public function blog() {
    return $this->hasOne(Blog::class);
}

public function photos() {
    return $this->hasMany(Photo::class);
}

public function audio() {
    return $this->hasMany(Audio::class);
}

Blog model is constructed as such:

public function user()
{
    return $this->belongsTo(User::class);
}

My current search query:

    $blogs = Blog::where('description', 'ilike', '%'.$search.'%')
    ->orWhere('title', 'ilike', '%'.$search.'%')
    ->orWhereHas('user', function($query) use ($search) {
        $query->where('name', 'ilike', '%'.$search.'%')
            ->orWhere('username', 'ilike', '%'.$search.'%');
    })
    ->paginate(10);

Based off the given details, how do i adjust my query to return blogs ranked my their users photo and audio count?

** UPDATE **

I'm able to get the count of each nested relationship by using the withCount method with eager loading:

    $blogs = Blog::where('description', 'ilike', '%'.$search.'%')
    ->orWhere('title', 'ilike', '%'.$search.'%')
    ->orWhereHas('user', function($query) use ($search) {
        $query->where('name', 'ilike', '%'.$search.'%')
            ->orWhere('username', 'ilike', '%'.$search.'%');
    })
    ->with(['user' => function($query){
        $query->withCount(['blobs', 'audio']);
    }])
    ->paginate(10);

However, how can i then order this current query by those nested count attributes?

user_8637968 · Answer

Based on Laravel 8 docs: https://laravel.com/docs/8.x/eloquent-relationships#counting-related-models

You could try with:

Blog::withCount(['photos', 'audio'])

Which will place 2 columns: photos_count and audio_count.

So the final result would be:

$blogs = Blog::withCount(['photos', 'audio'])
->where('description', 'ilike', '%'.$search.'%')
->orWhere('title', 'ilike', '%'.$search.'%')
->orWhereHas('user', function($query) use ($search) {
    $query->where('name', 'ilike', '%'.$search.'%')
        ->orWhere('username', 'ilike', '%'.$search.'%');
})
->orderBy('photos_count', 'desc')
->orderBy('audio_count', 'desc')
->paginate(10);

Laravel Eloquent Order By Relationship Count

1 Answers