How To Join 3 Tables Using Criteria

Hello every one.

I have 3 tables to join.

properties (id, city_id, stret_name …)

cities (id, province_id, name)

provinces (id, name).

I need to get query like this by using criteria:

SELECT * FROM properties


LEFT JOIN provinces ON

I can join cities by using CDbCriteria::with but I can’t do it with provinces so I used CDbCriteria::join

$criteria->with=array('city', 'user');



$criteria->join='LEFT JOIN provinces on';

By running code above I gat this error:

Column not found: 1054 Unknown column ‘city.province_id’ in ‘on clause’. The SQL statement executed was: SELECT COUNT(DISTINCT FROM properties t LEFT JOIN provinces on LEFT OUTER JOIN cities city ON ( LEFT OUTER JOIN user user ON (

Yii generates this query

SELECT * FROM properties

LEFT JOIN provinces ON


and not this

SELECT * FROM properties


LEFT JOIN provinces ON

How can I fix it?

No one knows?

I don’t know how you made relations are in the models but something like this

'with' => array('city', 'city.province'),

may work

you can use this if relation to province declared on city

$criteria->with = array('city' => array('with' =>'province'),'user');

Thanks Reza m. It halped me.

Hello Olsam, I am a newbie… I am also trying to join 3 tables. Properties, location & location group.

properties have location as foreign key & location has group as foreign key. I want to list the properties where locations are having group_id = "array of ids". Will i please post ur query so that i can analyse it. Its a bit confusing.