Hello guys,
I am wondering how can one filter on a field from a relation, different then the foreign key, using CGridView!?
For example, let’s say we have some Emails (Email model) and some Senders(Sender model) and when we list the emails in the CGridView we also want to be able to filter by Email->sender->country_id.
I have tried many things with no success, and found no solution on this forum.
Can anybody help ?
Check this thread, this has been discussed there - http://www.yiiframework.com/forum/index.php?/topic/9083-solved-search-filter-of-a-relations-field-through-cgridview/
Thank you for your help!
But my problem was a little bit more complicated then that. In the end i managed to solve it, and i think it can be helpful to other people as well:
If you wish to filter on a field from a relation, but that field is actually the foreign key (so you have that property in your main model) you can do it as in the thread you pointed me to.
The problem appears when you try to show and filter some other column form the relation. Let’s say we have Email and Provider as models, and emailProvider as a relation in Email (for provider_id). We can easily show and filter provider_name using provider_id as a value.
If we also want to show and filter provider_location:
We must create a property for the Email model named ‘provider_location’(of course not in the database, but rather in the model, so that the user submitted value for filtering has a place to be stored) like this:
public $provider_location;
Remember to mark is as "safe on search" in the model rules.
The search function will have to contain something like this:
$criteria->compare('emailProvider.provider_location',$this->provider_location, true);
...
...
$criteria->with = 'emailProvider';
This has the advantage of using the eager loading and is more db efficient.
Finally the column definition in the view is rather simple:
array(
'name'=>'provider_location',
'value'=>'isset($data->emailProvider)?$data->emailProvider->provider_location:""',
),
This will create a textbox filter by default. Adding a combobox filter is done like this:
'filter'=>CHtml::listData(EmailProvider::model()->findAll(array('order'=>'provider_location ASC')), 'provider_location', 'provider_location')
Enabling sorting can be done as described here.
Hope this helps somebody!