Cgridview And Indirect Relation Filter
Posted 30 December 2013 - 10:11 AM
a. Source (had many ChildrenOfSource)
b. ChildrenOfSource (source_id is FK)
d. SourceTargetMap (Many to Many mapping between Source & Target)
ChildrenOfSource is having source_id, SourceTargetMap is having mapping of Target_id & Source_id .
I am on ChildrenOfSource admin view using CGridview. I would like to filter ChildrenOfSource for a given Target. Target is related to ChildrenOfSource via two hops:
Please guide me to solve this.
Posted 30 December 2013 - 10:55 PM
Do you have already established the proper relations among those models?
I guess they should be like the following:
Sourse HAS_MANY ChildrenOfSource / ChildrenOfSource BELONGS_TO Source ... 1:N Source MANY_MANY Target / Target MANY_MANY Source ... N:N
Or you may have divided the MANY_MANY relation into 2 sets of 1:N relations. This is also fine:
Source HAS_MANY SourceTargetMap / SourceTargetMap BELONGS_TO Source ... 1:N Target HAS_MANY SourceTargetMap / SourceTargetMap BELONGS_TO Target ... 1:N
Filtering by a BELONGS_TO relation is quite easy.
You can find the standard solution for it in the following wiki:
I hope you have already got it.
And you may note that the filtering by an indirectly related model is also supported.
Say, you have another model named 'Foo', and Foo and Source has a following relation:
Foo HAS_MANY Source / Source BELONGS_TO Foo ... 1:N
Then you can access an attribute of Foo by a notation of $childrenOfSource->source->foo->some_field.
And it also means that you can write like the following in the search() function of ChildrenOfSource:
$criteria = new CDbCriteria; $criteria->with = array( 'source', 'source.foo' ); ... $criteria->compare( 'foo.some_field', $this->someWord, true ); ...
Now, as you have described, your Target can be accessed from ChidrenOfSource by a notation of $chidrenOfSource->source->sourceTargetMap->target. So you can write like this:
$criteria = new CDbCriteria; $criteria->with = array( 'source', 'source.sourceTargetMaps', 'source.sourceTargetMaps.target',); $criteria->together = true; // important ... $criteria->compare( 'target.some_field', $this->someWord, true ); ...
Looks easy? Yes it is, kind of.
You will encounter a strange problem when the relations involve a HAS_MANY or MANY_MANY relation ... you will not get the proper count of records in the search result.
Please refer to the following wiki for details:
Posted 01 January 2014 - 02:35 AM
I am still learning, got stuck somewhere here:
$criteria->compare( 'target.some_field', $this->someWord, true );
I declared "someWord" member in my ChildrenofSource class. I checked the log, query is doing a join of all tables because of "with". But when I am using $this->someWord in my CGridview, it is blank. Doing my best to debug that.