need help to do self join with CActiveRecord

I need to construct the following query. notice table1 does a self join based on 2 columns. is there a way to do it in CActiveRecord?

select * from table1 s1 left join table1 s2 on (s1.prodid=s2.prodid and s1.creationdate < s2.creationdate) join table2 on (s1.prodid = where s2.prodid is null;

table1 and table2 has a relationship where table1 belongs to table2, and table2 has many table1.

I tried using findAllBySql route, even though this is “eager loading” when using raw sql, when I do table1->table2, it will still do an extra select. so the relationship doesn’t really exists in a raw sql query.

It will be great if I can use CActiveRecord to do either sub-query or self join, but looks like Yii doesn’t support any of those which is very limiting in many situation.

hi Daikon,

Maybe you can refer to, use $criteria->with for you AR. Something like below:

$criteria=new CDbCriteria;


$criteria->condition=‘parent_id = :parent_id’;


$criteria->with = array(‘custom_products_type_child’=>array(‘select’=>‘custom_products_type_id’);

$criteria->together = true;

$result = $this->model()->find($criteria);

custom_products_type_child is the LEFT JOIN table and the custom_products_type_id is the field in this table that i want to retrieve, if you already set you model relation, i think it will be work. Hope it can help you.

but can I do a self join on (s1.prodid=s2.prodid and s1.creationdate < s2.creationdate) though?

and if I do self join, how should I set up the relationship in the model? should I set both BELONG_TO and HAS_ONE?