Yii: Getting distinct field values from a related model using scopes

Question

I'm working on the Admin view in Yii for my Project model.

One of the columns with a filter is user (owner) of the project.

I can do something like this:

'filter'=> CHtml::dropDownList('Project[user_id]', $model->user_id,
    CHtml::listData(User::model()->orderAsc()->findAll(),
    'id','username'),array('empty'=>'(select)')),

Which gives me a list of all users in the user table, but I'd like to create something that pulls the distinct users who own a project (I've got 200 users, but only a handful tend to create projects).

Project and User are related via:

'user' => array(self::BELONGS_TO, 'User', 'user_id')

I've tried a bunch of option in the findAll() method, but none worked, I'm not trying to do something with a scope.

So far I've tried this:

'filter'=> CHtml::dropDownList('Project[user_id]', $model->user_id,
    CHtml::listData(Project::model()->with('user')->ownerUsernames()->
    findAll(), 'id','username'),array('empty'=>'(select)')),

and my Scope is defined as:

'ownerUsernames' => array(
    'with' => 'user.username',
    'select' => 'user.username',
    'distinct' => true,
),

I've tried so many iterations of the above I've lost count 'with'=>'username' 'select'=>'username' etc., but just can't seem to get it to work.

I've tried replace Project::model() with $model just because I thought it might have something to do with it, but no luck.

Any ideas are appreciated. Very new to Yii, but this seems like something it can do.

score 1 · Accepted Answer · answered Mar 01 '14 at 02:32

You have everything ready. Define for the project model a getter function like

public function getUsername()
{
return $this->user->name;
}

Now you should be able to use

CHtml::dropDownList('Project[user_id]', $model->user_id,
    CHtml::listData(Project::model()->with('user')->ownerUsernames()->
    findAll(), 'id','username'),array('empty'=>'(select)'))

The logic is that CHtml::listData will get the projects as a model, it will create the keys using $project->id and it will create the values using $project->username. Because you created the getted function it will know what $project->username is. Unfortunately CHtml::listData(Project::model()->with('user')->ownerUsernames()->findAll(), 'id','user.name') will not work because it cannot execute 'user.name' or anything like that.

Rafay Zia Mir · Answer 2 · 2014-03-01T05:02:59.540

actually you can do many things to accomplish that but some methods would be not appropriate due to time consumption. I prefer to tackle this problem as

Make a table in DB named user_project with attributes
id
user_id(fk to user table)
project_id(fk to project table)
When you create a project then also populate user_project with given fields.
Make its model and you will see relations in it. Now make a function like

public function getName()
{
return user->name;//here i have assumed that the relation name is user
}

Now query this table like

$user=UserProject::mode::findAll();
$list=CHtml::listData($user,'user_id','name');

and use this list to populate the dropDownList. The benefit of this method is that you dont need to query all users of the user table.

I believe you can also do $list=CHtml::listData($user,'user_id','name'); because you defined getName and that is a getter. — Mihai P., Mar 01 '14 at 01:50

Yii: Getting distinct field values from a related model using scopes

2 Answers2