Geek Answers Handbook

Using Yii Active Record to find a maximum of one column

How can I find the maximum value of a single column in a Yii Active Record database?

I can do it using pure sql and createCommand method, but I want to do it using Active Record and CDbCriteria class.is is there any way to do this?

+4
source share
6 answers

You want to change the selection criteria as follows.

 $model = new Model; $criteria=new CDbCriteria; $criteria->select='max(column) AS maxColumn'; $row = $model->model()->find($criteria); $somevariable = $row['maxColumn'];

Link:

http://www.yiiframework.com/forum/index.php/topic/6730-how-can-i-use-max-in-find-method/page_view_findpost_p_80659

+10
source

This avoids creating an unnecessary temporary object:

 $criteria = new CDbCriteria; $criteria->select = 'max(column)'; // additional where conditions, if you so wish $criteria->addColumnCondition(array('published' => 1)); $model = SomeModel::model(); $value = $model->commandBuilder->createFindCommand( $model->tableName(), $criteria)->queryScalar();
+6
source

I used this in case of integer value

  $max = Yii::app()->db ->createCommand("SELECT MAX(TableCol) FROM tableName") ->where('cond1=:cond1', array(':cond1'=>$cond1)) ->andWhere('cond2=:cond2', array(':cond2'=>$cond2)) ->queryScalar();

OR

This case I used in the case of dateTime

 $maxAnsTime = Yii::app()->db ->createCommand("SELECT tableColumn FROM tableName ORDER BY tableColumn DESC LIMIT 1") ->where('cond1=:cond1', array(':cond1'=>$cond1)) ->queryRow();

$ max varible will give you maximum results.

You can also use the binding parameter and put the where clause in your createCommand.

I also found that active yii records are not the best things used for a larger dataset.

0
source

If you need a WHERE clause:

In the controller:

 $max = $model->getMax($filter);

In the model:

 public $maxColumn = 0; public function getMax($filter = null) { /* Get max in column1 WHERE column2 = $filter */ $criteria = new CDbCriteria; $criteria->select = 'MAX(t.column1) as maxColumn'; $criteria->condition = 't.column2 LIKE :parm'; $criteria->params = array(':parm'=>$filter); $tempmodel = $this->find($criteria); $max = $tempmodel['maxColumn']; return $max; }
0
source

You can simply limit the result and order to DESC, for example

 $criteria = new CDbCriteria; $criteria->order = 'column DESC'; $row = Model::model()->find($criteria); $somevariable = $row->column;
0
source

It is he.

 function getMaxId( $modelName ){ $criteria = new CDbCriteria; $criteria->order = 'id DESC'; $model = $modelName::model()->find( $criteria ); return $model->id; }
0
source

More articles:


All Articles