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.

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

Solution 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').

Solution 3

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

Comments

[Pitchinnate]

Why would you use something other than the primary key to setup a relationship? What happens if there are two rows with status_id = 1?

[Pitchinnate]

Do you have a status table?

[The Humble Rat]

@Pitchinnate the statuses are held within the related fields table. If there are two active services with the status of 1 then the relationship will display both of them as Open. There would be no conflict here. There are a few other fields in the table I have not included as they were not important to this particular question. For example in the active services there is an account_id field which relates to an Accounts table. This way I can find all services and display them on the account, using echo rl_status when referencing its status. I have posted the solution I found below