groupBy() and count(): What is the laravel way?

Published 5 months ago by mankowitz

I have two tables.

patients: id, site_id

sites: id

and they are linked so ( patients.site_id = )

I want to figure out how many patients there are for each site.

In SQL, I can make a query like this

FROM   sites 
       LEFT JOIN patients 
              ON ( patients.site_id = ) 
WHERE  patients.site_id IN (SELECT id 
                            FROM   sites 
                            WHERE  medical_director_id = 35) 
       AND patients.created_at BETWEEN 20080101 AND 20200101 

The result is something like this:

| site | count() | | --- | --- | | Home1 | 23 | | Home2 | 1 |

And I'd like to do the laravel thing, so I start with this:

Patient::whereBetween('created_at', [$from, $to])
    ->whereIn('site_id', Site::where('medical_director_id', $id)->select(['id']))

But I can't figure out how to use count() and still get the aggregation. What should I do?

(I also can't figure out the markdown to make a table :< )

5 months ago (57,965 XP)
class Patient
    public function site()
        return $this->belongsTo(Site::class);
class Site
    public function patients()
        return $this->hasMany(Patient::class);
$sites = Site::withCount('patients')->get();
// or
$sites = Site::withCount(['patients' => function($query) use ($from, $to) {
    $query->whereBetween('created_at', [$from, $to]);
@foreach($sites as $site)
    {{ $site->patients_count }}

I guess you have a relations in your Site model named "patients()". (just in case).

$sites = Site::where('medical_director_id', $id)
    ->with(['patients' => function ($q) use($from, $to) {
        $q->whereBetween('created_at', [$from, $to]);
    ->get(['id', 'name']);


@Swaz - Thanks for your help. I already have the one-to-many relations set in the model definitions, but you pointed me in the right direction using withCount()

@Vilfago - Thanks for your help. I used withCount() instead of with() to get the count. Also, I found that get(['id', 'name']) didn't actually limit the output. I had to put in a select(['name']) command.

My final command:

Site::where('medical_director_id', $id)->select(['name'])
    ->withCount(['patients' => function ($q) use($from, $to) { 
        $q->whereBetween('created_at', [$from, $to]);                

Please sign in or create an account to participate in this conversation.