How to retrieve a max value in a table

Hi, let’s say we have a “Post” model with the attributes “id” and “value”. What would be the best way to retrieve the row with the highest value? Just like the following query would do it: “SELECT MAX(value) FROM post”

Is there a yii-style way to do this? I guess I would like to be able to retrieve the value like this:


Post::model()->totalCount()

or


Post::model()->totalCount

I tried with statistical query without luck, since it seems to be oriented to retrieve statistics about the child objects of ONE model record, not about all the model records.

I don’t know that you would use a relation for this because the data isn’t really related, Yii needs to know how it’s related. In this case Post is related to Post with id, so that is only ever going to return itself.

Now just say you had a User and Post BELONGS_TO User, then you could (in User):


'maxPost' => array(self::STAT, 'Post', 'userId', 'select' => 'MAX(id)');

But this is only going to give the maximum Post id of the User.

No good for you - I think this makes more sense outside of an ActiveRecord (because an AR is all about discrete models of data).

Maybe instead you could give Post a new function




public static function maxId() {

  return Yii::app()->db->createCommand()

    ->select('MAX(*)')

    ->from('post')

    ->queryScalar();

}



Thank you for your help, luke. Your suggestion is working fine to me.

You have a small typo though:


->select('MAX(*)')

in this example, should be:


->select('MAX(value)')

I’m accesing the function this way:


Post::maxId()

Is this how you expected to access the function? or is it:


Post::model()->maxId()

? both work and I don’t see the difference :confused:

Indeed, the "statistical query" approach seems to be for relations, since it is actually defined inside the "relations" section of the model.

Yeah I didn’t run the code so the typo doesn’t surprise me, I’m terrible at parsing language in my head.


Post::maxId()

is fine, the reason Yii uses


Post::model()->find()

is because Yii is pre php 5.3 and cannot employ the


static::aMethod()

scope resolution keyword, look up late static binding if you want to know more. Needless to say it doesn’t impact your code.

Oh so that’s it. I was afraid of negative and silent side effects by not using the right format when calling the function.

Thanks for the clarification. Really appreciated. :]