How do you a select from two tables using CDbCriteria? For example for the following SQL query:
SELECT * FROM supplier s, supplier_vehicle sv
WHERE s.id = sv.supplier_id AND sv.vehicle_id IN (1, 4, 6) AND s.town IN (290, 394)
GROUP BY s.id
There is no ‘from’ property in CDbCriteria. I plan to pass the criteria in to a findAll method - obviously this will run the criteria on the model that that is used by the findAll method, but what about the second table?
What is the Yii equivalent of the SQL query?
Could not test it right now but I think it should work this way (or similar):
//$criteria->alias = 'supplier_vehicle'; // maybe this is needed, don't know
$criteria->join='LEFT JOIN supplier on supplier.id=supplier_vehicle.supplier_id';
$criteria->condition='supplier_vehicle.vehicle_id IN (1, 4, 6) AND supplier.town IN (290, 394)';
Don’t know how to use the alias like [b]supplier s, supplier_vehicle sv
[/b]Maybe the condition and group work also without the table name’s in front of the colums.
For the condition, you should look at $criteria->addInCondition(…)