[SOLVED] Relations with master-detail and wrong join.

Yii 1.1 General Discussion
1

Hi, I have this two MySQL 5.5 MyISAM tables:


CREATE TABLE IF NOT EXISTS `locks` (   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   `id_user` bigint(20) unsigned NOT NULL,   `id_reason` bigint(20) unsigned NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=MyISAM;  CREATE TABLE IF NOT EXISTS `reasons` (   `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,   `message` text COLLATE utf8_unicode_ci NOT NULL,   PRIMARY KEY (`id`) ) ENGINE=MyISAM;

Since they are MyISAM, there are not foreign keys. But the idea is: locks.id_reason = reasons.id. "reasons" is the catalog of reasons for which an account can be locked, and locks are all the possible locks for an user, so, an user could have many locks and each lock must have one reason. Then I defined in the models this relations: Locks model:


 public function relations() { 	return array(   		'reason' => array(self::HAS_ONE, 'Reasons', 'id'),   	); }

Reasons model:


 public function relations() { 	return array(    	'lock' => array(self::BELONGS_TO, 'Locks', 'id_reason'), 	); }

and then I want to get, for a given user id, the lock reasons:


$criteria = new CDbCriteria(); $criteria->with = array('reason'); $criteria->condition = 'id_user=' . Yii::app()->user->id;       $dataProvider = new CActiveDataProvider(    'Locks',    array(   	'criteria' => $criteria,    )  $this->controller->render('locked', 		array(     		'dataProvider'=>$dataProvider, 		) ); );

The final result should be rendered in a CListView just printing $data->reason->message (that’s a list of human readable reasons for which the account is locked), but I just get nulls. Checking the MySQL logs:


SELECT `t`.`id` AS `t0_c0`, `t`.`id_user` AS `t0_c1`, `t`.`id_reason` AS `t0_c2`, `reason`.`id` AS `t1_c0`, `reason`.`mensaje` AS `t1_c3` FROM `locks` `t`  LEFT OUTER JOIN `reasons` `reason` ON [b](`reason`.`id`=`t`.`id`)[/b]  WHERE (id_user=20) LIMIT 10

Notice that reason.id is being compared to locks.id, when this should be (reason.id=t.id_reason) because id_reason is he field linking locks to reasons. How can I fix my relations and/or criteria to reflect this relationship? Thank you in advanced.

2

I think the relationships should be:

Locks BELONGS_TO Reasons

Reasons HAS_MANY Locks.

3

Always use the FK in the relationship definition.

Locks.php




 public function relations()

{

  return array(

    'reason' => array(self::HAS_ONE, 'Reasons', 'id_reason'),

  );

}

/Tommy

4

Thank you both :)

It works great if set this way:

In [font="Courier New"]Locks[/font] model:


'reason' => array(self::BELONGS_TO, 'Reasons', 'id_reason'),

In [font="Courier New"]Reasons[/font] model:


'locks' => array(self::HAS_MANY, 'Locks', 'id'),

Thanks again!

5

With the query example in your post you don’t need the second relationship but that one should also use the FK (id_reason).

/Tommy