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.
MODEL
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('id',$this->id);
$criteria->compare('age',$this->age);
$criteria->compare('first_name',$this->first_name,true);
$criteria->compare('last_name',$this->last_name,true);
$criteria->compare('family_name',$this->family_name,true);
$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(
'criteria'=>$criteria,
'sort'=>array('attributes'=>array('full_name'=>array(),"*")),//YOU CAN SORT ON THE DERIVED COLUMN.
));
}
VIEW
<?php $this->widget('zii.widgets.grid.CGridView', array(
'id'=>'identity-grid',
'dataProvider'=>$model->search(),
'filter'=>$model,
'columns'=>array(
'id',
'age',
'first_name',
'last_name',
'family_name',
'full_name',//JUST ADD THE VIRTUAL PROPERTY.
array(
'class'=>'CButtonColumn',
),
),
)); ?>
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.
Regards.
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',
'Verlag',
'Sammelheft',
'Sammelheft.Verlag'=>array('alias'=>'Verlag_Sammelheft'));
$criteria->select = "*,
IF(t.Komponist_ID>0,
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.
$criteria->together=true;
Regards.
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