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?
Could not test it right now but I think it should work this way (or similar):
$criteria=new CDbCriteria;
//$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)';
$criteria->group='supplier.id';
$supplier=Supplier::model()->find($criteria);
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.