OUTER JOIN with extra condition in JOIN condition

Yii 1.1 General Discussion
1

I want to use an extra condition when using the with() method in CDbCriteria. For example, if table A is related to B with a HAS_MANY relationship, I want to fetch a record from A even when there is no record in B that is related to A. The way to do this is by using OUTER JOIN. But I also want an extra condition that applies to B.

When using the with() method, I have something like this:


$a = A::model()->with(['b' => ['key_index=:key', 'params' => [':key' => 'text_estimate']]])->findByPk($id);

I want the ‘key_index=:key’ condition to be as an extra condition in the LEFT OUTER JOIN query and not in the WHERE condition, something like this:


SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.id_foreign AND b.key_index='text_estimate' WHERE a.id=1

How can I accomplish this with CActiveRecord?

2

Hello. For using an extra condition when making a JOIN i use CDbCriteria, for example:




//Here i declare some variables...

$crossTableName = "country_to_hotel";

$fk = "country_id";

$id = $_POST['id']; //submited by a form or an AJAX call




$searchCriteria = new CDbCriteria(); //create the CDbCriteria object

$searchCriteria->select = "t.*";     //we want all fields

$searchCriteria->order = "t.title asc";   //order by...

$searchCriteria->join = "INNER JOIN ". $crossTableName ." ct ON ct.hotel_id = t.id"; //here we declare the join

$searchCriteria->addCondition("ct.".$fk." = ".$id);            //here we add an extra condition

$data = Hotel::model()->findAll($searchCriteria);

This SQL should be like:

SELECT t.* FROM hotel t INNER JOIN country_to_hotel ct ON ct.hotel_id = t.id WHERE ct.country_id = $id;

where $id is the country_id passed to the function, so the result should be all hotels from a concrete country, searched in the crosstrable "country_to_hotel".

You can addCondition() every time you need.

If it doesn’t fit your needs, you can try add the AND condition in the join declaration this way:




$searchCriteria->join = "INNER JOIN ". $crossTableName ." ct ON ct.hotel_id = t.id AND ct.created_by = 'admin'";

I hope it may help you.

3

Thanks for your reply.

This is not what I want. I want a OUTER JOIN because I need to get a specific record (A.id = xx) from table A joined with any records from B (whether they exist or not) where A.id = B.id_foreign (id_foreign links to A) plus a condition that applies to a B column (let’s call this column col_condition) which is not related to A. If there is no such column in B (col_condition), then I should get nulls in place of B’s columns joined with A, but I MUST get A’s row anyway.

The INNER JOIN fetches rows where the joined columns exist in both joined tables.

4

yes i know, it was an approximation example, just change INNER for OUTER and the fields and tables you need in the example and try it.




$searchCriteria->join = "OUTER JOIN ". $crossTableName ." ct ON ct.hotel_id = t.id AND ct.created_by = 'admin'";

I hope it helps!