List related records

Yii 1.1 General Discussion
1

Good morning everybody, I’m struggling to list related records, for example, registration of a design has the User who registered (user table), the listing of projects is listing the user code, would like to list the user name and column filter user, I would like a textfield where he researches the name or part of the username, the same advanced searching, I’m trying some ways, but without success.


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

	'id'=>'project-category-grid',

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

	'filter' => $model,

	'columns' => array(

		array(

			'value'=>'CHtml::checkBox("id",null,array("value"=>$data->id,"id"=>"id".$data->id))',

			'type'=>'raw',

			'htmlOptions'=>array('width'=>5),

		),

		array(

			'name'=>'id',

			'htmlOptions'=>array('width'=>20),

		),

		array(

			'name'=> 'user.username',

		    'value' => User::model()->findByPk($model->user_id)->username

		),...

So up pops the username in the listing, but the filter field vanishes.


array(

			'name'=> 'user_id',

		    'value' => User::model()->findByPk($model->user_id)->username

		),

Thus the filter appears, but the listing displays the user id’s also the filter considers the id and not user name, a different thing to filter by user_id = 2 it goes to show user names in the list.

Anyone have any ideas?

Thanks!

2

You need to modify your models search function. :)

First you need to add this to the criteria:


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

Then you need to modify the user compare to use user.username instead of user_id:


    	$criteria->compare('user.username', $this->user_id);

And then you need to create a filter, with user_id and username:


	public function getUserFilter() {

    	$Criteria = new CDbCriteria();

    	$Criteria->select = "username";

    	$results = User::model()->findAll($Criteria);

    	$user_list = array();

    	foreach ($results as $result) {

        	$user_list[$result->username] = $result->username;

    	}

    	return $user_list;

	}

And in the view:


            	array(

                	'name' => 'user_id',

                	'header' => 'User',

                	'type' => 'raw',

                	'value' => '$data->user->username',

                	'filter' => $this->getUserFilter(),

                	'htmlOptions'=>array('width'=>'10'),

            	),

Lifted out of own code without checking, but that should give you an idea of what you need to do.

3

try to add a virtual attribute ‘username’ to your model

http://www.yiiframework.com/wiki/167/understanding-virtual-attributes-and-get-set-methods

And in your search method add something like:




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

$criteria->compare...

...

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

and in your view just:




 array(

                        'name'=>'id',

                        'htmlOptions'=>array('width'=>20),

                ),

               'user.username',

                ...
4

Nope. Because he needs to have his relations set up properly, so he already has ‘user’ as a property of his model.

Take advantage of Yii’s AR if you’re using it. ;)

If there’s no such relation, then the original poster is in much bigger trouble.

5

Its not about using the relation, but rather a place to store the user submitted data. you need an attribute in the model to hold the user submittion of ‘username’ so that in his search() method he can use this value to filter.

6

Yii handles all of that.

You don’t need to create a username parameter at all.

As long as you edit your search function properly and create a filter (for the grid), then Yii handles everything.

7

Perfect!

Only adapted this part:




'filter' => $model->getUserFilter(),

Could you explain this line of code? It was the only one who did not understand:


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

Guys, thank you! this forum is much more efficient than some others out there.

Thanks!

8

It will eagerly load the User model, instead of doing a lazy on demand load.

I believe you can leave it out, but it’s supposed to be more efficient when you know you’re going to need the related model.

9

Thanks Again! Goodbye!

10

I’m having another problem, I need to put one more field that is related, I tried the code as below but without success:




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

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

		

$criteria->compare('user.username', $this->user_id);

$criteria->compare('segmentation.name',$this->segmentation_id);

The following error occurs:

CDbCommand failed to execute the SQL command: SQLSTATE [42S22]: Column not found: 1054 Unknown column ‘user.username’ in ‘where clause’

11

Try this:


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

oops, almost there!

the combos work, but if I apply a filter in another field textfield normalele generates the error:

CDbCommand failed to execute the SQL command: SQLSTATE [23000]: Integrity constraint violation: 1052 Column ‘name’ in WHERE clause is ambiguous

Thanks!

13

Use t.name for the current models name field, and relationname.name for the relations name field.

Yii needs some help in determining which name field you’re referring to. :)

14

And where do I change it?

I tried something like:




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

		

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

		$criteria->compare('user.username', $this->user_id);

		$criteria->compare('segmentation.name',$this->segmentation_id);

		$criteria->compare('project_category.name',$this->name,true);

		$criteria->compare('project_category.description',$this->description,true);

		$criteria->compare('project_category.created_at',$this->created_at,true);

		$criteria->compare('project_category.updated_at',$this->updated_at,true);

But the error persists.

15 

$criteria=new CDbCriteria;


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

		

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

		$criteria->compare('user.username', $this->user_id);

		$criteria->compare('segmentation.name',$this->segmentation_id);

		$criteria->compare('t.name',$this->name,true);

		$criteria->compare('t.description',$this->description,true);

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

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

It worked!

Thank you for your attention!