Yii model relation using a field other than the primary key

Question

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 1

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"',

Answer 2

For the record, in Yii 1.1.9 this was addressed; see issue #2706. It is not terribly obvious from the documentation, but this exact thing can be accomplished by putting an array in place of where the foreign key name would ordinarily go, with local column as key and foreign column as value.

So, for instance, if you had two local columns 'fk1' and 'fk2' referencing a composite unique key with columns 'col1' and 'col2' in the table for model "Foo", your entry in the relationship array would look like this:

'foo' => array(self::BELONGS_TO, 'Foo', array('fk1'=>'col1','fk2'=>'col2'))

Quoted from the documentation on CActiveRecord.relations():

In case you need to specify custom PK->FK association you can define it as array('fk'=>'pk').

Answer 3

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