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?
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.
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
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();
});
sortBy(Closure $callback, $options = SORT_REGULAR, bool $descending = false) Just set 3rd parameter to true.
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);
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
Tag::orderByLinkCount()->paginate(5) then in your Tag model create scopeOrderByLinkCount with your orderByRaw statement
WHERE tag_id = tag.id NOT WHERE tag_id = id
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
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();
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)
Follow WeChat
Success story sharing
withCount()was added in Laravel 5.2maleParticipants, the query should be like thiwithCount('maleParticipants')->orderBy('male_participants', 'desc')$this->crud->query->withCount('contactTags')->orderBy('contact_tags_count', 'desc').