-
Notifications
You must be signed in to change notification settings - Fork 48
AggregateModel with referenced field in expression throwing error #1078
Copy link
Copy link
Closed
Labels
Description
Steps to reproduce:
- You have a model order_doc which contains a sample field
open_amount(ignore the name, in fact it is a "sign" that can be -1 or 1) - You have a hasMany model order_doc_position which contains the fields
article_geoupandtotal_netand which also adds the fieldopen_amountthrough a hasOne relation to order_doc (so$this->hasOne('order_doc_id', ['model' => OrderDoc::class])->addField('open_amount')) - You create an AggregateModel and run a aggregate function:
$orderDocPosAggregate = new AggregateModel($orderDocModel);
$orderDocPosAggregate->setGroupBy(['article_no'], [
'total' => ['expr' => 'sum([open_amount] * [total_net])']]);
If you place a referenced field like sign (coming from a parent table) into the aggregate expression sum(...), an erroneous SQL statement is created by Atk4/Data:
Doctrine\DBAL\Driver\Mysqli\Exception\ConnectionError [code: 1054]: Unknown column '_od_odp_cfb1e779955f.order_doc_id' in 'where clause'
If you omit the [open_amount] in the expression, there is no error.
Expected result:
It correctly multiplies the child table's total_net with the parent tables sign and sums this up.
For more background here is the complete error:
Exception Parameters
query | 'select count(*) from ((select 1 from (select `id`, `total_net`, `article_no`, `order_doc_id`, (select `open_amount` from `order_doc` `_od_odp_od_e20255aafc81` where (`is_deleted` = 0 and `id` = `_od_odp_cfb1e779955f`.`order_doc_id`)) `open_amount` from `order_doc_pos` `_od_odp_cfb1e779955f` order by `id`, `position`) `_tm` group by `article_group` order by sum((select `open_amount` from `order_doc` `_od_odp_od_e20255aafc81` where (`is_deleted` = 0 and `id` = `_od_odp_cfb1e779955f`.`order_doc_id`)) * `total_net`) desc)) `_tc`'
Reactions are currently unavailable