Skip to content

AggregateModel with referenced field in expression throwing error #1078

@mkrecek234

Description

@mkrecek234

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_geoup and total_net and which also adds the field open_amount through 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`'

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions