I have a reports table which looks like something as follows:

Reports is monthly reports of users putting their efforts. It's many to many relationship table.

I want to get all users with their contributions projects-wise so that I can create an excel something like this.

I am able to group users by group and get their collective data, but unable to group by project id as well.

So far here is my query

  $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();

And this query return

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

        )

But what I am actually looking for is something like this:

[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
                )

        )

So that I can loop the data and plot them in excel. How can this be done? Even if it is just a Raw MySQL query how to do Groupby inside a groupBy?

Some reference that I checked but without any help

• sql group by sub group
• Mysql Nested query and GROUP BY
• using group function inside group function

CodePudding user response：

You can use with() method to load the relation with groupby clause

$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();

CodePudding user response：

I would query the active projects within the reporting period and then use that to build up the main query with conditional aggregation (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();

The idea is to build and execute a query which looks something like this -

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