"SQL case" syntax in Yii

Yii 1.1 General Discussion
1

Hello,

Do you know the syntax for "case" in Yii?

I used this, but it does not work




$model->getDbConnection()->createCommand('SELECT CASE CodeAgence WHEN NULL THEN CodeClient  ELSE CodeAgence  END "Client / Agence" FROM Reglement')->queryAll();

Thanks for help :)

2

This works :




$model->getDbConnection()->createCommand('SELECT CASE WHEN CodeAgence IS Null THEN CodeClient ELSE CodeAgence END "Client / Agence" FROM Reglement')->queryAll();
3

Why not to make a usual findAll and then use a getter method?




$models=Reglement::model()->findAll();

In the model:




public function getCode()

{

   if ($this->CodeAgence!==null)

       return $this->CodeAgence;

   else

       return $this->CodeClient;

}

In any view you can now use $model->code like any other property.

4

Thank you zaccaria, is the solution I’m looking for.

I made the changes to the "CGridView" like this :




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

	'id'=>'reglement-grid',

	'dataProvider'=>$model->search(),

	'filter'=>$model,

	'columns'=>array(

		array('name'=>'CodeAgence','value'=>$model->code),

	),

)); ?>

but it does not work, it still displays the "CodeAgence" even if it is NULL

5

In the model Reglement :




	public function getCode($CodeClient,$CodeAgence)

	{

	   if ($CodeAgence!==null)

	       return $CodeAgence;

	   else

	       return $CodeClient;

	}

In the view Admin




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

        'id'=>'reglement-grid',

        'dataProvider'=>$model->search(),

        'filter'=>$model,

        'columns'=>array(

                array('name'=>'CodeAgence','value'=>'Reglement::model()->getCode($data->CodeClient,$data->CodeAgence)'),

        ),

)); ?>

This works, but is this the best solution ??

6

Try something like:




        public function getCode()

        {

           if ($this->CodeAgence!==null)

               return $this->CodeAgence;

           else

               return $this->CodeClient;

        }

The function getCode will be invoked on the actual instance of the model, so the properties codeAgence and codeClient will be available in $this.




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

        'id'=>'reglement-grid',

        'dataProvider'=>$model->search(),

        'filter'=>$model,

        'columns'=>array('code'),

        ),

)); ?>

The magic getter method will make so that the property code will be accessed using the magic function getCode we just wrote.

7

Thank you very much zaccaria :)

It works well :)

Just a small change in columns :




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

        'id'=>'reglement-grid',

        'dataProvider'=>$model->search(),

        'filter'=>$model,

        'columns'=>'code',

        ),

)); ?>
8

The filter does not work :(




array('name'=>'Code','header'=>'Client/Agence'),







	public function search()

	{

                $criteria=new CDbCriteria;

		$criteria->compare('CodeClient',$this->Code,true);

		$criteria->compare('CodeAgence',$this->Code,true,'OR');

                .......

	}
9

You should do the filter by yourself.

In CDataGrid:




'columns'=>array(

array('name'=>'code', 'filter'=>CHtml::activeTextField($model, 'CodeClient')

)

This will make the filter to send the data in post to the property CodeClient.

Now you can write the filter relying on $this->codeClient.

10

Hi

You may try to see solution that we’ve worked out I think on similiar problem. Hope that helps.

best,

Tom

11

Thank you :) This works