如何获取目标表最新的created_at和updated_at字段

现有一段Laravel查询代码，关联了patrol_gate_surveillance_transactions、client_locations、clients三张表，用于分组统计各地点的员工出入数量。现在需要在查询结果中，额外获取每组对应的patrol_gate_surveillance_transactions表中最新的created_at和updated_at字段，传递到Blade模板使用。

$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表：包含created_at、updated_at、client_location_id、type、employee_qty等字段，记录员工出入数据
• client_locations表：包含id、client_id、name字段，存储地点信息
• clients表：包含id、name字段，存储客户信息

查询结果中需包含每组对应的patrol_gate_surveillance_transactions表最新的created_at和updated_at字段，用于Blade模板解析。

要获取每组最新的时间字段，可通过聚合函数MAX()来获取分组内最大的created_at和updated_at（即最新的记录时间），修改后的查询代码如下：

$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'),
        // 获取分组内最新的created_at
        DB::raw('MAX(a.created_at) as latest_created_at'),
        // 获取分组内最新的updated_at
        DB::raw('MAX(a.updated_at) as latest_updated_at')
    )
    ->groupBy('location_id', 'location_name', 'client_name')
    ->get(); 

修改后，查询结果的每条数据会新增latest_created_at和latest_updated_at字段，对应该地点分组下最新的记录时间，可直接在Blade模板中调用。

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