I got a really difficult task for me, and I'm out of ideas. I use mysql-8, Laravel 8.

This is a structure of the tables:

• user has many projects (every project has user_id field)
• project has many selections (every selection has project_id field)
• selection has one pump (selection has pump_id field), but it also has pumps_count field
• pump has many price_lists (every price_list has pump_id field)
• price_list and user also have country_id, currency_id, so we can say that pump has ONE price_list that corresponds to current user. (For example if current user has country_id = 1 and currency_id = 2 then we should select only price lists, where country_id = 1 and currency_id = 2 and there'll be only one)
• projects and selections both have created_at field (timestamp).

So, the main goals:

1. For current user get total price of projects, grouped by months
2. For current user get total price of projects with 'increasing' grouped by months

users

id	country_id	currency_id
1	1	2

projects

id	created_at	name	user_id
1	2021-10-01 00:00:01	proj1	1
2	2021-11-01 00:00:01	proj2	1
3	2021-12-01 00:00:01	proj3	1

selections

id	created_at	project_id	pump_id	pumps_count
1	2021-10-02 00:00:01	1	1	2
2	2021-11-03 00:00:01	2	2	1
3	2021-11-04 00:00:01	1	1	3
4	2021-12-05 00:00:01	3	1	3

pumps

id
1
2

pumps_price_lists

pump_id	country_id	currency_id	price
1	1	2	100
2	1	2	500

Result for the first part:

year-month	total_projects_price
2021-10	200
2021-11	800
2021-12	300

Result for the second part:

year-month	total_projects_price_with_increasing
2021-10	200
2021-11	1000
2021-12	1300

Total price for project = total price of selections of the project.

Total price of selection = total pumps price of the selection.

Total pumps price of the selection = selection.pumps_count * pump price.

Pump price = pumps_price_lists.price for pump where county_id and currency_id are the same as user has.

I'm looking at this and really don't know where to even start. Project becomes quite big, so I would want the solution to not has joins, but any ideas are welcome.

I tried something like this with joins, but it doesn't seem work:

select
    selections.id as 'selection_id', 
    DATE_FORMAT(selections.created_at, '%Y-%m') as 'month',
    pumps_price_lists.price,
    pumps_price_lists.currency_id as 'currency_id',
    sum(pumps_price_lists.price) over (ORDER by DATE_FORMAT(selections.created_at, '%Y-%m')) as 'sum'
from projects
join selections on selections.project_id = projects.id
join pumps on selections.pump_id = pumps.id
join pumps_price_lists on pumps_price_lists.pump_id = pumps.id
GROUP BY selections.id, pumps_price_lists.currency_id

I also tried to do something with Laravel Eloquent, but stucked and don't know what to do next.

Auth::user()->projects()
    ->with(['selections' => function ($query) {
        $query->select('id', 'pumps_count', 'pump_id', 'created_at', 'project_id');
    }, 'selections.pump' => function ($query) {
        $query->select('id');
    }, 'selections.pump.price_list', 'selections.pump.price_list.currency'
    ])->get(['id', 'created_at', 'user_id'])->...

I hope my explanation was clear

CodePudding user response：

In SQL I would do it something like

with data as 
(select
DATE_FORMAT(selections.created_at, '%Y-%m') as m,
sum(price * pumps_count) as total_price
from users
left join projects on (projects.user_id = users.id)
left join selections on (selections.project_id = projects.id)
left join pumps_price_lists on (pumps_price_lists.pump_id = selections.pump_id)
where users.id = 1
group by m
)

select *, sum(total_price) over (order by m) as cumulative_price from data