Query based on MANY_MANY relationship

Yii 1.1 Tips, Snippets and Tutorials
1

To dumb this down I'm working with 3 tables.

Issue

User

IssueAssignment

My User table has id, name

Issue has id, title

IssueAssignment has id, userId, issueId.

I have defined my relationships in my models.

My problem is that I want to get all of my issues that have been assigned to a certain user so I'm trying…

$Criteria = new CDbCriteria();

if(count($this->IssueSearchForm->assignees) >= 1) {

$assignees_list = implode(",", $this->IssueSearchForm->assignees);


$Criteria->condition = "IssueAssignment.userId IN (:assignees)";


$Criteria->params = array(


	':assignees' => $assignees_list


);

}

$Issue = new Issue();

$issues = $Issue->with(array('assignees'))->together()->findAll($Criteria);

And my error basically says this.

CDbCommand failed to prepare the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'IssueAssignment.userId' in 'where clause'SELECT Issue.id AS t0_c0, Issue.clientId AS t0_c1, Issue.projectId AS t0_c2, Issue.name AS t0_c3, Issue.description AS t0_c4, Issue.createdDate AS t0_c5, Issue.statusId AS t0_c6, Issue.priorityId AS t0_c7, Issue.issueTypeId AS t0_c8, Issue.commentCount AS t0_c9, Issue.deleted AS t0_c10, Issue.submittedById AS t0_c11, t1.id AS t1_c0, t1.email AS t1_c1, t1.pass AS t1_c2, t1.name AS t1_c3, t1.admin AS t1_c4 FROM Issue LEFT OUTER JOIN IssueAssignment assignees_t1 ON Issue.id=assignees_t1.issueId LEFT OUTER JOIN User t1 ON t1.id=assignees_t1.userId WHERE IssueAssignment.userId IN (:assignees)

Now I know how to write the query manually, however I'm trying to use the Active Record/ORM with my relationships etc.  I'm new to this kind of DB access so any help would be much appreciated.

Thanks!

2

I figured out how to do this with the CDbCritera, I just didn't know if there was a simpler way.

So nevermind on this one.

3

If you don’t mind could you just show me how did you use CDbCriteria in your situation, as I have similar problem and I want to get data as in your case

Thank you