Many many relationship: Row selection based on foreign rows?

Hi Yii community,

Let’s say I have three tables set up like this.

video <=> video_category_assign <=> video_category

The relation array in Video model:


'videoCategories' => array(self::MANY_MANY, 'VideoCategory', 'video_category_assign(video_id, video_category_id)'),

Now as you van see, this is a many_many relationship between videos and video_category’s.

Now I have the following CActiveDataProvider set up:




$dataProvider=new CActiveDataProvider('Video',

	array

	(

		'criteria' =>

			array

			(

				'params' => array(":status" => Video::STATUS_ACTIVE),

				'condition' => 't.status=:status',

				'with' => array

				(

					'videoCategories' => 

						array

						(

							'select'=>array('name'),

							'order'=>'name ASC',

						),

					'postedByUser' =>

						array

						(

							'select'=>array('nickname'),

							'limit'=>1,

						),

				),

			),

		'pagination' => 

			array

			(

				'pageSize' => 5,

			),

		'sort' =>

			array

			(

				'defaultOrder' => array('approved_time'=>true),

				'attributes' => array('views','title','approved_time'),

			),

	)

);



This dataprovider fetches all videos (including all videoCategories for each Video). But how do I change the criteria in such a way that it only fetches Videos that are assigned to at least certain category id’s or category names? (Again, each Video should have all of its videoCategories listed etc.)

I’m fairly new to Yii and I’m doing my best to learn, but this one got me scratching my head a lot. I’ve gone through the documentation many times. Did I miss something? Maybe this has a really simple answer, I don’t know. Any help would be appreciated.

  • Tim

You can add a condition in criteria:




'condition'="id in SELECT video_id FROM video_category_assign WHERE video_category_id = '$category'"



Thanks a lot Zaccaria!