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.

Image of Timescale

🚀 pgai Vectorizer: SQLAlchemy and LiteLLM Make Vector Search Simple

We built pgai Vectorizer to simplify embedding management for AI applications—without needing a separate database or complex infrastructure. Since launch, developers have created over 3,000 vectorizers on Timescale Cloud, with many more self-hosted.

Read full post →

Top comments (0)

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more