Search by Foreign key Name

maggi · April 12, 2012, 2:25pm

There are 2 tables with primary-foreign key relationship

User (id,Name) and Manager(id,user_id)

When I create a model on Manager I was able to search(yii built in) on user_id.

This is the view file

$this->widget(‘zii.widgets.grid.CGridView’, array(

'id'=&gt;'manager-grid',


'dataProvider'=&gt;&#036;model-&gt;search(),


'filter'=&gt;&#036;model,


    'columns'=&gt;array(


	'id',


	'user_id',...............

I was able to get the name of the User using

          array('header'=&gt;'User','value'=&gt;'&#036;data-&gt;[b]user[/b]-&gt;Name',),

where user is defined ‘user’ => array(self::BELONGS_TO, ‘User’, ‘user_id’), in the Manager model relations.

everything works fine. but is there a way I can search for User Name in this model but not by user_id.

Any help is appreciated. Thanks.

ragua · April 12, 2012, 2:32pm

Hi,

you just have to declare he relation in the model (done by gii normally)

and in the serach function add code like this:




		$criteria->with = array( 'user');

		$criteria->compare( 'user.user_name', $this->user_search, true );

where user is the name of your relation, and user_search the field used by the search.

maggi · April 12, 2012, 2:58pm

ragua:

Hi,

you just have to declare he relation in the model (done by gii normally)

and in the serach function add code like this:
		$criteria->with = array( 'user');

		$criteria->compare( 'user.user_name', $this->user_search, true );
where user is the name of your relation, and user_search the field used by the search.

How should I define the user_search in relations method?

logos010 · April 13, 2012, 3:42am

Hi Maggi,

In Particular, you just use the search function in model which you generated by yii tool.

As you can see the function search() in Manager models, it contain 2 rows:




$criteria->compare('id', ......);

$criteria->comapre('user_id', ......);

So, if you want to search the user name in user model, you can and a join relation in criteria and replace the second creiteria by follow this:




$criteria = new CDBCriteria();

$criteria->select = "t.*, user.name";

$criteria->join = "JOIN user ON user.id = t.user_id";

$criteria->compare('id', ....);

$criteria->compare('user.name', ....);

With the above code, you’ll get a select query like this:




SELECT t.*, user.name

FROM manager t JOIN user ON user.id = t.user_id 

WHERE .........

You also can use the with() like Ragua shown you before

Good luck!

jekeyeke · August 18, 2017, 11:34am

ragua:

Hi,

you just have to declare he relation in the model (done by gii normally)

and in the serach function add code like this:
		$criteria->with = array( 'user');

		$criteria->compare( 'user.user_name', $this->user_search, true );
where user is the name of your relation, and user_search the field used by the search.

Thank you, it’s perfect!