Use Of Mysql Function In Yii

Is there a way to use MySQL functions like

IF (field1 > 0, CONCAT(field3, field4), CONCAT(field5,field6)) AS newcolumn

I tried in this way, but that does not work:

 $criteria=new CDbCriteria;

 $criteria->select = 'IF (field1 > 0, CONCAT(field3, field4), CONCAT(field5,field6)) AS newcolumn'; 

Dear Friend

Did you declare the newcolumn as virtual property in the model?.

public $newcolumn;

Thank you for your answer.

It does not help to declare

public $newcolumn;

The error message says something like:

ActiveRecord "Noten" uses the invalid field "IF ... " 

why bother complicated with RDBM specific db functions?

how about afterFind function in your model?

Dear Friend

I just simulated your scenario.

I have a model Identity(id,age,first_name,last_name,family_name) .just made for an example.


public $full_name;

public function rules()


        return array(   


	    array('id, age, first_name, last_name, family_name,full_name', 'safe', 'on'=>'search'),



public function search()


		// Warning: Please modify the following code to remove attributes that

		// should not be searched.

		$criteria=new CDbCriteria;

        $criteria->select="*,IF(age>40,CONCAT(first_name,' ',family_name),CONCAT(first_name,' ',last_name)) AS full_name";






		$criteria->compare("IF(age>40,CONCAT(first_name,' ',family_name),CONCAT(first_name,' ',last_name))",$this->full_name,true); //YOU CAN USE FILTERS ON THE NEW COLUMN.

		return new CActiveDataProvider($this, array(


			'sort'=>array('attributes'=>array('full_name'=>array(),"*")),//YOU CAN SORT ON THE DERIVED COLUMN.




<?php $this->widget('zii.widgets.grid.CGridView', array(















)); ?>

We have not only mapped a virtual property to a calculated field in the table.

We also enabled sorting and filtering on the new field as well.


Many, many thanks. I appreciate your help very much.

I tried your suggestions. But nevertheless I get the error, that a field named "IF" does not exist. Now I assume, that this is caused by the with property of CDbCriteria, that I assigned in my code before the assignment of the select property.

Here is that code snippet. Please apologize that it is in German.

$criteria=new CDbCriteria;

$criteria->with = array('Komponist',




$criteria->select = "*,


                     CONCAT(Komponist.Vorname,' ',Komponist.Zuname),

                     CONCAT(Sammelheft.Komponist.Vorname,' ',Sammelheft.Komponist.Zuname)) AS Composer";

I suppose that it is not possible to mix the with property and the select property. Seems to me, that this cannot be mixed and I have to get data using MySQL queries with necessary JOINS instead of using with. Is that true?

Dear Friend

I do not know how you have defined relations.

Anyway Try to add the following line if you have defined HAVE_MANY or MANY_MANY relations.



There are only this relations in model Noten:

public function relations() {

   return array(

       'Komponist'=>array(self::BELONGS_TO, 'Komponisten', 'Komponist_Id'),

       'Verlag'=>array(self::BELONGS_TO, 'Verlage', 'Verlag_Id'),

       'Sammelheft'=>array(self::BELONGS_TO, 'Sammelhefte', 'Sammelheft_Id'),



IIRC, you may have to embed the select statement in a PHP array().

/Tommy (only programming C++/Delphi since last Februar)

No, it can also be a comma separated string: Class Reference of CDbCriteria