Level 102
You can give them aliases like this
Shop::withSum([ 'sales as sales_foobar_sum' => function
Jan 20, 2023
9
Level 1
withSum() for many columns on relation
How can I get the sum for several columns with only one subquery?
Shop::withSum([
'sales' => function ($q) use ($dateFrom, $dateTo) {
$q->whereBetween('date', [
$dateFrom->format('Y-m-d'),
$dateTo->format('Y-m-d')
]);
}
], 'sum')
->withSum([
'sales' => function ($q) use ($dateFrom, $dateTo) {
$q->whereBetween('date', [
$dateFrom->format('Y-m-d'),
$dateTo->format('Y-m-d')
]);
}
], 'checks')
->toSql();
This code returns SQL like this:
select `shops`.*,
(select sum(`sales`.`sum`)
from `sales` where `shops`.`bx_id` = `sales`.`shop_bx_id`
and `date` between ? and ?) as `sales_sum_sum`,
(select sum(`sales`.`checks`)
from `sales` where `shops`.`bx_id` =`sales`.`shop_bx_id`
and `date` between ? and ?) as `sales_sum_checks` from `shops`
But I need SQl like this:
select `shops`.*,
(select sum(`sales`.`checks`) , sum(`sales`.`sum`)
from `sales` where `shops`.`bx_id` =`sales`.`shop_bx_id`
and `date` between ? and ?) as `sales_sum_checks` from `shops`
Level 1
@Sinnbeck If understand correctly, you suggest to write like this:
Shop::withSum([
'sales as sum' => function ($q) use ($dateFrom, $dateTo) {
$q->whereBetween('date', [
$dateFrom->format('Y-m-d'),
$dateTo->format('Y-m-d')
]);
},
'sales as checks' => function ($q) use ($dateFrom, $dateTo) {
$q->whereBetween('date', [
$dateFrom->format('Y-m-d'),
$dateTo->format('Y-m-d')
]);
},
], 'sum')->toSql();
But this code returns this:
select `shops`.*, (select sum(`sales`.`sum`) from `sales` where `shops`.`bx_id` = `sales`.`shop_bx_id` and `date`
between ? and ?) as `sum`, (select sum(`sales`.`sum`) from `sales` where `shops`.`bx_id` = `sales`.`shop_bx_id` and
`date` between ? and ?) as `checks` from `shops`
Level 102
@AleksCzech Ok I see you updated your post
What is the expected final output ? Seems you want to do a subselect of 2 "columns" and have them somehow show as one?
Level 1
@Sinnbeck I have a six columns for SUM() in related model In regular SQL my query looks like this:
SELECT * FROM `shops`
LEFT JOIN (
SELECT SUM(`sum`) as `sum`,
SUM(`checks`) as checks,
SUM('products') as `products`,
... etc
WHERE `date` BETWEEN ? and ?
GROUP by(shop_id) as tmp)
ON shops.shop_id = tmp.shop_id
My SQL query has only one subquery with all SUM(), but when I try do like this in eloquent, I get subquer for each SUM()
Level 102
@AleksCzech That sounds like the kind of query I would not convert to eloquent. I assume you need it just for the sums alone ?
Level 1
@Sinnbeck Only for sums on this relation Will I have to do a subquery using join and group by? Is it possible to somehow use the fact that the models are related?
Level 1
@Sinnbeck Is it possible to build a query like this:
Shop::with(???)->get();
Level 102
@AleksCzech You will need to some "crazy" things to get eloquent to behave like that. You cannot as far as I know pass a query builder instance to join, so you will need to write a query, convert it to a string and then bind the parameters.
->join() takes the table as the first argument, so you would need something like this (untested)
$join = Shop::withSum([
'sales' => function ($q) use ($dateFrom, $dateTo) {
$q->whereBetween('date', [
$dateFrom->format('Y-m-d'),
$dateTo->format('Y-m-d')
]);
}
], 'sum')
->withSum([
'sales' => function ($q) use ($dateFrom, $dateTo) {
$q->whereBetween('date', [
$dateFrom->format('Y-m-d'),
$dateTo->format('Y-m-d')
]);
}
], 'checks');
$shops = Shop::query()
->join($join->toSql(), '')
->mergeBindings($join->getQuery())
->get();
Level 102
@AleksCzech Not quite sure what you mean: But ->with() does a completely seperate query. It does not use join!
Please or to participate in this conversation.