如何在Laravel中按出生年份使用withCount统计用户数量？

我有一个users表，需要按出生年份统计各城市的用户数量，对应的SQL示例如下：

-- years = [1999, 1997, 1996, ..., 1990] 示例
SELECT u.city, 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 '1998',
       SUM(IF(u.born_date between '1997-01-01' and '1997-12-31', 1, 0)) as '1997'
       -- 更多年份
FROM users u
GROUP BY u.city;

请问如何在Laravel中实现该功能？

补充说明：我需要从City表获取关联的用户数据，目前我的实现方式如下：

$years = [1999, 1997, 1996]; // 示例
$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();

返回结果示例：y1999: 20, y1997: 15 ...

方案一：用selectRaw贴近原生SQL实现（效率更高） #

直接在主查询中通过条件聚合生成各年份统计值，和你给出的原生SQL逻辑一致，无需多次关联用户表：

use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

$years = [1999, 1997, 1996];
$selectColumns = ['cities.*', DB::raw('count(users.id) as total_users')];

foreach ($years as $year) {
    $startDate = Carbon::parse("{$year}-01-01")->toDateString();
    $endDate = Carbon::parse("{$year}-12-31")->toDateString();
    $selectColumns[] = DB::raw("SUM(CASE WHEN users.born_date BETWEEN '{$startDate}' AND '{$endDate}' THEN 1 ELSE 0 END) as y{$year}");
}

$result = City::query()
    ->where('active', 1)
    ->leftJoin('users', 'cities.id', '=', 'users.city_id') // 替换为实际的关联字段
    ->groupBy('cities.id', 'cities.name') // 根据City表实际字段调整分组项
    ->select($selectColumns)
    ->get();

返回结果包含total_users（该城市总用户数）以及y1999、y1997等各年份统计值。

方案二：优化你当前的withCount写法 #

去掉冗余的嵌套whereHas，直接在关联查询中过滤日期：

use Carbon\Carbon;
use Illuminate\Database\Eloquent\Builder;

$years = [1999, 1997, 1996];
$withCount = [];

foreach ($years as $year) {
    $start = Carbon::parse("{$year}-01-01")->startOfDay();
    $end = Carbon::parse("{$year}-12-31")->endOfDay();
    
    $withCount["users as y{$year}"] = function (Builder $query) use ($start, $end) {
        $query->whereBetween('born_date', [$start, $end]);
    };
}

// 额外添加总用户数统计
$withCount['users as total_users'] = function (Builder $query) {
    $query->select(DB::raw('count(id)'));
};

$result = City::query()
    ->where('active', 1)
    ->withCount($withCount)
    ->get();

该写法返回格式和你预期一致，同时避免了冗余查询，性能更优。

注意事项 #

• 确保City与User模型已正确定义关联关系（比如City模型中存在users()方法返回hasMany(User::class)）
• 若born_date存储的是时间戳，直接用时间戳比较即可；若为日期字符串，使用Carbon生成的日期字符串更稳妥
• 分组时需包含City表的主键及需要展示的字段，避免数据库严格模式下的分组错误

内容的提问来源于stack exchange，提问作者d1zz7