Is there a way to make this query with SUM more optimized?

I need to sum certain columns and group them by date, so I use:

MyModel::toBase()
->selectRaw('date, SUM(column1) as column1, SUM(column2) as column2, SUM(column3) as column3')
->groupBy('date')
->get()

But as opposed to just using sum, when I group by date I also need to use get which always gets the entire data if I'm getting it right? And is thus slower and takes more resources. Or I'm wrong and that's ok?

Initially I thought I would not need to group by date so it was just:

MyModel::toBase()
->selectRaw('date, SUM(column1) as column1, SUM(column2) as column2, SUM(column3) as column3')
->first();

rodrigo.pedra

4 years ago

Level 56

when I group by date I also need to use get

Why? You can use ->first() with ->groupBy() just fine. I would only add a ->orderBy(), or a ->where() clause to ensure you always get the same date back.

->first() just adds ->take(1), runs ->get() and unpack the first record from the returned collection. You can check this on Laravel's source code:

https://github.com/laravel/framework/blob/5299c22321c0f1ea8ff770b84a6c6469c4d6edec/src/Illuminate/Database/Concerns/BuildsQueries.php#L288-L297

So there is not place you can use ->get() that you cannot use ->first().

Your first query using ->groupBy() should work fine with ->first()

1 like

rodrigo.pedra

4 years ago

Level 56

One thing which is not clear to me...

Do you want the sums for a single date?
Or you want all the sums grouped by date?

If it is the second option, it is not clear why your first query, the one with ->get() is not sufficient.

Also, there is no performance penalty, you are grouping in the database and just returning one record per date.

If you have a very large time period, add a ->whereBetween('date', [..., ...]) to filter the date period.

1 like

cooperino

4 years ago

Level 3

@rodrigo.pedra this is an example of what I need with the following table (and why first didn't work for me):

The table:

id|  col1  |   col2  |  col3  |      date
1 |   20   |    0    |   1    |   2022-05-25
2 |   25   |    10   |   26   |   2022-05-11
3 |   62   |    3    |   3    |   2022-04-17
4 |   4    |    54   |   11   |   2022-04-02
5 |   5    |    3    |   0    |   2022-03-29
6 |   0    |    3    |   12   |   2022-03-03

What I need is to sum each column, but separate by date, in this case, by month. So the result should be:

date: 2022-05
col1: 45
col2: 10
col3: 27

date: 2022-04
col1: 66
col2: 57
col3: 14

date: 2022-03
col1: 5
col2: 6
col3: 12

If I use first it only gives me one of the months

rodrigo.pedra

4 years ago

Level 56

@cooperino well you didn't said you wanted to group by month.

Using first you won't get just the first month, you will get the results for a single day.

You need to tell the database you want to group it by month.

If you are using MySQL, try this:

$results = MyModel::query()
    ->selectRaw("DATE_FORMAT(date, '%Y-%m-01') AS month")
    ->selectRaw('SUM(column1) as column1')
    ->selectRaw('SUM(column2) as column2')
    ->selectRaw('SUM(column3) as column3')
    ->groupBy('month')
    ->withCasts(['month' => 'date'])
    ->get();

1 like

Please or to participate in this conversation.