Using Laravel's withCount to count a sub query

Using Laravel's withCount to count a subquery

When you need to could a subquery Laravel provides a useful withCount method that is perfect.

Take an example of a user who 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();

Finally 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 makes these types of queries.

Domains are often purchased from multiple providers, keeping track of where a domain is and its DNS settings can be tricky. Domain Mapper solves this by listing all your domains in one place. View your DNS settings and receive reminders to renew your domains. Try it today.