Cgridview Filtering With Relations Using Joining Table

Yii 1.1 General Discussion
1

Hello,

I know there are a million and one thread on this subject, going through them I find differing information and can’t find one specific to my problem, the over abundance of threads on this subject is actually less helpful than only having a handful. Please could someone take a few minutes to help.

I have 3 tables, here they are with relevant fields


Question (primary key = id)

Tag (primary key = id, text)

Question_Tag (question_id, tag_id)

[size="5"]Question Model Relations[/size]




		 return array(

			'tags'=>array(

				self::MANY_MANY,

				'Tags',

				'question_tag(tag_id, question_id)'

			),

			'question_tag'=>array(

				self::HAS_MANY,

				'QuestionTag',

				'question_id',

			),

		);

	}

[size="5"]Tag Model relations[/size]




		 return array(

			'questions'=>array(

				self::MANY_MANY,

				'Questions',

				'question_tag(question_id, tag_id)'

			),

			'question_tag'=>array(

				self::HAS_MANY,

				'QuestionTag',

				'tag_id',

			),

		);

	}

I have a CGridView, in this grid view I would like to display Question records (which I can do), I would also like to display, all tag records associated with each Question record’s, ‘text’ field, so “tag.text”, in the same column. I also need to be able to filter these tags, it doesn’t matter if the actual searching/filtering of tags is done individually for each Question_Tag record, but displayed in the Grid they should be in the same column.

I can’t for the life of me, after reading every wiki page, tutorial and forum thread, figure out how to do it. I can find fragments of the solution but I can’t put them together. I’ve only been using Yii for a few days so I guess I don’t understand it all properly.

Can anyone point me in the right direction on how to handle this situation?

Thank you very much!

2

I still cant figure this out :confused:

3

Here’s how I did it for a list of music tracks with one or more genres per track.

Model

You don’t need a relation for the junction (Question_Tag) table, but I think it would still work the way you have it.




class MusicTrack extends CActiveRecord

{

    public $genres_name;




    public function rules()

    {

        return array(

            array('id, genres_name', 'safe', 'on'=>'search'),

        );

    }


    public function relations()

    {

        return array(

            'genres' => array(self::MANY_MANY, 'MusicGenre', 'music_tracks_genres(track_id, genre_id)'),

        );

    }


    public function attributeLabels()

    {

        return array(

            'id' => 'ID',

            'genres_name' => 'Genres',

        );

    }


    public function search()

    {

        $criteria=new CDbCriteria;

        $criteria->together=true;

        $criteria->with='genres';


        $criteria->compare('t.id',$this->id);

        $criteria->compare('genres.name',$this->genres_name,true);


        return new CActiveDataProvider($this, array(

            'criteria'=>$criteria,

            'sort' => array(

                'attributes' => array(

                    'genres_name' => array(

                        'asc' => 'genres.name',

                        'desc' => 'genres.name DESC',

                    ),

                    '*',

                ),

            ),

        ));

    }

}

View

Shows a comma-separated list of the genres.




<?php $this->widget('zii.widgets.grid.CGridView', array(

    'id' => 'music-tracks-grid',

    'dataProvider' => $model->search(),

    'filter' => $model,

    'columns' => array(

        array(

            'filter' => CHtml::activeTextField($model,'genres_name'),

            'name' => 'genres_name',

            'value' => 'implode(", ", CHtml::listData($data->genres, "id", "name"))',

        ),

    ),

)); ?>