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

Published 1 week ago by mankowitz

I have two tables.

patients: id, site_id

sites: id

and they are linked so ( patients.site_id = sites.id )

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

In SQL, I can make a query like this

SELECT sites.NAME, 
       Count(*) 
FROM   sites 
       LEFT JOIN patients 
              ON ( patients.site_id = sites.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']))
    ->groupBy('site_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 :< )

Swaz
Swaz
1 week ago (54,955 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]);
}])->get();
@foreach($sites as $site)
    {{ $site->patients_count }}
@endforeach
Vilfago
Vilfago
1 week ago (31,790 XP)

I guess you have a relations in your Site model named "patients()". https://laravel.com/docs/5.6/eloquent-relationships#one-to-many (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']);
dump($sites);

https://laravel.com/docs/5.6/eloquent-relationships#counting-related-models

mankowitz

@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]);                
    }])->get();

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