How to select data in many to many relation

Yii 1.1 General Discussion
1

I have a question about select data in many to many relation.

Database table like same below

1865

untitled.GIF

Relation in Photo model


'category_r' => array(self::MANY_MANY, 'Category', 'photo_category(photo_id,category_id)'),

I want to select photo have category id = 1 and 2


$criteria = new CDbCriteria;

$criteria->with->category_r= array(

            'alias' => 'cat',

            'condition' => 'cat.id=1 and cat.id=2',

);

return new CActiveDataProvider(get_class($this), array('criteria' => $criteria));

But it doesn’t work. Do I need to select the photo some other way ?

2

Try something like this:


$photos=Photo::model()->with('category_r')->findAll(array(

    'condition'=>'category_r.id=1 OR category.id=2' 

));
3

Thank you for your advice

This case I would like both category in one photo may be use “and” but it still doesn’t work

4

Do you get an error, or what?




$photos=Photo::model()->with('category_r')->findAll(array(

    'condition'=>'category_r.id=1 OR category_r.id=2' 

));

You should be able to do something like this:




foreach($photos as $photo){

    foreach($photo->category_r as $category){

        echo $category->name;

    }

}

I am much confident that code should works, maybe with slight change according to your table structures/relation definitions.

5

I try


$photos = Photo::model()->with('category_r')->findAll(array(

                    'condition' => 'category_r.id=1 OR category_r.id=2'

                ));


        foreach($photos as $photo){

            echo $photo['id'].' '.$photo['name'].'<br>';

        }

Don’t have error and that result show a few id and name.

Next How can i do?

6

And i try


$photos = Photo::model()->with('category_r')->findAll(array(

                    'condition' => 'category_r.id=1 OR category_r.id=2'

                ));


        foreach($photos as $photo){

           foreach($photo as $cat)

            echo $cat->name.'<br>';

           }

        }

It have not any result. (No Error)

7 



'photocategory' => array(self::MANY_MANY, 'Category', '','on'=>'(photocategory.photo_id = photo.id  AND  photocategory.category_id = category.id )'),
8

Hi, I stumbled over this topic by chance (I was actually just trying to output the results from many to many joins … I thought List View should do this automagically)

Then however I was interested in the solution of this problem. Here it goes:

(I used my tables: Task, task_questions, Question)

Model Relations:


public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

			'questions'=>array(self::MANY_MANY, 'Question', 'task_questions(taskId, questionId)'),

		);

	}

Controller:


$criteria=new CDbCriteria;

		$criteria->with=array(

				

			'questions' => array(

	//Select=false gives me all questions. It can happen that you don't get all questions when you have conditions. Fun fact: Select=false actually adds a distinct Select.

			'select' => false,

		       ),

		);

		//All questions that have 2,8 or both

		$criteria->condition = 'questions.Id in (2,<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />';

		$criteria->group = 't.id';

                //Only tasks that have both of them

		$criteria->having = 'COUNT(DISTINCT questions.id) = 2';

		

		//Together is important for where statements / conditions

		$criteria->together = true;

		

		$dataProvider=new CActiveDataProvider('Task', array('criteria' => $criteria));

And in the view, you can loop over the results (depends on how you access your dataProvider).

And this is how you get all tasks that have questions with the ids 2 and 8. You don’t get questions that have either 2 or 8. You get however, tasks that have 2, 8 and x other questions. And since I don’t want to bother the guys at StackOverflow again, I have to leave it at that. (This is actually just a MySQL problem).

You can do this with HAS::MANY THROUGH relations, too.