Group by and nested array with php/eloquent/mysql

Question

Say that I have a time based dataset, which contains an array of data like this:

{
   "group": "a", "date": "2017/01/01", value: 0.01
},
{
   "group": "a", "date": "2017/01/02", value: 0.02
}

How can I produce a result set like this:

{
    "group": "a", value_sum: 0.03, timeline: [["2017/01/01", 0.01], ["2017/01/02", 0.02]]
}

and so on.

I'm using Eloquent ORM, and I'm currently stick to a pattern where I make two different queries, the first to get the sum for each distinct group:

SELECT group, sum(value) FROM table GROUP BY group

and the second one to get the trends:

SELECT group, date, sum(value) FROM table GROUP BY group, date

then I merge the two results set in php.

I'm looking for a way to get all results in one query

Tim Biegeleisen · Accepted Answer · 2017-10-09 12:12:12Z

1

You can try doing a GROUP BY with rollup:

SELECT
    `group`, date, sum(value)
FROM table
GROUP BY
    `group`, date
WITH ROLLUP

In Laravel:

DB::table('table')
    ->select('`group`', 'date', DB::raw('sum(value) as sumval'))
    ->groupBy(DB::raw('`group`, date WITH ROLLUP'))
    ->get();

You can check the MySQL documentation to see how group by with rollup works. For the current structure of your queries, rollup would generate additonal rows for the subtotals of the value for each group. In addition there would be a grand total row on the bottom of the result set. While you could also achieve the same thing with two separate queries, you might find it easier to let MySQL do the heavy lifting for you. After all, it was created for this very purpose.

One other note: Don't name your columns group, because this is a reserved MySQL keyword. Also, don't name tables table. Perhaps you just used those names as placeholders, but if not you'll have to escape them everywhere.

edited Oct 9, 2017 at 12:12

answered Oct 9, 2017 at 12:02

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

S.C. Over a year ago

Yes, using WITH ROLLUP let me spare one sql query. Thanks.

Collectives™ on Stack Overflow

Group by and nested array with php/eloquent/mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related