need help to do self join with CActiveRecord

Yii 1.1 General Discussion
1

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 = table2.id) 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.

2

hi Daikon,

Maybe you can refer to http://www.yiiframework.com/doc/guide/1.1/en/database.ar, use $criteria->with for you AR. Something like below:

$criteria=new CDbCriteria;

$criteria->select=‘categories_id’;

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

$criteria->params=array(’:parent_id’=>$game_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.

3

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?

Daikon