如何基于created_at进行数据过滤？分组场景下无法添加该字段至SELECT的问题

我需要实现基于created_at字段的数据过滤功能，但遇到一个问题：无法将created_at添加至SELECT语句中，因为这会干扰已有的分组逻辑。

我的查询代码 #

$datas = DB::table('patrol_gate_surveillance_transactions as a')
    ->leftJoin('client_locations as b','a.client_location_id', 'b.id')
    ->leftJoin('clients as c', 'b.client_id', 'c.id')
    ->select(
        'a.client_location_id as location_id', 
        'b.name as location_name', 
        'c.name as client_name',
        DB::raw('SUM(CASE WHEN a.type = 1 THEN employee_qty ELSE 0 END) as e_qty_in'),
        DB::raw('SUM(CASE WHEN a.type = 2 THEN employee_qty ELSE 0 END) as e_qty_out')
    )
    ->groupBy('location_id', 'location_name', 'client_name')
    ->get();

表结构 #

patrol_gate_surveillance_transactions表字段包括：

• id（主键）
• client_location_id（关联场地ID）
• employee_qty（人员数量）
• type（类型：1为进入，2为离开）
• created_at（记录创建时间）
• updated_at（记录更新时间）

解决方案 #

基础过滤（无需修改分组） #

你完全不需要把created_at放到SELECT或GROUP BY语句里，直接在分组前添加where类条件即可，过滤逻辑会先于分组执行，不会影响原有的分组聚合结果。比如筛选最近7天的记录：

$datas = DB::table('patrol_gate_surveillance_transactions as a')
    ->leftJoin('client_locations as b','a.client_location_id', 'b.id')
    ->leftJoin('clients as c', 'b.client_id', 'c.id')
    // 添加created_at过滤条件
    ->where('a.created_at', '>=', now()->subDays(7))
    ->select(
        'a.client_location_id as location_id', 
        'b.name as location_name', 
        'c.name as client_name',
        DB::raw('SUM(CASE WHEN a.type = 1 THEN employee_qty ELSE 0 END) as e_qty_in'),
        DB::raw('SUM(CASE WHEN a.type = 2 THEN employee_qty ELSE 0 END) as e_qty_out')
    )
    ->groupBy('location_id', 'location_name', 'client_name')
    ->get();

如果需要更精确的日期范围（比如某一天到另一天），可以用whereBetween：

->whereBetween('a.created_at', [Carbon::parse('2024-01-01'), Carbon::parse('2024-01-31')])

按日期维度分组（如需按天/月统计） #

如果你的需求是按日期（比如每天）和场地分组统计，那需要将created_at格式化后加入SELECT和GROUP BY：

$datas = DB::table('patrol_gate_surveillance_transactions as a')
    ->leftJoin('client_locations as b','a.client_location_id', 'b.id')
    ->leftJoin('clients as c', 'b.client_id', 'c.id')
    ->where('a.created_at', '>=', now()->subMonth())
    ->select(
        'a.client_location_id as location_id', 
        'b.name as location_name', 
        'c.name as client_name',
        DB::raw('DATE(a.created_at) as record_date'), // 格式化日期为天
        DB::raw('SUM(CASE WHEN a.type = 1 THEN employee_qty ELSE 0 END) as e_qty_in'),
        DB::raw('SUM(CASE WHEN a.type = 2 THEN employee_qty ELSE 0 END) as e_qty_out')
    )
    ->groupBy('location_id', 'location_name', 'client_name', 'record_date') // 新增日期分组
    ->get();

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