Contact Us

Looking up foreign key values using Model::displayField

Posted on 18/2/07 by Felix Geisendörfer

Deprecated post

The authors of this post have marked it as deprecated. This means the information displayed is most likely outdated, inaccurate, boring or a combination of all three.

Policy: We never delete deprecated posts, but they are not listed in our categories or show up in the search anymore.

Comments: You can continue to leave comments on this post, but please consult Google or our search first if you want to get an answer ; ).

Update: I updated the lookup function below to make use of Model::field instead of Model::find which should be a better choice in this context ; ).

Hey folks,

today I was spending some implementing various aspects of my new web application. One of the things I needed to do was to make sure that all Users that sign up for an account automatically become members of a certain group called 'Freemium'. For this reason my users table has a foreign key field called 'group_id' where I store what group each User belongsTo. However, I'm somebody who dislikes hard coding "magic numbers". This means I hate to code things like this:

// Make the User belongTo the group 'Freemium' which always has the id 1
$this->User->set('group_id', 1);

To me that is a bad practice for several reasons. For one it makes the code incredibly hard to read if this is done for several groups (with different id's) without explaining in the comments what is going on. The other thing is that I like my applications to be db data agnostic. That means if somebody would just empty all tables in an application I wrote, I would like it to automatically recover to it's original install state. I know this is not practical / desirable for all kinds of applications, but I find it very useful for the projects I often deal with (and especially applications I plan to distribute).

So how to do this better? Well it's pretty simply actually. Instead of hardcoding the 'group_id' to 1 for the sign-up process, one should instead query the groups table for a group called 'Freemium' and use it's id. If this group doesn't exist yet, then simply create it and use the 'id' value of this new record. So when going through this process today, I realised: 'Hey, this is something I need pretty often' and decided to make this more generic and less verbose. The result is a little function called 'lookup' which I recommend you to add to your AppModel:

 * A generic function that simply returns the value of a given $field for an record
 * that matches the given $conditions. If $create is set to true and no record matching
 * the conditions can be found, it will be created automatically.
 * @param string $field The name of the field to look the value up
 * @param mixed $conditions Either an array of conditions, or a string when using the displayField for the lookup
 * @param boolean $create If set to true and $conditions don't match anything, a new record will be created
 * @return mixed Either false on failure, or the value of $field matching $conditions

function lookup($field, $conditions, $create = false)
    // If $conditions is no array
    if (!is_array($conditions))
        // Then assume a value for this model's displayField was provided
        $conditions = array
            $this->getDisplayField() => $conditions
    // Try to find a $fieldValue matching our $conditions
    $fieldValue = $this->field($field, $conditions);

    // If no $fieldValue matching the $conditions was found, and $create mode is set to false
    if ($fieldValue===false && !$create)
        // Return false meaning that the lookup failed
        return false;
    elseif ($fieldValue===false && $create)
        // Otherwise, create a new record matching our $conditions
        // If saving the new record failed
        if (!$this->save())
            // Return false meaning that the lookup failed
            return false;
        // When querying for the new $fieldValue, make sure we find the new record for sure
        $conditions[$this->primaryKey] = $this->id;
        // Read the $fieldValue again (so we get things like default values of the db, cakephp created/modified fields, etc.)
        $fieldValue = $this->field($field, $conditions);
    // Return the value of our $field
    return $fieldValue;

There are several ways to use it. The first, and most simple way, is to rely on Model::displayField. CakePHP will always try to figure out which one this is for you by checking if your model has a 'name' or a 'title' field. So my groups table for example has a field called 'name' which CakePHP automatically detects. This allows me to replace the code from above with this:

$this->User->set('group_id', $this->Group->lookup('id', 'Freemium'));

But one of the requirements was also that the group 'Freemium' is automatically created if it doesn't exist already. In order to do this we just have to set the 3rd $create parameter to true:

$this->User->set('group_id', $this->Group->lookup('id', 'Freemium', true));

And voila, we have met all initial requirements for a best practices foreign key lookup approach using a simple one-liner. If you want to get more fancy, you can also make the 2nd parameter an array of $conditions to use for finding the lookup field value, but I don't think one will need that very often. I think this function is most efficient and elegant when used to lookup id values of groups, types and other things of that nature.

Alright, I hope some of you find this post useful. One last thing I got in line is that I want to point out yet another very useful tool for windows, called Taskix. It allows me to do one of the things that I've wanted to be able to do forever: to re-arrange my windows in the taskbar. You simply download it, start it, and from that point on you can simply drag and drop your opened windows to any position in the taskbar. Big thanks to Adrian Schlesinger who wrote it!

-- Felix Geisendörfer aka the_undefined


You can skip to the end and add a comment.

Tom said on Feb 18, 2007:

Heya felix, long time no see, but this time/post i have to comment, that this piece of code is very useful for me ; ) ty mate!

ok, have to go back to my cmdb project, happy baking ;o)

Lucian Lature  said on Feb 18, 2007:

Hi, Felix!...

Just wanted to know...what are you working on?'s your own project or is it a client's?...

Daniel Hofstetter said on Feb 18, 2007:

Instead of using a magic number you could use a constant, so instead of using the value "1" you would have something like "FREEMIUM_GROUP_ID".

Felix Geisendörfer said on Feb 18, 2007:

Lucian: I was talking about my own web application I'm working on. I'll probably do a post about it in a couple of days ; ).

Daniel: Well, this still doesn't allow for automatic db regeneration if tables are emptied. Plus I'm just not a huge fan of global constants these days : ).

Ben  said on Apr 13, 2007:

thanks for taskix link!

Felix Geisendörfer said on Apr 14, 2007:

Ben: Np. I really like the tool and am still using it so I'm happy to share it ; ).

Jason Leveille said on Apr 24, 2007:

Great post Felix. This technique is superb and I can't wait to implement it in future work.

Mark  said on Sep 27, 2007:


(old post I know, but wanted to point this out for others)

Using Magic numbers and using CONSTANTS are the same thing in this context.

The problem is the value for the group_id he is using is a primary key (autogenerated) from the database. If the table were dumped, that value would not restart at 1, but it would continue incrementing. So the next insertion after an emptied table not match the magic number or constant.

This is a great solution to a very common problem.

The other solution is not to use ID's for this at all, but rather use names. i.e. if I have these groups:




group membership is usually implemented such that you have the groupid and the userid in a join table (user_groups).

Alternatively, we could have our groups table like so:




Refer to the groups by name (since title can be changed/translated but name should not)

Just my .02 cents

This post is too old. We do not allow comments here anymore in order to fight spam. If you have real feedback or questions for the post, please contact us.