How can i use MAX() in find method?

Yii 1.1 General Discussion
1

I want to get the max value of some field like ordering,in sql query,i can use


SELECT MAX(ordering) FROM tableName

but i don’t know in Yii use find how to do?

and another,can i use findByAttributes(‘ordering’,$condition,$params)? filed ordering has no indexed,i saw the api,it’s said ‘indexed by attribute names’,is attribute names must indexed?

please help!

Thanks!

2

[quote=“holala, post:1, topic:52981”]

I want to get the max value of some field like ordering,in sql query,i can use


SELECT MAX(ordering) FROM tableName

but i don’t know in Yii use find how to do?

no

use




...findByAttributes(array('field'=>'value','field2'=>'value2'),$condition,$params);

//the array is indexing by attribute Name
3

Thank you!

4

You should also define a $maxOrdering attribute in your active record class

5

[s]how?

an example please

thanks

[/s]now I understand what you mean

6

See: http://www.yiiframework.com/doc/guide/database.arr

Statistical Query

You just need to change the ‘select’ option from COUNT(*) to MAX(ordering)

7

I had the same problem so let’s give a example for reference




$model = new Model;

$criteria=new CDbCriteria;

$criteria->select='max(column) AS maxColumn';

$row = $model->model()->find($criteria);

$somevariable = $row['maxColumn'];

(of course the name ‘maxColumn’ after the ‘AS’ is your choice)

Now in order for $row[‘maxColumn’] to be a valid command (and not throw an error) you should define inside your class a variable as




public $maxColumn;

It is supposed that all the other columns are already defined automatically as public $column_name1, public $column_name2 and so on… (<–this is true even if you change the table inside SQL)

I am not sure I get Roberto’s solution

Could someone elaborate on that please? :)

8

That would be the select property of CStatRelation.

IIRC, include a select member in the array declaring the relationship.

/Tommy

9

I don’t see this is a relationship, could be a relationship with the same model?

Like this





class Employer {


...


public function relations(){


...




'maxNumZZZ' => array(self::STAT, 'Employer', array('idXXX','idYYY'), 'select'=> 'MAX(numZZZ)','defaultValue'=>0),


}


...


}
10

As far as I can tell, the only advantage of using relational stats is to avoid writing your joins by hand (much like the standard relations allow us to avoid writing those queries by hand). The thing is that if you already have a standard relation setup it seems a little redundant to then define a second set of relations just for stats. I prefer to just make a DBCriteria instance and set the select property of that. This will work weather or not there are any relations and it’ll work using the relations you’ve already setup.