How to use withCount by year of birth?-CodePudding

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 ...

CodePudding user response：

Maybe you could try this :


$stats = User::select(DB::raw('city, YEAR(born_date) as year_of_birth, count(*) as count'))
    ->groupBy(DB::raw('city, YEAR(born_date)'))
    ->toBase()
    ->get();

you can if you want create an array with year_of_birth => number of users :

$stats->groupBy('city')->map->pluck('count', 'year_of_birth');

you will have then :

'London' => [
1999 => 204,
2000 => 301,
2001 => 2,
],
'Paris' => [
1999 => 44,
2001 => 30,
...
]

And you will not have to make a query for every year

CodePudding user response：

You can try it:

DB::table('users')
    ->selectRaw("users.city,
    count(*) AS allCount,
    (CASE WHEN users.born_date BETWEEN '1999-01-01' AND '1999-12-31'  THEN 1 ELSE 0 END) AS year1999),
    (CASE WHEN users.born_date BETWEEN '1998-01-01' AND '1998-12-31'  THEN 1 ELSE 0 END) AS year1998),
    (CASE WHEN users.born_date BETWEEN '1997-01-01' AND '1997-12-31'  THEN 1 ELSE 0 END) AS year1997)")
    ->groupBy('users.city')
    ->get();