BELONGS_TO relationship bug(join a table itself)

What steps will reproduce the problem?

1.create a table member with these columns:

mid int primary key,

name varchar(60),

sales_id int,

Here,a member could be a sales , and a member who is not a sales must belongs to a sales intead.Sample data like:






2.define relations in Member model:

return array(

   'sales' => array(self::BELONGS_TO, 'Member', sales_id','joinType'=>'LEFT JOIN'),

  ); controller or console:

$criteria=new CDbCriteria;

$criteria->with = array('sales');


foreach($models as $model){

    echo $model->name,":",$model->sales->name,"\n";


the expected sql should be :

select * from member t join member sales on t.sales_id=sales.mid;

but the sql is :

select * from member t join member sales on t.sales_id=sales.sales_id;

and if you use lazy load , which means just comment the line below:

//$criteria->with = array(‘sales’);

the sql will be right:

2010/01/12 21:44:01 [trace] [] lazy loading Member.sales

2010/01/12 21:44:01 [trace] [system.db.CDbCommand] Querying SQL: SELECT sales.mid AS t1_c0, AS t1_c1, sales.sales_id AS t1_c2 FROM member sales WHERE (sales.mid=:ypl0)

But as you can see, there will be N+1 sql , when the number of members is N.

Why eager loading is different with lazy loading?

this is my fault…I’ve modified the code of Yii as I mentioned in this topic:


I’m just wondering if anyone could help me out of this problem…

I don’t want to modify the code of the framework,but how can I define foreignKey in a relation?