Yii Framework Forum: CDbCriteria - select from two tables - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

CDbCriteria - select from two tables Rate Topic: -----

#1 User is offline   GSTAR 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,037
  • Joined: 30-October 09
  • Location:UK

Posted 28 July 2010 - 08:20 AM

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?

$suppliers=Supplier::model()->findAll($criteria);

What is the Yii equivalent of the SQL query?
0

#2 User is offline   kokomo 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 289
  • Joined: 23-July 10

Posted 28 July 2010 - 09:09 AM

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 supplier s, supplier_vehicle sv
Maybe the condition and group work also without the table name's in front of the colums.
0

#3 User is offline   Dana 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 203
  • Joined: 18-February 10
  • Location:Florida, USA

Posted 28 July 2010 - 09:42 PM

For the condition, you should look at $criteria->addInCondition(...)
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users