I have two tables.
patients: id, site_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 :< )
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.