mysql - 如果计算行数，查询生成器找不到列

Question

我正在尝试查看连续一周有多少记录并遇到有趣的行为

这个作品

    $weeks = $className::where($user_id_column, $user_id)
            ->where('created_at', '>', $period_start)
            ->select(DB::raw('WEEK(created_at) AS weeknumber'))
            ->groupBy('weeknumber')
            ->get();

这会引发错误

$week_count = $className::where($user_id_column, $user_id)
        ->where('created_at', '>', $period_start)
        ->select(DB::raw('WEEK(created_at) AS weeknumber'))
        ->groupBy('weeknumber')
        ->count();

Unknown column 'weeknumber' in 'group statement'
这是为什么？

Answer 1

您遇到此问题的原因是 count()将用 count(*) as aggregate 替换 select 语句.

要实现您的目标，您可以手动添加计数:

$week_count = $className::where($user_id_column, $user_id)
    ->where('created_at', '>', $period_start)
    ->selectRaw('WEEK(created_at) AS weeknumber, COUNT(*)')
    ->groupBy('weeknumber')
    ->get();