many to many + one to one eager loading

So I have a submissions table that’s in a many to many relationship to a categories table, but is also in a one to one relationship with a users table. I’ve setup relations and can setup the with to pull in either the user or the categories filtered on id but am having trouble getting both at the same time. My code is as follows:


public function relations()

	{

		return array(

			'categories' => array(self::MANY_MANY, 'Category', 'submission_category(submission_id,category_id)'),

			'user' => array(self::BELONGS_TO, 'YumUser', 'user_id'),

		);

	}

and I can pull in the categories with




		$submissions = Submission::model()->with(array(

				'categories'=>array(

						'on'=>'categories.id='.$id,

						'together'=>true,

						'joinType'=>'INNER JOIN',

				)))->findAll(array(

						'limit'=>$limit

				));



and the users with




$submissions = Submission::model()->with('user')->findAll(array(

				'limit'=>$limit));



but I am having trouble with loading both the user and categories at the same time. Any help would be greatly appreciated.

Ok it seems I figured out a solution:




	public function actionListCategory($id,$limit=10) {

		

		$submissions = Submission::model()->with(array(

				'categories'=>array(

						'on'=>'categories.id='.$id,

						'together'=>true,

						'joinType'=>'RIGHT JOIN'

				),

				'user'=>array(

						'on'=>'t.user_id=user.id',

						'together'=>true,

						'joinType'=>'INNER JOIN',

				)))->findAll(array(

						'limit'=>$limit

				));

		

		$dataProvider = new CArrayDataProvider($submissions);

		

		$this->render('list',array(

			'dataProvider'=>$dataProvider,

		));

	}



However, if anyone knows of a more elegant or efficient way of doing this it would be much appreciated. I’m still trying to figure out how to get all the categories for a submission as well, but only submissions that match a given category.

I think this can be helpful also make like you need.