REST Api and aggregates

Hi,

I move this topic to general discussion as it was left unanswered in the REST forum…

For whatever reason I am unable to force REST Api to return aggregates from related record. ActiveController is unwilling to show aggregates like SUM in related records.

For instance I have a person who has (many) orders and each order has (many) items. I need to show a total for each order, so I write:

$model = Person::find()->with(['orders' => function($q) {
    $q->joinWith(['items' => function($q) {
        $q->select('amount')->sum();
    }]);
}])->where(['person.id' => $pid])->one();

$model->toArray([], ['orders.items]);

But this does not work!

Any idea what is the right way (besides using a database view as a relational model)?

Thanks ahead for any clue!

Guide > ActiveRecord > Selecting extra fields
https://www.yiiframework.com/doc/guide/2.0/en/db-active-record#zhui-jianofirudowo-xuan-zesuru

There you see a sample code that handles a person’s order count. You could easily modify it to handle a person’s order sum.

Hi,

I have tested it and actually I am unable to get “extra field” showing on the ActiveController result :frowning:

Any idea???

Oh, I’m sorry, but how can I have any idea when you just said “it didn’t work”?

Simply - the resulting .json contains no aggregates :-/

I have no clue. You have to give us more information.

OK - I’ve got it!

The clue is ActiveController needs to be supplied with a model, where “extra attributes” are explicitely added to “fields” functions like this:

private $_sum;

public function setSum($val)
{
$this->_sum = $val;
}

public function getSum()
{
return -$this->_sum;
}

public function fields()
{
$fields = parent::fields();
$fields[‘sum’] = ‘sum’;
return $fields;
}

Now, the aggregated sum attribute is working as expected.

1 Like