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

Posted 8 months 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 :< )

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

Laracasts Mascot

Hi, Have We Met Yet?

Did you know that, in addition to the forum, Laracasts includes well over 1000 lessons on modern web development? All for the price of one lunch out per month.

Sign Me Up

Channels

Reply to

Use Markdown with GitHub-flavored code blocks.