CakePHP – using associationForeignKey with belongsTo

While defnining “belongsTo” association in CakePHP 1.2, there are five keys that you can use (to read more about association or belongsTo, checkout this page) . The five keys are className, foreignKey, conditions, fields, counterCache. In general, if you have followed CakePHP conventions, you wont need any other key. However, today I stumbled across a situation where the database was not structured according to CakePHP conventions. As a result, the belongsTo query that CakePHP was generating was not coming out right.

Consider a scenario where a “users” table belongsTo “loctions” table and the two tables are defined as shown below:
1. Users {id, username, passwd, zipcode}
2. locations {id, zipcode, city, zipcode}

Above tables break two of CakePHP’s conventions:
1. CakePHP expects “users” table to have a field “location_id”. However, in the above scenario, the “location_id” is named as “zipcode”. As shown below, this problem can be easily addressed using “foreignKey”

class User extends AppModel{
var $belongsTo = array(
      //now CakePHP will use zipcode instead of location_id
      'Location' => array('foreignKey' => 'zipcode')
    );
}

2. The second problem is that CakePHP assumes “users.zipcode” is equal to “locations.id” and therefore result in such a query

users left join locations on (users.zip_code = locations.id)

But it is locations.zipcode (and not location.id) that we want to associated with user.zipcode. This problem could have been easily solved by using “associationForiegnKey” (available only with HABTM). However, CakePHP ignores associationForeignKey when used along with belongsTo association. To address this problem there are two options. First, change your database schema to confirm with CakePHP’s conventions. Second, thanks to AD7six (see his comment below) is to set foreginKey to false and define condition. Thus, define the association between user and location as follow

var $belongsTo => array(
        'Location' => array(
            foreignKey => false,
            conditions => array('User.zipcode' => 'Location.zipcode')
         )
);

Note: Below is yet another solution that I was using before AD7six gave me the above idea. I don’t recommend this third option, but I just kept it over here so as to simply provide you with an idea of how problems can be solved using CakePHP.

Another solution is to change the primary key of the locations table from “id” to “zipcode” before a query is sent to the database and reset it to “id” after the query

// find uesrs and their locations (in app/models/uesr.php)
function getAllUsers(){
   $this->contain('Location');
   //fetch original primary key
   $originalPrimaryKey = $this->Location->primaryKey;    
   // (zipcode refers to the zipcode field in the locations table)
   $this->Location->primaryKey = 'zipcode' ;   
   $result = $this->find('all', array());
   // reassign original primary key
   $this->Location->primaryKey = $originalPrimaryKey;              
   return $result;
}

The above solution works but is not optimal as it involves lot of hard coding and also you have to make sure that every controller and model is properly setting primary key of location table. Below is a more optimal solution. While, it is based on the above approach (swapping primary key), it automates the process using beforeFind and afterFind functions. However, this approach requires defining associationForeignKey while defining belongsTo association.

//
// User.php
//
class User extends AppModel{
   var  $name = 'Article';
   var  belongsTo = array(
          'Location' = array(
           //refers to zipcode field of users table
          'foreignKey' => 'zipcode', 
           //refers to zipcode field of location table.
           'associationForeignKey' = "zipcode"   
            // While CakePHP ignores associationForeignKey,
            // I use it in beforeFind function
    );
    // A variable to temporarily store primary keys
     var $swapPrimaryKey = null;     

//
// beforeFind function - CakePHP calls beforeFind just before executing a query
//
function beforeFind($queryData){
   //Make sure swapKeys variable is initalized
   $this->swapKeys = array();
   //Check if any belongsTo association is defined in the Model
   if(isset($this->belongsTo) && !empty($this->belongsTo)){
      foreach($this->belongsTo as $key => $value){
         if(!is_array($value)) continue;
         //Check if associationForeignKey is defined.
         //If it is, then change the primary key of the associated model
         if(array_key_exists('associationForeignKey', $value)
&& !empty($value['associationForeignKey'])){
             // save orignal primary key
             $this->swapKeys[$key] = $this->{$key}->primaryKey;  
             //change primary key
             $this->{$key}->primaryKey = $value['associationForeignKey']; 
         }
      } //next $key
   } //endif
}  //end beforeFind

//
// Defining afterFind function. This function is called by CakePHP after it executed a query
//
function afterFind($results){
   //reset primary keys for all belongsTo association
   foreach($this->swapKeys as $key => $value)
      $this->{$key}->primaryKey = $value;
   unset($this->swapKeys);
   return $results;
}

//
// get all users and their locations (with above changes..this function becomes a piece of cake)
// compare it with the above getAllUsers() - this is much more elegant and simple
//
function getAllUsers(){
   $this->contain('Location');
   return  $this->find('all', array());
}

}

If you want, you can move afterFind and beforeFind functions in app_model.php.

Comments are welcome!