Laravel OrderBy relationship count

Question

I'm trying to get the most popular hackathons which requires ordering by the respective hackathon's partipants->count(). Sorry if that's a little difficult to understand.

I have a database with the following format:

hackathons
    id
    name
    ...

hackathon_user
    hackathon_id
    user_id

users
    id
    name

The Hackathon model is:

class Hackathon extends \Eloquent {
    protected $fillable = ['name', 'begins', 'ends', 'description'];

    protected $table = 'hackathons';

    public function owner()
    {
        return $this->belongsToMany('User', 'hackathon_owner');
    }

    public function participants()
    {
        return $this->belongsToMany('User');
    }

    public function type()
    {
        return $this->belongsToMany('Type');
    }
}

And HackathonParticipant is defined as:

class HackathonParticipant extends \Eloquent {

    protected $fillable = ['hackathon_id', 'user_id'];

    protected $table = 'hackathon_user';

    public function user()
    {
        return $this->belongsTo('User', 'user_id');
    }

    public function hackathon()
    {
        return $this->belongsTo('Hackathon', 'hackathon_id');
    }
}

I've tried Hackathon::orderBy(HackathonParticipant::find($this->id)->count(), 'DESC')->take(5)->get()); but I feel like I made a big mistake (possibly the $this->id), because it doesn't work at all.

How would I go about trying to get the most popular hackathons which is based on the highest number of related hackathonParticipants?

score 267 · Answer 1 · answered Oct 13 '16 at 12:36

267

This works for me in Laravel 5.3, using your example:

Hackathon::withCount('participants')->orderBy('participants_count', 'desc')->paginate(10);

This way it is ordered on the query and the pagination works nicely.

answered Oct 13 '16 at 12:36

kJamesy

5,973
5
20
22

24

This is a much better answer than the accepted one. – tremby Nov 07 '17 at 23:13
1

Method `withCount()` was added in Laravel 5.2 – Marek Skiba Jul 20 '18 at 11:59
Very accurate and quick answer..Thanks – Shahrukh Anwar Aug 10 '18 at 08:28
6

just for info, if your relation name got Uppercase letter such as `maleParticipants`, the query should be like thi `withCount('maleParticipants')->orderBy('male_participants', 'desc')` – Syamsoul Azrien Oct 05 '18 at 09:47
1

Thank you. In Laravel Backpack, I was able to use `$this->crud->query->withCount('contactTags')->orderBy('contact_tags_count', 'desc')`. – Ryan Dec 10 '18 at 01:08
Great answer. Much better than the accepted answer because it maintains the query. The accepted answer works, but it is after the query is executed, which results in things like pagination not working and can become slow if the size of the collection returned gets large. – Chad Fisher Feb 05 '20 at 20:56
The order query can be more elegan with `->orderByDesc('participants_count')`. – Muh Ghazali Akbar May 12 '20 at 08:32

score 48 · Answer 2 · answered Oct 17 '17 at 01:19

48

Another approach can be by using withCount() method.

Hackathon::withCount('participants')
        ->orderBy('participants_count', 'desc')
        ->paginate(50);

Ref: https://laravel.com/docs/5.5/eloquent-relationships#querying-relations

answered Oct 17 '17 at 01:19

tisuchi

924
1
14
18

user1669496 · Accepted Answer · 2019-10-17T20:36:48.607

47

Edit: If using Laravel 5.2 or greater, use kJamesy's answer. It will likely perform a bit better because it's not going to need to load up all the participants and hackathons into memory, just the paginated hackathons and the count of participants for those hackathons.

You should be able to use the Collection's sortBy() and count() methods to do this fairly easily.

$hackathons = Hackathon::with('participants')->get()->sortBy(function($hackathon)
{
    return $hackathon->participants->count();
});

edited Oct 17 '19 at 20:36

answered Jun 13 '14 at 15:28

user1669496

32,176
9
73
65

1

How to change asending/desecnding? – FooBar May 25 '15 at 21:36
4

`sortBy(Closure $callback, $options = SORT_REGULAR, bool $descending = false)` Just set 3rd parameter to true. – user1669496 May 26 '15 at 12:42
46

sort by doesn't do that at the DB level...so it won't work correct with paging! Good for when you're returning a full set though.. – Sabrina Leggett Jun 16 '15 at 21:57
got error laravel 5.5 : Call to undefined method Illuminate\Database\Query\Builder – Saurabh Mistry Jul 08 '18 at 13:55
2

very bad performance though. because it will get count of each row in a query. all of the counts should come within one query for fast results. – Majed DH Oct 23 '18 at 18:55
It would not get the count in a new query. It's already getting all the results, then counting the size of the array. If it was `$hackathon->participants()->count();` then you'd be correct. It's calling the `count` method on the `Collection` object. – user1669496 Oct 23 '18 at 20:48
If you need to use Collection and not Query Builder this will do the job done. For descending order use sortByDesc() method. – Stefan Pavlov Jan 28 '21 at 09:39

score 15 · Answer 4 · answered Mar 11 '16 at 16:00

15

I had similar issue and using sortBy() is not suitable because of pagination, exactly as Sabrina Gelbart commented in previous solution. So I used db raw, here's simplified query:

Tag::select( 
array(
    '*',
    DB::raw('(SELECT count(*) FROM link_tag WHERE tag_id = id) as count_links')) 
)->with('links')->orderBy('count_links','desc')->paginate(5);

answered Mar 11 '16 at 16:00

Johnyz

191
1
3

2

Best solution I've found for earlier versions of Laravel! (new ones can use withCount) Not sure what it was but I had do to: `tag_id = tag.id` instead of `tag_id = id`...also, you can simplify this even more! Instead of doing `select` you can just do `orderByRaw('(SELECT count(*) FROM link_tag WHERE tag_id = tag.id) as count_links') DESC')->paginate(5)`. And for those wondering, you don't need the `->with` unless you're loading the relationship – Sabrina Leggett Nov 09 '16 at 16:57
And..you can also do this in a scope if you want to...`Tag::orderByLinkCount()->paginate(5)` then in your Tag model create `scopeOrderByLinkCount` with your orderByRaw statement – Sabrina Leggett Nov 09 '16 at 17:03
I beleive it should be `WHERE tag_id = tag.id` NOT `WHERE tag_id = id` – Yahya Uddin Oct 31 '17 at 00:19

score 6 · Answer 5 · edited May 23 '17 at 12:34

You can also use join operator. As Sabrina said, you can not use sortby at the db level.

$hackathons = Hackathon::leftJoin('hackathon_user','hackathon.id','=','hackathon_user.hackathon_id')
           ->selectRaw('hackathon.*, count(hackathon_user.hackathon_id) AS `count`')
           ->groupBy('hackathon.id')
           ->orderBy('count','DESC')
           ->paginate(5);

But this code takes all records from database. So you should paginate manually.

       $hackathons = Hackathon::leftJoin('hackathon_user','hackathon.id','=','hackathon_user.hackathon_id')
           ->selectRaw('hackathon.*, count(hackathon_user.hackathon_id) AS `count`')
           ->groupBy('hackathon.id')
           ->orderBy('count','DESC')
           ->skip(0)->take(5)->get();

Referred from : https://stackoverflow.com/a/26384024/2186887

score 6 · Answer 6 · answered Jul 24 '21 at 08:08

6

I needed to sum multiple counts and then use it to set order. Following query worked for me in Laravel 8.

$posts = Post::withCount('comments','likes')->orderBy(\DB::raw('comments_count + likes_count'),'DESC')->get();

answered Jul 24 '21 at 08:08

Neo

523
7
15

score 4 · Answer 7 · answered Jul 14 '21 at 17:06

4

You can use below code

Hackathon::withCount('participants')->orderByDesc("participants_count")->paginate(15)

Or if you even want ASC/DESC with single method

Hackathon::withCount('participants')->orderBy("participants_count", 'asc')->paginate(15)

answered Jul 14 '21 at 17:06

Mujahid Khan

1,712
1
18
24

Laravel OrderBy relationship count

7 Answers7

Linked