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.