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.
Subscribe to my newsletter for the latest updates on my books and digital products.
Find posts, tutorials, and resources quickly.
Subscribe to my newsletter for the latest updates on my books and digital products.
