Nested joins with CDBCriteria

Hello

I have a model Company, and I would like to view a list of related contacts. The relationship is as shown in the figure attached: 2497

model.png

The SQL to do this would look like this:




SELECT p.* 

FROM Person AS p

	LEFT OUTER JOIN ContactPerson AS cp

		LEFT OUTER JOIN CompanyContact AS cc

			ON cc.contactId = cp.id

		ON cp.personId = p.id

WHERE cc.companyId = 1



The question is how do I do this using CDbCriteria?

You can set the property join of CDbCriteria, just write all the piece:




$criteria->join=""LEFT OUTER JOIN ContactPerson AS cp

                LEFT OUTER JOIN CompanyContact AS cc

                        ON cc.contactId = cp.id

                ON cp.personId = p.id;




Why do you want to make a left outer join if you have a clause on a related table?

Thanks for the quick reply, got it working now.

Hi,

Could you please post your solution?

I am facing same problem now.

How do I use Yii(criteria?) to build this nested left join query:

SELECT * FROM tb1 JOIN (tb2 JOIN tb3 ON tb2.tb3id=tb3.id) ON tb1.tb2Id=tb2.id;

Thanks in advance.

never mind, solved it.

$criteria->join="JOIN (tb2 JOIN tb3 ON tb2.tb3id=tb3.id) ON t.tb2Id=tb2.id"

this will do the job.

Hey Skuje,

Can you tell what line of code you wrote to render your view? My problem is if i render a view and pass one particular model, called view doesn’t recognise fields from other models other than its own.

in your case how will you display the fields from ContactPerson and CompanyContact when rendering Person view?