The Humble Rat The Humble Rat - 2 months ago 20
MySQL Question

Yii model relation using a field other than the primary key

I need to create a relation based on a field that is not the primary key. Many of the examples of how to do this are based on One to many and many to many relationships. I have tried the suggestions from the following without success

Relation in YII with not "ID" as primary key

Yii CActiveRecord: find related data, but not using the primary key

Yii Relations with non-Primary keys

Yii Model Relation Join (HAS_ONE)

I have the following table structure:

+------+---------+-----------+
| id | name | status_id |
+------+---------+-----------+
| 1 | service1| 1 |
+------+---------+-----------+
| 2 | service2| 2 |
+------+---------+-----------+


This is my table active_service. I also have the following table

+----------+----------+---------------------+-----------+
|id |related_id|related_text | text |
+----------+----------+---------------------+-----------+
|65 |1 |ActiveServices_status| Open |
+----------+----------+---------------------+-----------+
|72 |2 |ActiveServices_status| Active |
+----------+----------+---------------------+-----------+
|102 |3 |ActiveServices_status| Closed |
+----------+----------+---------------------+-----------+


This is my related_fields table
This table holds all the fields used for dropdown etc. The
related_text
tells us what it is for and the
related_id
is the id of the status and this is the field i need to link to. So the
status_id
in the active_service table relates to the
related_id
field of the related_fields table where the condition is met, ie the
related_text
is set to ActiveServices_status. How would I go about creating this relation. This is the best example of what I have done so far (in the ActiveServices model).

public function relations()
{
// NOTE: you may need to adjust the relation name and the related
// class name for the relations automatically generated below.
return array(

'rl_status'=>array(self::BELONGS_TO,'RelatedFields','status_id','condition'=>'related_text = "ActiveServices_status"','on'=>'status_id = related_id'),
);
}


Any help would be appreciated.

Answer

So finally figured this thing out after trying about 100 different lines of code. So heres the solution that worked for me.

'rl_status' => array(self::BELONGS_TO, 'RelatedFields', '', 'foreignKey' => array('status_id'=>'related_id'),'condition'=>'related_text = "ActiveServices_status"',
Comments