[solved] cgridview search relations

Hi guys,

i searched the forum and found a myriad of topics regarding my problem, but i simply cannot work it out.

I want to search for users in a City.

My relations: User HAS_ONE Profile, Profile HAS_ONE City




class User extends CActiveRecord {

...

	public $search_location;

	public $search_gang;

...

		$criteria->with=array('profile.city');

		if(strlen($this->search_location))

		$criteria->addSearchCondition('profile.city.name',$this->search_location,true);


// in my view

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

	'id'=>'user-grid',

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

	'filter'=>$model,

	'columns'=>array(

		'id',

		'name',

		//'search_location',

                array(            

                  'name'=>'search_location',

	  	  'value'=>'$data->profile->city->name',

		  'header'=>'Location',

                ),

	),

)); ?>



The city name is displayed in the grid and the search box is available, but when i enter any city name in the search box, no search is performed. The list shows still all users.

This is my first yii experience, so any suggestion is appreciated.

TIA

Marco

I am not sure that this dot notation is supported.

I suggest you to do something like:




$criteria->with=array('profile'=>array('with'=>array('city')));

if(strlen($this->search_location))

      $criteria->addSearchCondition('t3.name',$this->search_location,true);




// not tested

In this way the with should work correctly. You can check in the log if the query is correctly generated (I mean, with 2 join on the right fields).

If the join is correctly, you can check that the alias t3 for the table is correct (or you can set your own alias with CDbCriteria::alias)

I tested your suggestion. In fact the two ways to define ‘with’ are generating an identical SQL query:




$criteria->with=array('profile'=>array('with'=>array('city')));

$criteria->with=array('profile.city');



City.name is aliased as: city.name AS t2_c1

I tried both:




$criteria->addSearchCondition('t2.name',$this->search_location,true);

$criteria->addSearchCondition('t2_c1',$this->search_location,true);



with no success :(

Strange.

In the sql generated there is the condition? Can you post it?

While testing this i commented the line


//if(strlen($this->search_location))



Then i got an error: Unknown column ‘t2_c1’ in ‘where clause’, same with ‘t2.name’

To make it work I had to change to:




$criteria->addSearchCondition('city.name',$this->search_location,true);



Now the generated SQL:


Querying SQL: 

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

`t`.`email` AS `t0_c2`, `t`.`password` AS `t0_c3`, `profile`.`user_id` AS `t1_c0`,

`profile`.`city_id` AS `t1_c1`, `city`.`id` AS `t2_c0`, `city`.`name` AS `t2_c1`,

`city`.`latitude` AS `t2_c7`, `city`.`longitude` AS `t2_c8` 

FROM `user` `t`

 LEFT OUTER JOIN `profile` `profile` ON (`profile`.`user_id`=`t`.`id`) 

LEFT OUTER JOIN `city` `city` ON (`profile`.`city_id`=`city`.`id`)  WHERE

(city.name LIKE :ycp0) LIMIT 10. Bind with parameter :ycp0='%%'



Now the condition is attached, but when i enter a value in the search box i have still no success.

Parameter :ycp0 will not get the entered value but stays ‘%%’.

You should add the parameter.

I guess that you added the parameter searchLocation parameter like:




public $search_location



Now you should add to the list of safe attributes:




public function rules()

{

    return array(

      [...]

       array('[..], search_location', 'safe', 'on'=>'search')

);

}



Ahh ok, now it works just fine.

Thank you so much for your kind help, zaccaria!

Marco

Excellent hint there with the need to include the attribute in the safe on search rule zac, I finally got filtering by relations working :)