The Humble Rat The Humble Rat - 1 year ago 95
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
tells us what it is for and the
is the id of the status and this is the field i need to link to. So the
in the active_service table relates to the
field of the related_fields table where the condition is met, ie the
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 Source

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"',
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download