Yii Framework Forum: [solved] cgridview search relations - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

[solved] cgridview search relations Rate Topic: -----

#1 User is offline   Marco W. 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 46
  • Joined: 29-October 10
  • Location:Hamburg, Germany

Posted 09 November 2010 - 04:46 AM

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
0

#2 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 09 November 2010 - 05:22 AM

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)
0

#3 User is offline   Marco W. 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 46
  • Joined: 29-October 10
  • Location:Hamburg, Germany

Posted 09 November 2010 - 05:47 AM

View Postzaccaria, on 09 November 2010 - 05:22 AM, said:

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 :(
0

#4 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 09 November 2010 - 06:16 AM

Strange.

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

#5 User is offline   Marco W. 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 46
  • Joined: 29-October 10
  • Location:Hamburg, Germany

Posted 09 November 2010 - 06:54 AM

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 '%%'.
0

#6 User is offline   zaccaria 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 2,232
  • Joined: 04-October 09
  • Location:Moscow

Posted 09 November 2010 - 07:00 AM

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')
);
}

2

#7 User is offline   Marco W. 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 46
  • Joined: 29-October 10
  • Location:Hamburg, Germany

Posted 09 November 2010 - 07:03 AM

View Postzaccaria, on 09 November 2010 - 07:00 AM, said:

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
0

#8 User is offline   secno 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 4
  • Joined: 11-December 10

Posted 20 January 2011 - 06:05 AM

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

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users