Let’s take an example :
I have four tables : author(id,name,nationality_id) / author_genre(id,author_id,genre_id) / genre(id,genre_name) / nationality(id,name);
relations :
in author model : "genres" => array(self::MANY_MANY, "genre", "author_genre(author_id,genre_id)")
"from" => array(self::BELONGS_TO, "nationality", "nationality_id")
in genre : "authors" => array(self::MANY_MANY, "author", "author_genre(genre_id,author_id)")
in author_genre : "genre" => array(self::BELONGS_TO, "genre", "genre_id")
"author" => array(self::BELONGS_TO, "author", "author_id")
Here is my method search, the best way I found to get my many_many relationship working with filter and pager. Unfortunately I never succeed to get sorting working with my hisGenre column because I have an error as it misses "genres.id" in the group by.
$criteria=new CDbCriteria;
$criteria->with=array('genres'=>array('select'=>false));
//$criteria->limit=-1;
$criteria->together=true;
$criteria->compare('t.id',$this->id);
$criteria->compare('t.name',$this->enabled);
$criteria->compare('genres.id',$this->hisGenre);
return new KeenActiveDataProvider(get_class($this), array(
'pagination'=>array(
'pageSize'=> Yii::app()->user->getState('pageSize',Yii::app()->params['defaultPageSize']),
),
'withKeenLoading' => array('genres'),
'criteria'=>$criteria,
'sort'=>array('defaultOrder'=>'t.id ASC','attributes'=>array('assignedRole'=>array('asc'=>'genres.id','desc'=>'genres.id DESC'),'*')),
));
}
Then I had the necessary to display the nationality as :
$criteria=new CDbCriteria;
$criteria->with=array('genres'=>array('select'=>false),'from');
//$criteria->limit=-1;
$criteria->together=true;
$criteria->compare('t.id',$this->id);
$criteria->compare('t.name',$this->enabled);
$criteria->compare('genres.id',$this->hisGenre);
$criteria->compare('from.name',$this->isFrom);
return new KeenActiveDataProvider(get_class($this), array(
'pagination'=>array(
'pageSize'=> Yii::app()->user->getState('pageSize',Yii::app()->params['defaultPageSize']),
),
'withKeenLoading' => array('genres'),
'criteria'=>$criteria,
'sort'=>array('defaultOrder'=>'t.id ASC','attributes'=>array('assignedRole'=>array('asc'=>'genres.id','desc'=>'genres.id DESC'),'isFrom'=>array('asc'=>'from.name', 'desc'=>'from.name desc'),'*')),
));
}
And with that I always have on this page an error as it misses from.id in group by… but If I add in KeenLoading extension by hand line 200 : $pkNames[] .="from.id";
Then I get something working for displaying, sorting and filtering for the column “isFrom” but I didn’t succeed to get sorting working for hisGenre column. Because if I add “genres.id” in group by then pager doesn’t work anymore… for Gridview displays not all objects…
I created a topic here if you wand to read more about my problem and try to help me. I tried a lot of thing but never get all working correctly =) thanks
http://www.yiiframework.com/forum/index.php/topic/44838-filter-and-sort-a-column-fed-by-a-many-many-relation/page__gopid__213109#entry213109
