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:
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'";
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.