Question about AR relations

Yii 1.1 General Discussion
1

I have two tables: User and User_Address; User has a field ‘physical_address_id’ that references the field ‘user_address_id’ in the User_Address table. How do I set up a HAS_ONE relation between these two tables? Specifically, how would I do this in the relations method array?


public function relations()

{

   return array('physicalAddress' => array(SELF::HAS_ONE, 'SUserAddress', 'what do I put here?');

}
2

I guess you should use ‘on’ option which specifies join on.

how about




'physicalAddress' => array(self::HAS_ONE, 'SUserAddress', 'on' => '`User`.physical_address_id = `User_Address`.user_address_id')
3

That gives me this sql:

SELECT physicalAddress.user_address_id AS t1_c0, physicalAddress.address1 AS t1_c1, physicalAddress.address2 AS t1_c2, physicalAddress.city AS t1_c3, physicalAddress.county_id AS t1_c4, physicalAddress.county AS t1_c5, physicalAddress.state_province_id AS t1_c6, physicalAddress.zip AS t1_c7, physicalAddress.validation_status AS t1_c8, physicalAddress.validation_attempt AS t1_c9, physicalAddress.modified AS t1_c10 FROM User_Address physicalAddress WHERE (User.physical_address_id = User_Address.user_address_id)

which gives me this error:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘User.physical_address_id’ in ‘where clause’.

The physical_address_id field is in the User table, so I’m not sure why I’m getting that error. In any case, that sql doesn’t look right. I think it needs to do a join.

4

The relation should be like that:


return array(

    array('physicalAddress' => array(SELF::HAS_ONE, 'SUserAddress', 'physical_address_id')

);
5

No, that’s not right either. I get this error when I try that:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘physicalAddress.physical_address_id’ in ‘where clause’. The SQL statement executed was: SELECT physicalAddress.user_address_id AS t1_c0, physicalAddress.address1 AS t1_c1, physicalAddress.address2 AS t1_c2, physicalAddress.city AS t1_c3, physicalAddress.county_id AS t1_c4, physicalAddress.county AS t1_c5, physicalAddress.state_province_id AS t1_c6, physicalAddress.zip AS t1_c7, physicalAddress.validation_status AS t1_c8, physicalAddress.validation_attempt AS t1_c9, physicalAddress.modified AS t1_c10 FROM User_Address physicalAddress WHERE (physicalAddress.physical_address_id=:ypl0). Bound with :ypl0=‘13776’

13776 is, in this case, the User record id.

Here’s the sql I would write to accomplish this:

SELECT * FROM User_Address ua

LEFT JOIN User u ON u.physical_address_id = ua.user_address_id

WHERE u.user_id = 13776

I would love to say something like this:

return array(‘physicalAddress’ => array(self::HAS_ONE, ‘SUserAddress’, ‘user_address_id’, ‘localKey’ => ‘physical_address_id’)

So that it knows to join the two tables on user_address_id and physical_address_id. How do I accomplish this?

6

Here’s how I solved this. It was simple once it was pointed out to me in the chat room:

return array(‘physicalAddress’, array(self::BELONGS_TO, ‘SUserAddress’, ‘physical_address_id’));

This is because there is only one physical address record for the user. Not sure why HAS_ONE didn’t work, but this will get you going.