如何将指定SQL控制台查询转换为Laravel查询构建器函数？

原始SQL查询（可得到预期结果） #

select DATE(c.check_in),
       group_concat(users.id),
       sec_to_time(sum(TIME_TO_SEC(c.check_hours))) AS total_time
from users
         join checks c on users.id = c.user_id
where c.check_hours is not null
  and DATE(c.check_in) = '2022-11-17'
group by DATE(c.check_in)

最初尝试的Laravel代码（存在问题） #

用户尝试用模型关联加载的方式实现，但无法正确完成时间求和逻辑，且逻辑存在冗余：

DB::connection()->enableQueryLog();
$today = Carbon::yesterday();
$asdf = User::query()
    ->byNotWhereAdmin()
    ->with(['checks' => function ($query) {
        $query->where('check_hours', '08:00') // 多余条件，原始SQL无此限制
            ->where('check_hours', '!=', null)
            ->selectRaw('sum(TIME_TO_SEC(`check_hours`)) as total_time') // 缺少SEC_TO_TIME包裹
            ->whereDate('check_in', '2022-11-17');
    }])
    ->get();

预期结果 #

DATE(c.check_in) group_concat(users.id)         total_time
2022-11-17,      "2,8,11,5,15,16,4,6,14,7,13",  88:00:00

现有解决方案的问题分析 #

用户切换到Check模型实现的代码存在以下问题：

1. 分组逻辑错误：groupBy('check_in', 'check_hours')会将同一天不同打卡时长的记录拆分分组，不符合原始需求的「按日期聚合」；
2. 未实现用户ID聚合：with('user')仅加载单个用户模型，无法得到group_concat(users.id)的聚合结果；
3. 语法不规范：where('check_hours', '!=', null)可替换为Laravel原生的whereNotNull方法；
4. 日期范围处理冗余：分支判断可简化，无需区分两种whereBetween逻辑。

优化后的Laravel查询构建器实现 #

1. 完全匹配原始SQL的单日期查询 #

$targetDate = '2022-11-17';

$result = User::query()
    ->join('checks as c', 'users.id', '=', 'c.user_id')
    ->whereNotNull('c.check_hours')
    ->whereDate('c.check_in', $targetDate)
    ->selectRaw('DATE(c.check_in) as check_date')
    ->selectRaw('GROUP_CONCAT(users.id) as user_ids')
    ->selectRaw('SEC_TO_TIME(SUM(TIME_TO_SEC(c.check_hours))) as total_time')
    ->groupBy('check_date')
    ->first(); // 单日期分组，用first()更高效

2. 支持日期范围查询的优化版 #

$startDate = $this->selectDate;
$endDate = $this->selectDate2;

$result = User::query()
    ->join('checks as c', 'users.id', '=', 'c.user_id')
    ->whereNotNull('c.check_hours')
    // 统一处理日期范围，确保包含结束日期的所有时间
    ->whereBetween('c.check_in', [
        Carbon::parse($startDate)->startOfDay(),
        Carbon::parse($endDate)->endOfDay()
    ])
    ->selectRaw('DATE(c.check_in) as check_date')
    ->selectRaw('GROUP_CONCAT(DISTINCT users.id) as user_ids') // 去重避免重复用户ID
    ->selectRaw('SEC_TO_TIME(SUM(TIME_TO_SEC(c.check_hours))) as total_time')
    ->groupBy('check_date')
    ->get();

优化说明 #

• 使用join替代with：聚合查询需直接关联表，而非加载模型关联；
• 规范语法：用whereNotNull替代手动判断非空；
• 简化日期处理：通过startOfDay()和endOfDay()确保范围包含结束日期的所有记录，无需分支；
• 去重优化：给GROUP_CONCAT添加DISTINCT，避免同一用户当天多次打卡导致ID重复；
• 分组逻辑对齐原始SQL：按DATE(c.check_in)分组，确保聚合结果正确。

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