AggregateModel with referenced field in expression throwing error

[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`'

[mvorisek]

@mkrecek234 thank you for describing the problem, please add here a small repro code that can be copied and run.

[mkrecek234]

Please check this sample code based on a MySQL database as described showing the error:

class Account extends Model
{
    public $table = 'account';

    protected function init(): void
    {
        parent::init();
        $this->addFields(['name', 'fx_rate' => ['type' => 'float']]);
    }
}

class Booking extends Model
{
    public $table = 'booking';

    protected function init(): void
    {
        parent::init();
        $this->addFields(['booking_group', 'value' => ['type' => 'float']]);
        $this->hasOne('account_id', ['model' => [Account::class]])->addField('fx_rate');
    }
}

    public function testAggregate1078(): void
    {
        $m = new Model($this->db, ['table' => 'account']);
        $m->addField('name');
        $m->addField('fx_rate', ['type' => 'float']);
        $this->createMigrator($m)->create();

        $m->import([
            ['name' => 'Test Account', 'fx_rate' => 1.2],
        ]);

        $m = new Model($this->db, ['table' => 'booking']);
        $m->addField('account_id', ['type' => 'integer']);
        $m->addField('booking_group');
        $m->addField('value', ['type' => 'float']);
        $this->createMigrator($m)->create();

        $m->import([
            ['account_id' => 1, 'booking_group' => 'Group1', 'value' => 123],
            ['account_id' => 1, 'booking_group' => 'Group1', 'value' => 456],
            ['account_id' => 1, 'booking_group' => 'Group2', 'value' => 789],
        ]);

        $accountModel = new Account($this->db);
        $bookingModel = new Booking($this->db);

        $bookingAggregate = new AggregateModel($bookingModel);
        $bookingAggregate->setGroupBy(['booking_group'], [
            'total' => ['expr' => 'sum([value])'], ]);

        foreach ($bookingAggregate as $booking) {
            echo 'Result: ' . $booking->get('booking_group') . ' | ' . $booking->get('total');
        }

        echo 'That worked..';

        $bookingAggregate = new AggregateModel($bookingModel);
        $bookingAggregate->setGroupBy(['booking_group'], [
            'total' => ['expr' => 'sum([fx_rate] * [value])'], ]);

        foreach ($bookingAggregate as $booking) {
            echo 'Result: ' . $booking->get('total');
        }
        echo 'That did not work..';
    }