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
- Relation table must be named like PluralTable1_PluralTable2
- 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.
- 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.
Nice and clear explanation of HABTM. Thanks for it!
You are welcome 🙂
Fantastic explanation. The manual really doesn’t cover the implementation of associative tables. Thank you.
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. 😉
@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?
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.
@Mariano: I don’t think so. However I’m not a cake developer so I can be wrong.
It survived because it had looked for the method.
Thank you!!
This article was a lifesaver!!
Master .. this article has saved my lot’s of time 🙂
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 😉
Sorry Gianni, I need more details. What is the tables structure? How they are related?
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
@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
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 !
Senza qualche stralcio di codice faccio fatica. Prova ad iscriverti alla mailing list (http://groups.google.com/group/cake-php) e poi chiedi lì mandando stralci di codice. C’è gente molto più in gamba di me 🙂
Ok, provo subito.
Grazie ancora !
Buen artículo! 🙂
No sabía que se podía hacer: $this->Modelo1->Modelo2->generateList()
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.
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
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.
Could you explain to save this to database?
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!*
@Terri, sorry but without any extract of code I can’t understand
why. You better ask in the mailing list
posting some code. There are many people more expert than me 🙂
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.
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
@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.
Thanks 🙂
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.
Can you help me?
I have a problem with saving and retrieve the data using HasAndBelongsToMany in CakePHP
All the information you find here:
http://stackoverflow.com/questions/21778347/cakephp-2-4-x-how-to-retrieve-and-save-data-using-habtm
I want to thank you in advance.
Sorry Nick, I’ve not been using/experimenting with PHP or CakePHP since 2007. I don’t really know how to help 😦
You can retrieve data HABTM with my answer here
https://stackoverflow.com/questions/12450707/cakephp-how-to-retrieve-habtm-data-with-conditions/25834568#25834568