When you need to could a sub query Laravel provides a useful withCount method that is perfect.
Take an example of a user can have records with a hasMany relationship:
I have a model called BookingCandidate that links to the user by its filled_by_id that matches a user_id
public function filledJobs(): HasMany
{
return $this->hasMany(BookingCandidate::class, 'filled_by_id', 'id');
}
To count how many filledJobs match users a simple withCount will do the job:
User::withCount('filledJobs')->get();
this will add a `filled_jobs_count`field into the response.
You can also order by the field using it inside an order by:
User::withCount('filledJobs')->orderby('filled_jobs_count', 'desc')->get();
Fianlly if you want to use a closure with the withCount this can be down by using withCount([]) like this:
$start = date('Y-m-1');
$end = date('Y-m-t');
User::withCount(['filledJobs' => function($q) use($start, $end) {
$q->where('filled_at', '>=', $start)
->where('filled_at', '<=', $end);
}])
->orderby('filled_jobs_count', 'desc')
->get();
I love how easy Laravel make these types of queries.