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:

option_code.png

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\'',),

 


Did you know you can hire me?

I take on projects of all sizes. From Consulting to large Development Projects.

If you're starting a new Yii project and would like some help to get setup and running or you need some help with a particular module or you just need someone to develop the whole dang thing, then just ask ...


Beginning Yii ...

I'm very excited about a new Yii Training course that has just been published.

The course is designed for anyone wanting to learn Yii, whether new to frameworks or converting from another.

Eight chapters, each having five videos of between 3 and 5 minutes, take you through the basics of the Yii Framework, building a web application step-by-step.

Save 40% - introductory offer $20.99 / £13.19 / €16.19 / A$20.99

2 comments

  • twitter.49769145
    20/01/2012

    Yesterday's post: Yii relations using a non-primary key ~ <a href="http://t.co/ceQlRAG4" rel="nofollow">http://t.co/ceQlRAG4</a> #yii #relations

  • twitter.49769145
    19/01/2012

    Todays problem: Relations using a non-primary key in Yii ~ <a href="http://t.co/ceQlRAG4" rel="nofollow">http://t.co/ceQlRAG4</a> #yii #databaseDesign

Leave a Comment

twitterfacebookgooglelinkedin https://me.yahoo.com