how can I use BELONGS_TO when there is no foreign key?

Yii’s document is limit,I have a problem with the relations:

I have a trade table and a item table,

trade.item_id refers to item.item_id

but as our app is so complex that item_id can be null sometimes,so I can’t define it as a Primary Key,and we have an auction_id column in item too ,which make it worse for AR to regnize…

the structure is like this:

item(auction_id char(32) not null unique key,item_id bigint default null unique key)

I define a relation in trade:

return array(

  'item'  => array(self::BELONGS_TO, 'Item', 'item_id','joinType'=>'JOIN'),

);

and I want sql something like :

select * from trade join item on trade.item_id=item.item_id

but what I get is:

select * from trade join item on trade.item_id = item.auction_id

just because the auction_id is placed before item_id…

and I read the document again and again , sometimes read through the RAW code of Yii framework,but still can’t find anything to modify this behavior…

Can’t I define which column to be joined by myself??

I don’t want the too “smart” AR behavior…

Can someone help me?

anyone here???

return array(

  'item'  => array(self::BELONGS_TO, 'Item', 'item_id',

    'joinType'=>'JOIN',

    'foreignKey '=>'item.item_id'

  ),

);

see: http://www.yiiframework.com/doc/api/CBaseActiveRelation#foreignKey-detail

return array(

  'item'  => array(self::BELONGS_TO, 'Item', 'item_id',

    'joinType'=>'JOIN',

    'foreignKey '=>'item.item_id'

  ),

);

‘foreignKey’ => ‘item.item_id’ doesn’t work,got error:

The relation "mall_item" in active record class "MallTrade" is specified with an invalid foreign key "mall_item.item_id". There is no such column in the table "mall_trade".

if I set ‘foreignKey’=>‘item_id’,it doesn’t work too,totally no changes:

SELECT COUNT(DISTINCT `hs_mall_trade`.`id`) FROM `hs_mall_trade`  JOIN `shop` `shop` ON (`hs_mall_trade`.`sid`=`shop`.`sid`) JOIN `hs_mall_item` `hs_mall_item` ON (`hs_mall_trade`.`tb_item_id`=`hs_mall_item`.`auction_id`) WHERE (hs_mall_trade.sid=:sid and hs_mall_trade.trade_time>=:date_start and hs_mall_trade.trade_time<=:date_end)

still hs_mall_trade.tb_item_id=hs_mall_item.auction_id)

I’m tired to try any new methods,and I have to modify the code of Yii framework:

Yii 1.1,CActiveFinder.php , line975:

if(is_array($pke->_table->primaryKey)) // composite PK

    $pk=$pke->_table->primaryKey[$i];

#add one if before the code above:

if( isset($pke->_table->columns[$fk]) )

    $pk = $fk;

else if(is_array($pke->_table->primaryKey)) // composite PK

    $pk=$pke->_table->primaryKey[$i];

I know it’s not a good idea , but …

And another thing:

How can I eager load data with depth >=2?

like trade.item_id join item.item_id ,then item.cid join category.cid

How can I do that?

Trade::model()->with('item.category')->findAll();

If you have your AR relations set up properly

don’t you think it’s ambiguous?

how do you decide the "category" to be a column or a relationship?

if I have a item.category column in item table.

Well, the idea of using AR is to reduce schema dependency. If your schema not really normalized then don’t use AR. If you eager load and do lot of …->with(r1,r2,r3) then you put schema dependency into your code.

relations

		return array(

			    'grupo'=>array(self::BELONGS_TO,

                'grupo','grupo_id',

                'joinType'=>'JOIN',

                'foreignKey'=>'grupo.grupo_id'

                ));

under controller how to call user group_id=1 to return group name like : Admin

regards

'music_artist'=>array(self::BELONGS_TO, 'MusicArtist', 'mu_id'),

     $model = Music::model()->findByPk(125);

     $artists = $model->music_artist;

SQL: SQLSTATE[HY000]: General error: 10007 An object or column name is missing or empty. 

For SELECT INTO statements, verify each column has a name. 

For other statements, look for empty alias names. 

Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. [10007] (severity 5) [(null)]. 

The SQL statement executed was: 

SELECT [music_artist].[mu_id] AS [t1_c0], [music_artist].[art_ids] AS [t1_c1] 

FROM [dbo].[music_artist] [music_artist] 

WHERE ([music_artist].[]=:ypl0)