Yii 2 Aggregational Ar Queries

Yii 2.0 General Discussions
1

To all the Yii gurus out here what’s the best approach (rather the Yii way) to do an aggregational query using an Yii AR class. Example SQL below:

[sql]

SELECT max(id) from tbl_customer WHERE (some_condition);

[/sql]

It most definitely can be done through Yii Query Builder and Command very easily.

However, what’s the best/shortest way to do this using Yii ActiveRecord (AR). Let’s say the model is [font=“Courier New”]Customer[/font] for the table [font=“Courier New”]tbl_customer[/font].

The Yii 2 (AR) exposes two methods: [font="Courier New"]find()[/font] and [font="Courier New"]findBySql()[/font] for getting an ActiveQuery instance.

What’s the simplest Yii way to get to the requirement?

2

Technically you don’t need AR here (because there’s no way to store this data back), so you can use only AQ’s methods.

Notice that ActiveQuery extends Query, and this gives you a lot of possibilities.

For example,




$max = MyModel::find() // getting AQ instance

    ->myScope() // applying scopes and all the cool stuff

    ->andWhere(['some' => 'condition']) // also some conditions

    ->select('max(id)') // we need only one column

    ->scalar(); // cool, huh?
3

Thanks… just wondering if this is also correct then (looks this is also possible as per the api docs):




$max = Model::find()->select('id')->max();
4

Sure. Also there’s sum, average, min, max and so on (take a look at API).

The thing is, if you need something more complex than boring aggregates, you can get it very easy.

5

Hi Katik, i code same you but not working, please help me




$max = Term::find()

       ->andWhere(['taxonomy_id'=>$model->taxonomy_id])

       ->andWhere(['parent'=>$model->parent])

       ->select('order')->max();
6

try this:




$max = Term::find()

       ->andWhere(['taxonomy_id'=>$model->taxonomy_id])

       ->andWhere(['parent'=>$model->parent])

       ->select('order')->max('order');
7

If you use one of the ActiveQuery’s aggregate methods, you may omit the call of select():




$max = Term::find()

   	->andWhere(['taxonomy_id'=>$model->taxonomy_id])

   	->andWhere(['parent'=>$model->parent])

   	->max('order');
8

I did it on ActiveRecord

//method> in model


public function PriceMin($index)

    {

        return $this->find()->where(['d_id' => $index])->min('price');

    }


    public function PriceMax($index)

    {

        return $this->find()->where(['d_id' => $index])->max('price');

    }

//foreach> in view


<?=$advert->priceMin($devic->d_id);?>

<?=$advert->priceMax($devic->d_id);?>