CakePHP: HasAndBelongsToMany (HABTM)

WARNING, this post is related to the 1.1 version of the framework. Now there’s the 1.2, so maybe something may not work properly.

Originally posted by me on June 6th, 2006 on another blog.

Cake allow you to relate two tables in a n<->m relation. This is done by the HasAndBelongsToMany relationship (HABTM). In order to implement this relationship you need three table: the two tables to be related and a table for the relationship.

Profiles      relation-table          Users
--------      --------------          -----
id* --------> profile_id*     |------ id*
dx            user_id*    <---|       dx

If you’ll follow the cake naming conventions, implementing this relation is very simple. Let’s quickly sum up the conventions

  1. Relation table must be named like PluralTable1_PluralTable2
  2. Tables that give the name to the relation table must be alphabetically sorted. So if you have a users and a profiles table, you’ll get the profiles_users.
  3. The fields of the relation table must be the primary keys of the related tables and must be named like SingularTable_id. For example profile_id and user_id.
drop table if exists profiles_users;
create table profiles_users(
   profile_id int(8) unsigned not null default 0,
   user_id int(8) unsigned not null default 0,
   primary key(profile_id, user_id)
)engine=MyISAM;

Now in the model of the tables you need to relate them. This is done by the $hasAndBelongsToMany variable.

class User extends AppModel{
   var $name="User";
   var $hasAndBelongsToMany = array("Profile");
}

class Profile extends AppModel{
   var $name="Profile";
   var $displayField = "dx";
   var $hasAndBelongsToMany = array("User");
}

Done and done. Now everything should be handled automatically by cake.

Now you just need to insert the fields in the form. Example on the manual use a multiple section <select> tag. In order to create this control you just need the HtmlHelper and selectTag():

$html->selectTag('Profile/Profile',$profiles,null,array('multiple'=>'multiple'))

The only trick is the field name that must be RelatedTable/RelatedTable. In the example, if we are adding a user (users table) and need to save the specified profiles (profiles table), the name to be specified will be Profile/Profile.

In case you don’t like the use of a multiple select and prefer to use more than one checkbox, the workaround is something like the following.

In the needed controller let’s extract the needed values

class UsersController extends AppController{
   var $name="Users";

  function add(){
      ...
      $this->set("profiles",$this->User->Profile->generateList());
      ...
   }
}

Then in the view something like

<?php
   if(is_array($profiles)){
      foreach($profiles as $profile => $title){
         print "<input type='checkbox' value='{$profile}'
		name='data[Profile][Profile][]'>" . $title . "\n";
      }
   }
?>

Notice the name parameter. I’ve not used the HtmlHelper::checkbox() since I failed to make it works in this case.

Advertisements

34 thoughts on “CakePHP: HasAndBelongsToMany (HABTM)

  1. marcos says:

    Hi, nice explanation. However, I have a doubt. I have and associative table with the foreing keys, but also some specific fields for the relation, but when I do for example

    $this->User->findById(1)

    it return a User array with all his profiles, but not the info specific for each user-profile relation. How can I manage to get that info (the one in the associative table)?

    Thanks. 😉

  2. @marcos, don’t know. Generally the associative table is only for association, I’ve never had your case. What sort of information do you have in the associative table?

  3. Can this be applied to a joined table that joins 3 tables? Ie:
    Table1, Table2_names, Table3_last_names:

    Join table: table1_table2_names_table3_last_names?

    I need to join 3 tables. and 4 maybe

    Great tutorial.

  4. Gianni says:

    Hi, I’m a new CakePHP user, and I would like to know how can I display the field name, from the products table, in the view.

    Thaks for this article 😉

  5. Gianni says:

    Sorry, I’m using HABTM to relate two table (application and state) just like you describe in this article.

    States applications_states Applications
    ——– ——————– —–
    id* ——–> state_id* |—— id*
    name application_id* <—| name

    When I look at applications_states table the relation seems to work,
    but, in my index view, I don’t know how to display the State name corresponding to an application.
    I’m sorry for my english… I hope that I’m understandable enough

  6. @Gianni, If I undestood it right:

    if in your controller you have something like


    $this->set(“data”,$this-Application->findAll());

    in your view it should be enough to use something like

    foreach($data as $item){
    echo $item[“State”][“name”];
    }

    However if you would like I also speak Italian too (try reverse my
    nick) 🙂

    HTH
    Bye

  7. Gianni says:

    Ok ! Non avevo visto !
    Sara piu comodo cosi.

    Il problema e che ho scritto le stesse linee nel controller e la view, pero mi da un errore del tipo : Undefined index: name in /view/applications/index.thtml

    Ho un altra “table” : Grpapplication, che contiene id e name, che e definita come belongsTo nel modello application, e cosi nella view :
    echo $application[‘Grpapplication’][‘name’] e funziona benissimo !

    Non so se il problema viene dall fatto che utilizo Cake nella versione 1.1.xx con una relazione HABTM, o certamente ho dimenticato qualcosa…

    Comunque grazie !

  8. Anyone knows how to ‘interrelate’ instances of the vary same model? As in a way of grouping similar products, for example.
    Could I do this by adding $hasAndBelongsToMany = array(‘Product’); to my Product model? If yes, what would the relationship table look like, since you can’t have two product_id columns? Another example would be dependencies.

  9. Hi,
    I want to know, whether how to save data with HABTM relationship?
    I have 3 tables, Posts, Categories, and Categories_Posts. 1 post, can have 2 or more categories.

    First, i saved the data to Posts table, to get the last ID.
    But, I get confuse when saving data to Categories_Posts.

    Can you help me? thx a lot

  10. I don’t remember well but it should be enough to do a $this->Post->save(… ) if every relationship has been defined correctly in the Model and the view has been build correctly.

  11. This was fantastically helpful, thanks! My only problem is that the data isn’t saving on add or edit.

    It displays, and selects correctly but it never saves these associations. Am I missing something. I’ve never used bake and I am incorperating this into something that’s already been built do I didn’t think that erasing it all to bake would be a good idea either.

    *cheers!*

  12. Your article was helpful and I don’t know if it changed between versions. But relatedtable/relatedtable as the field name is not quite right.

    2 issues, minor one first, new syntax is with period I believe.

    Second more important issue, naming the form field this will get the selection to display the tables values in the xref, especially if there is a title field in the corresponding table and you do form->input. But it’s not the relatedtable name, it’s the related model name. So if you are in the User add view, doing form->input(‘Profile.Profile’) will display it, and the model will recognize and save it. Assuming you did put var $hasAndBelongsToMany = array(“Profile”); in the User model.

  13. Hi!
    Indeed a very time saving issue here… wow, thx a lot!

    i did it also, but i recognized that the relation table is a) not saved and b) exisiting relations are not displayed.

    So, i just put the $form->select-Stmt into the view. I did not change the model classes.

    Like that:
    echo $form->select(‘Role/Role’,$roles,null,array(‘multiple’=>’mu
    ltiple’));

    (I’ve a table names actions, roles and actions_roles; it’s in file views/actions/edit.ctp)

    Any idea what i’ve to change?

    Thx, Sam

  14. @sam, this post is related to the cakephp 1.0. Now there’s the 1.2, that by the .cpt file, it seems you are using.

    Unfortunly I still havent had time to do nothing with cake 1.2. Try asking in the mailing list.

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