Using Laravel's withCount to count a sub query

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_countfield 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.