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?');
}
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_AddressphysicalAddress 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.
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_AddressphysicalAddress 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