DEV Community

David Carr
David Carr

Posted on • Originally published at dcblog.dev on

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');
}
Enter fullscreen mode Exit fullscreen mode

To count how many filledJobs match users a simple withCount will do the job:


User::withCount('filledJobs')->get();
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

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();
Enter fullscreen mode Exit fullscreen mode

I love how easy Laravel make these types of queries.

Discussion (0)