Compare And Addcondition Together

Yii 1.1 General Discussion
1

Hello,

I try to filter and sort in my cgridview through relations. I did it with sucess with most of my models but in one of them I have a complex search function where i can not do it. Here is my search function:

public function search()

{

$alta=AccionesPostventa::model()->FindByAttributes(array(‘accion’=>‘Alta de terminal’))->id;

$baja=AccionesPostventa::model()->FindByAttributes(array(‘accion’=>‘Baja de terminal’))->id;

$cambio=AccionesPostventa::model()->FindByAttributes(array(‘accion’=>‘Cambio de terminal’))->id;

$modificacion=AccionesPostventa::model()->FindByAttributes(array(‘accion’=>‘Modificación de terminal’))->id;

$sql="

SELECT b.id FROM (


SELECT * FROM (SELECT * 


FROM postventa


WHERE acciones_postventa_id =:alta


OR acciones_postventa_id =:baja


OR acciones_postventa_id =:cambio


OR acciones_postventa_id =:modificacion


ORDER BY fecha_accion DESC ) a


GROUP BY num_linea_asociada_id


HAVING acciones_postventa_id<>:baja) b";

$criteria=new CDbCriteria;

$criteria->with=array(‘terminales’,‘numerosTelefonos’);

$criteria->addCondition('t.id in ( ‘.$sql.’ ) ');

$criteria->with=array(‘terminales’);

$criteria->compare(‘terminales.imei’,$this->nuevoterminal);

$criteria->compare(‘numerosTelefonos.numero_telefono’,$this->lineaasociada);

$criteria->params=array(’:alta’=>$alta, ‘:baja’=>$baja, ‘:cambio’=>$cambio, ‘:modificacion’=>$modificacion);

$criteria->order=‘fecha_accion asc’;

$acciones=new CActiveDataProvider($this,

			array('criteria'=>$criteria,


				'sort'=>array(


                                       'attributes'=>array(


                                        'nuevoterminal'=>array(


                                            'asc'=>'terminales.imei',


                                            'desc'=>'terminales.imei DESC',


				     ),


                                         'lineaasociada'=>array(


                                            'asc'=>'numerosTelefonos.numero_telefono',


                                            'desc'=>'numerosTelefonos.numero_telefono DESC',


				     ),


				     '*',


				),


			)


	));

return $acciones;

}

}

This search functions goes well the first time and my cgridview shows the correct values but when i try to filter with a value, the result query is the same as without filter. I traced the queries and ther is no where condition added for the ->compare in $criteria. The only condition in where is the condition in addCondition.

in my view I have:

array(‘name’=>‘nuevoterminal’,

            'value'=> '$data->terminales->imei',


            'header'=>'Imei',


            'htmlOptions' => array('style' => 'width:130px'),


        ),

Where nuevoterminal is a public model variable which it is include in rules as safe in search.

Can anyone help me why compares does not appear in queries?

Thanks

2

Do you need to set the third compare parameter to true to allow partial matching?


$criteria->compare('terminales.imei',$this->nuevoterminal,true);
3

Hi J.Fornes,

Did you check "/protected/runtime/application.log" for errors?

  1. The 2nd "with" will overwrite the 1st "with", and you may get "column not found" error on "numerosTelefonos.numero_telefono".

  2. "params" should be set before you use "compare", or you may get "number of binding parameters mismatching" error.

  3. “numerosTelefonos” looks like a HAS_MANY relation … you’ll probably need to specify “together” to true.

Note that “compare” will not create any SQL phrase if the parameter to be compared is empty, so it’s possible that your initial search runs well without an error. But when $this->nuevoterminal or $this->lineaasociada is not empty, then you may get an error.

4

I am sorry but making the post I uncommented some code lines. The original code did’nt have any problem but it does not function:




public function search()

	{

		$alta=AccionesPostventa::model()->FindByAttributes(array('accion'=>'Alta de terminal'))->id;

		$baja=AccionesPostventa::model()->FindByAttributes(array('accion'=>'Baja de terminal'))->id;

		$cambio=AccionesPostventa::model()->FindByAttributes(array('accion'=>'Cambio de terminal'))->id;

		$modificacion=AccionesPostventa::model()->FindByAttributes(array('accion'=>'Modificación de terminal'))->id;


		$sql="

			SELECT b.id FROM (

			SELECT * FROM (SELECT * 

			FROM postventa

			WHERE acciones_postventa_id =:alta

			OR acciones_postventa_id =:baja

			OR acciones_postventa_id =:cambio

			OR acciones_postventa_id =:modificacion

			ORDER BY fecha_accion DESC ) a

			GROUP BY num_linea_asociada_id

			HAVING acciones_postventa_id<>:baja) b";

		

		$criteria=new CDbCriteria;

		$criteria->with=array('terminales','numerosTelefonos');

		$criteria->addCondition('t.id in ( '.$sql.' ) ');

		$criteria->compare('terminales.imei',$this->nuevoterminal);

		$criteria->compare('numerosTelefonos.numero_telefono',$this->lineaasociada);

		$criteria->params=array(':alta'=>$alta, ':baja'=>$baja, ':cambio'=>$cambio, ':modificacion'=>$modificacion);	

		$criteria->order='fecha_accion asc';

		

		$acciones=new CActiveDataProvider($this, 

								array('criteria'=>$criteria,

										'sort'=>array(

									        		'attributes'=>array(

									            		'nuevoterminal'=>array(

									                		'asc'=>'terminales.imei',

									                		'desc'=>'terminales.imei DESC',

									            		),

									            		'lineaasociada'=>array(

									                		'asc'=>'numerosTelefonos.numero_telefono',

									                		'desc'=>'numerosTelefonos.numero_telefono DESC',

									            		),

									            		'*',

									        		),

												)

									));	

		return $acciones;

	}

I do not need the ,true parameter to get like. I need where condition.

I do not have any error in application log. I traced sql and the problem is like it does not use the compare condition, like if variables are empty.

Here is the sql trace after entering a value in the filter:





SELECT `t`.`id` AS `t0_c0`, `t`.`terminales_id` AS `t0_c1`,

`t`.`acciones_postventa_id` AS `t0_c2`, `t`.`fecha_accion` AS `t0_c3`,

`t`.`num_orden` AS `t0_c4`, `t`.`num_linea_asociada_id` AS `t0_c5`,

`t`.`nuevo_terminal_id` AS `t0_c6`, `t`.`imeinuevo` AS `t0_c7`,

`terminales`.`id` AS `t1_c0`, `terminales`.`modelos_id` AS `t1_c1`,

`terminales`.`color_id` AS `t1_c2`, `terminales`.`imei` AS `t1_c3`,

`terminales`.`num_serie` AS `t1_c4`, `terminales`.`fecha_entrada` AS

`t1_c5`, `terminales`.`estado_id` AS `t1_c6`, `terminales`.`fecha_estado`

AS `t1_c7`, `terminales`.`linea_original_id` AS `t1_c8`,

`terminales`.`pedido_id` AS `t1_c9`, `terminales`.`comentario` AS `t1_c10`,

`numerosTelefonos`.`id` AS `t2_c0`, `numerosTelefonos`.`cliente_id` AS

`t2_c1`, `numerosTelefonos`.`numero_telefono` AS `t2_c2`,

`numerosTelefonos`.`extension` AS `t2_c3`, `numerosTelefonos`.`fecha_alta`

AS `t2_c4`, `numerosTelefonos`.`fecha_estado` AS `t2_c5`,

`numerosTelefonos`.`estado_id` AS `t2_c6`, `numerosTelefonos`.`usuario` AS

`t2_c7`, `numerosTelefonos`.`tarifa_id` AS `t2_c8`,

`numerosTelefonos`.`ultimo_cambio` AS `t2_c9` FROM

`db1346296_terminales`.`postventa` `t`  LEFT OUTER JOIN `terminales`

`terminales` ON (`t`.`nuevo_terminal_id`=`terminales`.`id`)  LEFT OUTER

JOIN `db1346296_telefonia`.`numeros_telefonos` `numerosTelefonos` ON

(`t`.`num_linea_asociada_id`=`numerosTelefonos`.`id`)  WHERE (t.id in ( 

			SELECT b.id FROM (

			SELECT * FROM (SELECT * 

			FROM postventa

			WHERE acciones_postventa_id =:alta

			OR acciones_postventa_id =:baja

			OR acciones_postventa_id =:cambio

			OR acciones_postventa_id =:modificacion

			ORDER BY fecha_accion DESC ) a

			GROUP BY num_linea_asociada_id

			HAVING acciones_postventa_id<>:baja) b ) ) ORDER BY fecha_accion asc

LIMIT 10

As you can see there is no where condition associated to compares.

The relations are all belongs_to:




public function relations()

	{

		return array(

			'terminales' => array(self::BELONGS_TO, 'Terminales', 'nuevo_terminal_id'),

			'terminalactual' => array(self::BELONGS_TO, 'Terminales', 'terminales_id'),

			'accionesPostventa' => array(self::BELONGS_TO, 'AccionesPostventa', 'acciones_postventa_id'),

			'numerosTelefonos' => array(self::BELONGS_TO, 'NumerosTelefonos', 'num_linea_asociada_id'),

			

		);

	}

and in the view, I use the model variables nuevoterminal and lineaasociada:




<?php 

    

    $this->widget('bootstrap.widgets.TbGridView', array(

    'type'=>'striped condensed',

    'filter'=>$model,

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

    'pager'=>'LinkPager',

    'template'=>"{items}{pager}",

    'columns'=>array(

        array('name'=>'fecha_accion',

            'value'=>'Yii::app()->dateFormatter->formatDateTime(strtotime($data->fecha_accion), "medium", null)',

            'header'=>'Fecha',

            'htmlOptions' => array('style' => 'width:80px'),

            ),

        array('name'=>'nuevoterminal', 

                'value'=> '$data->terminales->imei',

                'header'=>'Imei',

                'htmlOptions' => array('style' => 'width:130px'),

            ),

        array('name'=>'nuevo_terminal_id', 

                'value'=> 'CHtml::link($data->terminales->idModelo->modelo, Yii::app()->createUrl("terminales/view",array("id"=>$data->nuevo_terminal_id)))',

                'header'=>'Modelo',

                'type'=>'raw',

                'htmlOptions' => array('style' => 'width:150px'),

            ),

         array('name'=>'lineaasociada',

                'header'=>'Línea asociada',

                'value'=>'CHtml::link($data->numerosTelefonos->numero_telefono, Yii::app()->createUrl("numerosTelefonos/view",array("id"=>$data->num_linea_asociada_id)))',

                'type'=>'raw',

                 'htmlOptions' => array('style' => 'width:120px'),

            ),

     ),

)); ?>

Always i have the same result. First time i get all the right values but when i enter some value in the activetext filter from the cgridview i get the same gridview without filtering.

Does anyone know any way to solve this?

5

Did you set ‘nuevoterminal’ and ‘lineaasociada’ safe on search scenario in the rules?

If not, you won’t get anything other than empty, and there will be no place for ‘compare’ to act.

And once you have solved that, then you’ll get the error of 2).

6

Yes, here are my rules:




			array('imeinuevo','numerical','integerOnly'=>true),

			array('imeinuevo', 'length', 'is'=>15),

			array('imeinuevo', 'mi_valida_imei'),

			array('fecha_accion, terminales_id, nuevo_terminal_id, crearterminal, crearmovimientos', 'safe'),array('id, terminales_id, acciones_postventa_id, fecha_accion, num_orden, num_linea_asociada_id, imeinuevo', 'safe', 'on'=>'search'),

			array('imeiterminalactual, nuevoterminal, lineaasociada', 'safe', 'on'=>'search'),

			array('nuevoterminal','safe'),
7

Hmm, it’s strange.

I guess that ‘nuevoterminal’ and ‘lineaasociada’ are always empty when you call ‘search’, whatever values you have input for them, that’s why ‘compare’ will not create any sql phrases for filtering.

I think that at some point you are failing to assign the values to them.

  1. I’d like to see the controller code.

  2. How do you define ‘nuevoterminal’ and ‘lineaasociada’? Are they public variables? The properties implemented with getter and setter may not work as expected in this situation.

8

Yes, I declared these variables as public:




class Postventa extends CActiveRecord

{

	

	public $crearterminal; 		//Variable para el checkbozx

	public $crearmovimientos; 	//Variable para el checkbox

	public $imeiterminalactual;	//Variable para filtrar el admin

	public $nuevoterminal;		//Variable para filtrar el admin

	public $lineaasociada;

My controller is so easy:




public function actionTerminales_postventa()

	{

		$model=new Postventa;

		$this->render('index',array(

			'consulta'=>'Postventa',

			'model'=>$model,

			'parcial'=>false,

			));

	}

Is there any way to trace what happens with the values i enter in the activetexfield filter of cgridview?

9

I use this method with success in other models and cgridview but i do not know why it does not go well here. I know I have to:

1.- declare de variables in the model

2.- put them in the rules ‘safe’ on search

3.- put ->with(‘relations’) in cdbcriteria

4.- put ->compare( new variables, $this->index model)

5.- put ‘sort’ staff

6.- put ‘name’=>‘variables’ in cgridview in the view

The only difference with other models is the search function is now more complex.

10

In the above, the controller will never receive the search parameters into the Postventa model for search.

It must be something like the following:




public function actionTerminales_postventa()

	{

		$model=new Postventa('search'); // must set 'search' scenario

		$model->unsetAttributes();      // clear any default values

		if (isset($_GET['Postventa']))  // receives search parameters into $model

			$model->attributes = $_GET['Postventa'];

		$this->render('index',array(

			'consulta'=>'Postventa',

			'model'=>$model,

			'parcial'=>false,

			));

	}
11

Thanks a lot. It works.

But i needed to put:




$criteria->params+=array(':alta'=>$alta, ':baja'=>$baja, ':cambio'=>$cambio, ':modificacion'=>$modificacion);

because i get an error:




SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

due to filter condition overwrites my params.

But i do not know wy i need scenarios here. I used in other models with public variables without problems in my filters view

12

About the parameters, please take a look at this thread.

http://www.yiiframework.com/forum/index.php/topic/42669-bounding-parameters-lost/

When we collect inputs for filtering, we usually use ‘search’ scenario in which every attribute is declared ‘safe’ in order to receive the input value as is.