I have a users table, I need to pull out the count by year of birth.
SQL example:
-- years = [1999, 1997, 1996, ..., 1990] example
SELECT u.city, count(*) -- all count
SUM(IF(u.born_date between '1999-01-01' and '1999-12-31', 1, 0)) as '1999',
SUM(IF(u.born_date between '1998-01-01' and '1998-12-31', 1, 0)) as '1999',
SUM(IF(u.born_date between '1997-01-01' and '1997-12-31', 1, 0)) as '1999'
-- some more years
FROM users u
GROUP BY u.city;
How to do it in Laravel?
upd: I need to take users from another table, while I decided it like this:
$years = [1999, 1997, 1996]; // example
$byYearQueries = [];
$cities = City::query()->where('active', 1);
foreach ($years as $year) {
$byYearQueries['users as y' . $year] = function (Builder $query) use ($year) {
$query->whereHas(
'users',
function ($q) use ($year) {
/** @var Builder $q */
$q
->where(
'born_date',
'>=',
Carbon::make($year . '-01-01')->timestamp
)
->where(
'born_date',
'<=',
Carbon::make($year . '-12-31')->timestamp
);
}
);
};
}
$result = $cities->withCount($byYearQueries)->get();
result: y1999: 20, y1997: 15 …
2
Answers
You can try it:
Maybe you could try this :
you can if you want create an array with year_of_birth => number of users :
you will have then :
And you will not have to make a query for every year