如何在laravel中执行GroupBy的GroupBy ?

Question

我有一个reports表，看起来像下面的东西:

Reports是用户投入工作的月度报告。这是一个多对多关系表。

<表类> id user_id project_id total_effort created_at tbody><<tr>15232402023-01-23

可以使用with()方法加载groupby子句

$data = Report::select('user_id', 'project_id')
->selectRaw("SUM(total_effort) as total, DATE_FORMAT(report_for_date, '%b,%Y') new_date")
->groupBy('user_id')
->groupBy('project_id')
->with('userDetail:id,first_name,last_name')
->with('projectDetail:id,name')
->get();

我会查询报告期内的活动项目，然后使用条件聚合(pivot)构建主查询-

// get list of projects within reporting period
$projects = DB::table('projects p')
->join('reports r', 'p.id', '=', 'r.project_id')
->select('p.id', 'p.name')
->whereBetween('r.created_at', ['2023-01-01', '2023-01-31'])
->groupBy('p.id')
->get();
// build conditional aggregates
$columns = ['CONCAT(u.first_name, ' ', u.last_name) AS username'];
foreach ($projects as $project) {
$columns[] = "SUM(IF(r.project_id = {$project->id}, total_effort, 0)) AS project_{$project->id}";
}
// Add total column
$columns[] = 'SUM(total_effort) AS total';
$report = DB::table('reports r')
->join('users u', 'r.user_id', '=', 'u.id')
->selectRaw(implode(',', $columns))
->whereBetween('r.created_at', ['2023-01-01', '2023-01-31'])
->groupBy('r.user_id')
->get();

这个想法是建立并执行一个查询，看起来像这样-

SELECT
CONCAT(u.first_name, ' ', u.last_name) AS username,
SUM(IF(r.project_id = 232, total_effort, 0)) AS project_232,
SUM(IF(r.project_id = 233, total_effort, 0)) AS project_233,
SUM(IF(r.project_id = 234, total_effort, 0)) AS project_234,
SUM(total_effort) AS total
FROM reports r
JOIN users u ON r.user_id = u.id
WHERE r.created_at BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY r.user_id