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!

Advertisements

About Ritesh Agrawal

I am a applied researcher who enjoys anything related to statistics, large data analysis, data mining, machine learning and data visualization.
This entry was posted in CakePHP and tagged , , . Bookmark the permalink.

21 Responses to CakePHP – using associationForeignKey with belongsTo

  1. AD7six says:

    All of the above can be achieved in 1 line of (executed) code:

    $this->User->bindModel(array(
    ‘belongsTo’ => array(
    ‘Location’ => array(
    ‘foreignKey’ => false,
    ‘conditions’ => array(‘User.zip_code = Location.zipcode’)
    ))));

    Any typos at no extra charge.

  2. ragrawal says:

    hi AD7six,

    oh awesome, I didn’t realized that you can set foreignKey to false. I checked this solution and it works perfectly great

  3. nate says:

    Hm… I don’t think you understand what associationForeignKey is supposed to mean. It’s not that CakePHP “ignores” it for belongsTo, it simply does not apply.

    Take the example of Post hasAndBelongsToMany Tag: setting ‘foreignKey’ overrides posts_tags.post_id, and ‘associationForeignKey’ overrides the posts_tags.tag_id key, not Tag.id.

    The only reason for ‘associationForeignKey’ in HABTM is because, since it uses a linking table, there are two foreign keys used to do the mapping. There is no case in any association where a key is provided to override the primary key in another model.

  4. ragrawal says:

    Hi Nate,

    Glad to see that you read my post 🙂 .

    I think of associationForeignKey differently, but this does not mean our views are contradictory. I just think of associationForeignKey as the field in the other (associated) table with which current table’s foreignkey should be paired.

    However, irrespective of how we define associationForeignKey, the point that the post was trying to make is that there is no simple way to specify the field (in the other table) that corresponds to the foreignKey of the current table in belongsTo association. Assuming that its id or primaryKey is too restrictive for CakePHP and, for rationale, see the example I gave in my post. However, it is exactly the role that associatedForiegnKey plays the roles. It allows you to define the field that should be paired with the current table’s foreignKey. In case of HABTM, the other table happens to be the linking table.

  5. nate says:

    Hi,

    “I think of associationForeignKey differently, but this does not mean our views are contradictory.”

    Well, if I may be so bold, I think the key difference here is that one of us is right and one of us is wrong. 😉

    “I just think of associationForeignKey as the field in the other (associated) table with which current table’s foreignkey should be paired.”

    Hm… I’m not sure how to put this delicately, but, no. That’s simply not accurate. The linking table in an HABTM relationship is akin to a compound foreign key, hence the extra array key. Again, note that none of the settings have anything to do with the linking of primary keys. The linking table is an abstraction that represents a relationship in the same way that a foreign key would — you might be getting confused because you’re thinking of it in concrete terms.

    Also, regardless of anything else (and I don’t mean this to sound haughty or elitist), when you create something, you get to decide what it means. When we created the framework, we assigned a very particular meaning to that term, and within the context of CakePHP, it will always have the exact meaning that we give it and no other. “Thinking” that it has a different meaning does not make it so.

    “…the point that the post was trying to make is that there is no simple way to specify the field (in the other table) that corresponds to the foreignKey of the current table in belongsTo association.”

    Again, not true. Andy demonstrated a simple and effective method for disabling the default constraint, and assigning a custom one. There is nothing difficult or complicated about it.

    “Assuming that its id or primaryKey is too restrictive for CakePHP”

    Well, that is your opinion, one which I happen to disagree with, and one that I could easily argue is mostly objectively wrong as well. In 90-95% of all cases, the default behavior that CakePHP provides is exactly what you want to have happen.

    “However, it is exactly the role that associatedForiegnKey plays the roles.”

    My apologies for being so blunt, but again, wrong. Please see above.

    “It allows you to define the field that should be paired with the current table’s foreignKey. In case of HABTM, the other table happens to be the linking table.”

    As I mentioned, your confusion on this issue has to do with the concrete way in which you’re thinking about the linking table: as a first-class element in the domain model (no), rather than a glorified foreign key (yes).

  6. ragrawal says:

    Hi Nate,

    @ semantic difference
    I don’t believe, our views about associationForeignKey can be simply categorized into right or wrong, but I guess we are approaching from different perspective. Moreover, its not about how we define associationForeignKey, but what role does it play within the overall framework.

    For me, it simply tells the framework with what field to pair the foreignKey of the current table.

    @ 90-95% cases:
    As a developer, we all have to make certain assumptions. However, with each assumptions, there are trade-offs between simplicity and completeness. In the case of associationForeignKey, you have your own meaning and assumptions and it works for 90-95%. I agree with that.
    However, I also think, making associationForeignKey as a part of belongsTo association will increase that percentage (making it more complete) and at the same time keeping the simplicity of the CakePHP framework.

    @ andy’s solutations:
    I agree andy’s solution is simple and elegant. That’s the reasons that I included it as a part of my post and promoted it over my own solution. However, internally, I don’t feel it to be consistent with the CakePHP’s overall approach, at-least based on my view of associationForeignKey. Within CakePHP, “conditions” are never used to define associations, but to define constraints while retrieving records. For defining associations, there are foreignKey and associationForeignKey.

  7. nate says:

    Hi again,

    A couple more clarifications:

    “I don’t believe, our views about associationForeignKey can be simply categorized into right or wrong, but I guess we are approaching from different perspective.”

    Your relativistic view of things is incompatible with reality. What you just said is sort of like me saying “I have a different perspective on gravity, so I believe that I can fly”. Just because I believe it, doesn’t make it true. Our differences on this issue are not semantic at all — they’re fundamental divergences in approach and how we see things. It seems that your apparent inability to think about this conceptually is preventing you from seeing that. :-/

    In the context of CakePHP, we as the architects specify rules and definitions for things. Therefore, when talking about CakePHP and the features in it, there are absolute right or wrong answers.

    “Moreover, its not about how we define associationForeignKey, but what role does it play within the overall framework.”

    Again, within the context of CakePHP, ‘associationForeignKey’ has a specific intended functionality and overall meaning. Therefore, it is about both: how we define it *is* the role it plays within the framework.

    “For me, it simply tells the framework with what field to pair the foreignKey of the current table.”

    Since ‘associationForeignKey’ already has a defined meaning within CakePHP, and that isn’t it, you’re making an objectively false statement. The your opinions and perspective have no bearing on the fact that this statement is false.

    Furthermore, you statement will continue to be false until you write your own framework and redefine ‘associationForeignKey’ with your own meaning and rules. Do you understand what I’m trying to communicate here? This isn’t about perspectives or opinions, it’s about definition of terms.

    “As a developer, we all have to make certain assumptions. However, with each assumptions, there are trade-offs between simplicity and completeness.”

    This is absolutely true, you’ve hit the nail on the head. However, I’d say a slightly more accurate way to put it is that it’s a trade-off between flexibility and ease-of-use.

    “However, I also think, making associationForeignKey as a part of belongsTo association will increase that percentage”

    Maybe, but that would be wrong, since it’s not conceptually consistent with what ‘associationForeignKey’ means. In order to maintain conceptual integrity (see http://en.wikipedia.org/wiki/The_Mythical_Man-Month#Conceptual_Integrity), we’d also need to implement the same thing in all other associations. In the Posts/Tags example, we’d need a way to override Tag.id when it’s used in the join. Since HABTM already has an ‘associationForeignKey’ (since that’s what it’s already been correctly defined for) we’d need to add yet another key. Either way, what you’re suggesting breaks the model, and is therefore (as well as for the other reasons I previously mentioned) incorrect.

    Furthermore, since the default functionality works fine for 90-95% of cases, and other settings are available to account for outside cases, there is absolutely no reason to alter anything. Both simplicity and completeness are already covered.

    “However, internally, I don’t feel it to be consistent with the CakePHP’s overall approach, at-least based on my view of associationForeignKey. Within CakePHP, “conditions” are never used to define associations”

    Again, reality exists irrespective of your view of it. You can set ‘foreignKey’ to false to disable the default behavior, and use ‘conditions’ to define it yourself. Simple, and perfectly consistent with everything else. Also, as you can clearly see, the ‘conditions’ key is part of the association’s definition, so it is by it’s very definition a part of the way in which associations are defined.

    “For defining associations, there are foreignKey and associationForeignKey.”

    I suggest you develop a deeper understanding of how CakePHP actually works before posting a follow-up comment, because so far, most of your comments, and this post itself, have communicated to me that what you *don’t* know outweighs what you *do* know by orders of magnitude.

    Also, I think it’s a bit silly for you to suggest that you know the way things are or should be done in CakePHP, when you’re having a conversation with the person who actually decides such things. 😉 Just sayin’.

    Finally, in the future, you would do well to gracefully admit when you’re wrong — you’ll at least save some face that way.

  8. ragrawal says:

    Okie Nate, you are right 🙂 .

    You are the developer of CakePHP and not me, and CakePHP is definitely one of the best framework that I have ever used.

    So, It really doesn’t bother me how things are defined and whether conceptual integrity is maintained or not by CakePHP, as long as I can keep my project going and keep my code clean and simple.

  9. sundm says:

    Great thing, you spared me some brains…
    but the condition solution didn’t work for me, ’cause cake made some weird things with the conditions array:
    the SQL looked something like this:
    …LEFT JOIN … ON (`User`.`zipcode’ = ‘Location.zipcode’) which alway gave me NULL Results. So once i put my Glasses on, watched the debug sql, putted it into an sql browser, i saw that this would not work, hence it compares an id with a string.
    So in my working model it looks like:

    ‘condition’ => array(‘User.zipcode = Location.zipcode’),

    and …tadaaa…. it works

    maybe you can leave out the array (was just lazy)

  10. macrocode says:

    THANK YOU FOR THIS WRITE UP! I’ve been racking my brain on binding MAC addresses together and this article saved the day! THANKS!!!

  11. Chuck says:

    I know this post may be an example, but for the record, the whole zip code issue is a BAD example. Primary Keys are designed to be unique. If you try to assert the zip code as the primary key, there will be collisions in records. Multiple cities can (and do) share zip codes. This means that the foreign key will correspond to multiple records. I am not certain how the DB will react in this situation. Just my 2 cents.

  12. uniacid says:

    Anyone else try this on Cake 1.3?

    I need to associate Gallery.photo_id with PressRelease.photo_id but I tried the above methods and I get errors with unsuccessful results…

  13. ranjeet says:

    hello
    I m new to cake php and i m getting this error
    wyy this so
    Call to a member function find() on a non-object

  14. vovagold says:

    Hey, you have a very bad typo, which took some time to find it.
    Instead of
    conditions => array(‘User.zipcode’ => ‘Location.zipcode’), which will generate next code
    ON (`User`.`zipcode` = ‘Location.zipcode’), which is wrong

    you just need change it to
    ‘conditions’ => array(‘User.zip_code = Location.zipcode’)
    so just exactly as first commenter did.

    Cheers!

    • Hi vovagold,

      Can you explain why CakePHP expects User.zip_code when the column name is zipcode ? I haven’t used CakePHP for about last 2 years so forgot all the details.

  15. Darren says:

    Thanks guys, setting fk to false and using conditions instead solved my problem in Cakephp 2 🙂 Much appreciated!

  16. marky says:

    5 years later, we still have this limitation. I’ve tried to do a lot of things to make this work but in the end the first comment above works. I put it in my controller function so it will override whatever was defined in the Model. This is just so lousy, but then the tables I’m using was designed by somebody else so poorly. Jeez!

    $this->User->bindModel(array(
    ‘belongsTo’ => array(
    ‘Location’ => array(
    ‘foreignKey’ => false,
    ‘conditions’ => array(‘User.zip_code = Location.zipcode’)
    ))));

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s