How To Join 3 Tables Using Criteria

Yii 1.1 General Discussion
1

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 cities ON properties.city_id=cities.id

LEFT JOIN provinces ON cities.province.id=provinces.id

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->together=true;

		

$criteria->join='LEFT JOIN provinces on provinces.id=city.province_id';

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 t.id) FROM properties t LEFT JOIN provinces on provinces.id=city.province_id LEFT OUTER JOIN cities city ON (t.city_id=city.id) LEFT OUTER JOIN user user ON (t.user_id=user.id)

Yii generates this query

SELECT * FROM properties

LEFT JOIN provinces ON cities.province.id=provinces.id

LEFT JOIN cities ON properties.city_id=cities.id

and not this

SELECT * FROM properties

LEFT JOIN cities ON properties.city_id=cities.id

LEFT JOIN provinces ON cities.province.id=provinces.id

How can I fix it?

2

No one knows?

3

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




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

may work

4

you can use this if relation to province declared on city




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

Thanks Reza m. It halped me.

6

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.