Laravel OrderBy relationship count
Stefan Izdrail
Founder & Senior Architect · 2026-06-29
Title: Laravel OrderBy Relationship Count - A Comprehensive Guide for Querying Popular Hackathons
Body:
When developing applications using Laravel, it is essential to understand how to query and manage relationships between various models. In this blog post, we will focus on ordering hackathon participants by the number of their respective hackathons, which can be an important aspect when determining popular events. We start with our current database structure and then proceed to write correct queries for this purpose.
Database Structure:
hackathons (Table)
id
name
...
hackathon_user (Table)
hackathon_id
user_id
users (Table)
id
name
Model Definitions:
The Hackathon model defines the relationships with users: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');
}
}
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');
}
}
Incorrect Query and Possible Mistakes:
You attempted to use:Hackathon::orderBy(HackathonParticipant::find($this->id)->count(), 'DESC')->take(5)->get();
Correct Query and Explanation:
To achieve the desired result, we need to follow these steps: 1. Retrieve all HackathonParticipant instances within our given date range (e.g., current day) using `HackathonParticipant::whereBetween('created_at', [start\_date, end\_date])`. 2. If we don't have a specific date range, we can collect all HackathonParticipant instances or use the latest ones (e.g., `HackathonParticipant::latest()->take(5)`). 3. Use the Laravel collection `mapWithKeys()` function to create an associative array that maps each participant instance to its respective hackathon ID and count. Then, sort this new array in descending order by count using the PHP built-in `array_multisort()` function. 4. Iterate through the sorted array and fetch the Hackathon instances with their respective counts without counting duplicates (users participating in multiple hackathons). 5. Lastly, retrieve a subset of hackathons by taking the most popular ones based on the highest count within our desired limit (e.g., 5).$participants = HackathonParticipant::whereBetween('created_at', [date('Y-m-d'), date('Y-m-d')])
->get()
->mapWithKeys(function ($hackathonParticipant) {
return [$hackathonParticipant->hackathon_id => $hackathonParticipant->participants()->count()];
})
->sortByDesc('1')
->toArray();
$popularHackathons = Hackathon::with(['owner', 'type'])->whereIn('id', array_keys($participants))
->selectRaw('hackathons.*, count(hackathon_participant.user_id) as total_participants')
->join('hackathon_participant', function ($join) {
$join->on('hackathon_participant.hackathon_id', '=', 'hackathons.id')
->whereIn('user_id', array_keys($participants))
->groupBy('hackathon_participant.hackathon_id');
})
->groupBy('hackathons.id')
->orderByDesc('total_participants')
->take(5)
->get();