Join Problem


I have this issue where I need to join two tables that both have a dateAdded field and sort them by date. Below is my code.

$criteria = new CDbCriteria();


$criteria->join= 'left join product_subs on t.productCode = product_subs.productCode';

$criteria->order='t.dateAdded desc, product_subs.dateAdded desc';


$products = self::model()->findAll( $criteria );

Now this works fine, only problem is when accessing a productsubs relation I have in product they are obviously not sorted by date. It appears to me I cannot use models for this particular query. Can anyone point me in how I can do this with yii or if I have to do a manual query for this functionality. ( need the left join because some products do not have product subs ).

The code above won’t fetch any data from product_subs table, it just joins the tables. Items from poduct_subs are lazy-loaded instead, on the first attempt at accessing them, without any ordering instruction.

Use CDbCriteria::$with for relational queries:

$criteria = new CDbCriteria();

$ctiteria->with = array('product_subs' => array(

  'order' => 'product_subs.dateAdded desc',


$criteria->order='t.dateAdded desc';

That worked great, just to add I had my relation name as productSubs not product_subs, so I just needed to change that plus remove the join option because the with already added the left join. ::)