Does DB raw sum function help optimized data?-CodePudding

In the application, we have a feature where it will display all the years as tab. Each tab has amount(where the amount is the summarized) but at the same time it is have data.

Scenario

Just to give a background about the application, it is a car seller

Imagine you are the customer, you started to use this application way back 2019. You clicked this module to check the summarized of each year

Let's assume we have 2500 data for each year.

In current year I want to display the total amount of all previous years

2023 tab is the active, from 2019 - 2022 you have sold 300,000 each year

In getting the previous amount, I used the DB raw sum function to get the summation.

Question: Does DB raw sum function lessen the heavy query to get the summation?

CodePudding user response：

You can accomplish this with eloquent, without having to load all of the inner relationship data.

Normally programmers do the following:

Model:

public function children()
{
    return $this->hasMany(Children::class);
}

In the Controller:

$models = Model::with('children')->get();

Then they load the data normally in blade and count:

@foreach($models as $model)
   <p>{{ $model->children->count() }}</p>
@endforeach

Instead you can do the following:

In the Controller:

$models = Model::withCount(['children'])->get();

In the blade you load the data with the count:

@foreach($models as $model)
   <p>{{ $model->children_count  }}</p>
@endforeach

The withCount method will place a {relation}_count attribute on the resulting models

Use Barry DebugBar to test out the speed with heavy data, you will notice that doing it this way will reduce heavily the time needed to load the relationship, since you are not loading all of the inner relationship, rather you are just counting the inner relationship and displaying it.

For withCount Reference: https://laravel.com/docs/9.x/eloquent-relationships#aggregating-related-models

For withSum Reference: https://laravel.com/docs/9.x/eloquent-relationships#other-aggregate-functions

You can accomplish the same thing using withSum() and you can do this for deeper level of relationships as well.

CodePudding user response：

Create and maintain a summary table, with year, and the sum for that year.

Working from that table, the query would be much faster, even if you had to do multiple summing.

Or, that table could have the cumulative amount since day-1. Then, the sum for "prior to 2023" is "cumulative through 2022" minus the "cumulative through 2018".

Summary Tables