Sometimes while getting value from foreign key relation, dependency is more than one level. This post covers how to get value from base table and enable search in gridview.
Consider following scenario:
- User table: Userid, transaction_id,user_type
- Transaction : Userid, info_id
- Info : info_id, name
Suppose requirement is to show name of user in user grid. Here , to relate name and userid , there is a 2 level dependency through transaction table.
Here we are not taking name field directly in user table, because, there are different type of user and each type has different info table which contains name.Transaction table is a master table for each type which has ids like user_id,info_id, address_id etc.
To get name value in user table with search
1)Make a relation with transaction table in User model.
‘rel_tran’ => array(self::BELONGS_TO, ‘Transaction’,’user_id’),
2) in Transaction model, make a relation with info table.
‘rel_info’ => array(self::BELONGS_TO, ‘Transaction’,’info_id’),
3)Now in search method,
For get Info field’s name in user
$criteria->with = array(‘rel_tran’,’rel_tran.rel_info’);
here,name is a field of info which,that needed to show with relation.
To use other model’s relation we use ‘.’ with relation to that table.
For example,To access name, rel_transaction.rel_info and in compare directly write rel_info.
Declare first_name with public in user model & add in search in rules.
In admin ,gridview,
‘value’ => ‘$data->rel_tran->rel_info->first_name’,
While showing value we use ‘->’ and write both model’s relation name to retrieve the value.For example, here $data->rel_tran->rel_info->first_name.
This way,you are able to work with 2 level relations with searching.
It is alternate work around to achieve same functionality:
1) Add a new field of transaction in info.(info_transaction_id)
2) Make relation with transaction
'rel_tran' => array(self::BELONGS_TO, 'Transaction','', 'on' => 't.created_by=user_id'),
here in created by,I have user table id.
user_id is a field in transaction table which has value of user id.
3)Make relation with type1_info
here transaction_id is auto-increment id of transaction table.
4)Declare name as public variable & in search,
In search method
$criteria->with = array('rel_tran','rel_info'); $criteria->compare('rel_info.name',$this->name,true);
5)In gridview get value with $data->rel_info->name.
This way,I got name with searching in gridview with 2 level dependency.