mysql 如何在laravel中执行GroupBy的GroupBy？

wz3gfoph 于 2023-01-25 发布在 Mysql

关注(0)|答案(2)|浏览(166)

我有一个reports表，它看起来如下所示：
报表是用户投入的月度报表，是多对多的关系表。
| 身份证|用户标识|项目标识|总工作量|创建时间|
| - ------|- ------|- ------|- ------|- ------|
| 1个|五个|二百三十二|四十|二〇二三年一月二十三日|
我想让所有的用户在项目上都有贡献，这样我就可以创建一个类似这样的excel。

我可以按组对用户进行分组并获取他们的集体数据，但也无法按项目ID进行分组。
到目前为止，我的问题是

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

此查询返回

[0] => Array
        (
            [user_id] => 2
            [total] => 500
            [new_date] => Dec,2022
            [user_detail] => Array
                (
                    [id] => 2
                    [first_name] => Hermione
                    [last_name] => Granger
                )

        )

但我实际上在寻找的是这样的东西：

[0] => Array
        (
            [user_id] => 2
            [total] => 500
            [new_date] => Dec,2022
            [projects]=>Array(
                [0]=>Array(
                    [project_id]=>1,
                    [total]=>30,
                    [project_detail]=>Array(
                            [id]=>1
                            [name]=>Project 1
                    )
                )
                [1]=>Array(
                   [project_id]=>41,
                    [total]=>30,
                    [project_detail]=>Array(
                            [id]=>41
                            [name]=>Project 41
                    )
                )
                [2]=>Array(
                    [project_id]=>32,
                    [total]=>30,
                    [project_detail]=>Array(
                            [id]=>32
                            [name]=>Project 32
                    )
                )
            )
            [user_detail] => Array
                (
                    [id] => 2
                    [first_name] => Hermione
                    [last_name] => Granger
                )

        )

这样我就可以循环数据，并在excel中绘制它们。这怎么能做到呢？即使只是一个原始的MySQL查询，如何在groupby内部做groupby？
我查过一些参考资料，但没有任何帮助

mysql

来源：https://stackoverflow.com/questions/75205555/how-to-perform-groupby-of-a-groupby-in-laravel

2条答案

按热度按时间

snz8szmq1#

可以使用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();

赞(0）回复(0）举报 2023-01-25

gwo2fgha2#

我将查询报告期内的活动项目，然后使用这些项目构建具有条件聚合（透视）的主查询-

// 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

赞(0）回复(0）举报 2023-01-25

我来回答

mysql 如何在laravel中执行GroupBy的GroupBy？

2条答案

相关问题

热门标签

最新问答