Looking up foreign key values using Model::displayField

Posted by Felix Geisendörfer, on Feb 18, 2007 - in PHP & CakePHP » DataSources, Models & Behaviors

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:

php
  1. // Make the User belongTo the group 'Freemium' which always has the id 1
  2. $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:

php
  1. /**
  2.  * A generic function that simply returns the value of a given $field for an record
  3.  * that matches the given $conditions. If $create is set to true and no record matching
  4.  * the conditions can be found, it will be created automatically.
  5.  *
  6.  * @param string $field The name of the field to look the value up
  7.  * @param mixed $conditions Either an array of conditions, or a string when using the displayField for the lookup
  8.  * @param boolean $create If set to true and $conditions don't match anything, a new record will be created
  9.  * @return mixed Either false on failure, or the value of $field matching $conditions
  10.  */
  11. function lookup($field, $conditions, $create = false)
  12. {
  13.     // If $conditions is no array
  14.     if (!is_array($conditions))
  15.     {
  16.         // Then assume a value for this model's displayField was provided
  17.         $conditions = array
  18.         (
  19.             $this->getDisplayField() => $conditions
  20.         );
  21.     }
  22.    
  23.     // Try to find a $fieldValue matching our $conditions
  24.     $fieldValue = $this->field($field, $conditions);
  25.  
  26.     // If no $fieldValue matching the $conditions was found, and $create mode is set to false
  27.     if ($fieldValue===false && !$create)
  28.     {
  29.         // Return false meaning that the lookup failed
  30.         return false;
  31.     }
  32.     elseif ($fieldValue===false && $create)
  33.     {
  34.         // Otherwise, create a new record matching our $conditions
  35.         $this->create($conditions);
  36.        
  37.         // If saving the new record failed
  38.         if (!$this->save())
  39.         {
  40.             // Return false meaning that the lookup failed
  41.             return false;
  42.         }
  43.        
  44.         // When querying for the new $fieldValue, make sure we find the new record for sure
  45.         $conditions[$this->primaryKey] = $this->id;
  46.        
  47.         // Read the $fieldValue again (so we get things like default values of the db, cakephp created/modified fields, etc.)
  48.         $fieldValue = $this->field($field, $conditions);
  49.     }
  50.            
  51.     // Return the value of our $field
  52.     return $fieldValue;
  53. }

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:

php
  1. $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:

php
  1. $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