Yii – Relations using a non-primary key

Often mistakes you make in your database design stage come around and bite you in the butt later!

My Mistake

I’m just testing a new reservation system that should be going live soon and it’s looking really good, expected results are bang on, the system is nice and stable, and (even better) easy to navigate.

However, I just clicked on a sortable column in a gridview and realised that it was sorting on the alphabetic value of the column rather than the order that the client might expect.   Added to that, in the original design, I used the lookup code and not the ID in the foreign key table! HELP!

This is a status column and therefore, in a business workflow, the customer will expect the column to appear in the order that these things happen in the business.  Therefore, I’m going to have to get the sort order from my Lookup table that has a column ‘sort_order’.

subnote: I believe this kind of attention to detail makes the difference between just a contented customer and a truly ecstatic customer!

Summary

Problem:  A CGridview using a column that links to another table but not using the primary key.

Example:

Solution

A stop gap solution is to define the relation between the reservations table and the lookup table using a column other than the primary key of the lookup table.  Obviously, the better solution would be to change the status column to hold the ID of the lookup rather than the lookup-code but that will have to wait for another day.

I couldn’t find any absolute way of doing this in the documentation so spent some time tinkering around with the realtionship parameters.

I ended up, effectively hard coding the join using the ON clause as follows:

  'StatusOption'=> array(self::BELONGS_TO, 'Options', '', 'on' => 't.status=option_code AND option_name='Status'',),

 

Let’s Start a Project!

Contact Me