Get object having multiple related objects

Yii 1.1 General Discussion
1

Hey there,

I have a models Projects and ProjectTags that have a MANY_MANY relationship. As an example, I have an array of ProjectTags IDs and I’m trying to find a list of all Projects that have ALL of those ProjectTags. How can I build this query?

I have this relationship in the Projects model:




	public function relations() {

		return array(

			'projectTags' => array(self::MANY_MANY, 'ProjectTags', 'p_pt(project_id, project_tag_id)'),

		);

	}

I’ve tried using the compare method:




		$tags = array(1, 4, <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />;

		$criteria=new CDbCriteria;

		$criteria->compare("projectTags.id", $tags, true, "AND");

		$projects = Projects::model()->with("projectTags")->findAll($criteria);

Thanks for any insights.

2

something like:




$criteria->addCondition("t.id IN (SELECT project_tag.project_id FROM project_tag WHERE project_tag.tag_id IN (" . implode( ',', $tags) . "))");
3

Thanks Maciej for your reply. I haven’t been able to get it work with the query builder. But I did get something to work.




		$sql = "SELECT tbl.* From ( 

							(SELECT p.* FROM projects p INNER JOIN assoc a ON p.id=a.project_id WHERE a.project_tag_id=1)

		Union All

							(SELECT p.* FROM projects p INNER JOIN assoc a ON p.id=a.project_id WHERE a.project_tag_id=2)

		Union All

							(SELECT p.* FROM projects p INNER JOIN assoc a ON p.id=a.project_id WHERE a.project_tag_id=9)

		) AS tbl GROUP BY tbl.id HAVING COUNT(*)=3";

		$projects = Yii::app()->db->createCommand($sql)->queryAll();

So basically I would loop through my array of tags and add a subquery for each one. So this gives me the correct data results, however it returns the results as arrays, not as Active Record objects. Is there any way to get these to be AR objects?

4

Also, I’m not sure if I described by tables very well. I have three tables that I’m dealing with here that look something like this:




project

-------

| id | name   | ... |

| 1  | proj 1  | ... |

| 2  | proj 2  | ... |


assoc

-----

| project_id | project_tag_id |

| 1              | 1              |

| 1              | 2              |

| 2              | 1              |


project_tag

-----------

| id | name  | ... |

| 1  | tag 1  | ... |

| 2  | tag 2  | … |
5

try code bellow:




$tags = array(1, 4, <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='8)' />;

$criteria = new CDbCriteria();

$criteriaProjectTags = new CDbCriteria();

$criteriaProjectTags->compare('projectTags.id', $tags);

$criteria->with['projectTags'] = array(

    'condition' => $criteriaProjectTags->condition,

    'params' => $criteriaProjectTags->params

);

unset($criteriaProjectTags);

$criteria->together = true;

$projects = Projects::model()->findAll($criteria);
6

aaa, sorry, I misunderstood you. you can try sth like this:




$criteria->join .= "INNER JOIN (SELECT a.project_id, count(*) cnt FROM assoc a WHERE a.tag_id IN (" . implode( ',', $tags) . ") GROUP BY a.project_id HAVING cnt >= 3) subquery ON (subquery.project_id = t.id)");