skip to Main Content

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


  1. 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();
    
    Login or Signup to reply.
  2. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search