Modeling relationships in CakePHP (faking Rails' ThroughAssociation)
Posted on 26/10/06 by Felix Geisendörfer
Ok, this has been something I found very delightful when I first heard about it (see this speech by DHH and slides). But somehow I didn't really manage to blog about so far, which means it's about time to do this right now. The topic can be simply discribed as: How to implement ThroughAssociations (as they are known in Ruby on Rails) in CakePHP. The idea behind it is pretty simple: Often you have two Models that are associated with each other where setting the associations themself via hasAndBelongsToMany isn't quite enough for what you try to to. Let's say you have Users and Groups and you want to not only store what Users belong to what groups, as you would normally do with a join table and a hasAndBelongsToMany relationship, but you would also like to track when a User became a Member of a certain group and maybe even add a note about this. If you try to add additional fields to your Users or Group tables you'll certainly run into issues very soon, because this information simply does not belong into any of those Models. Same goes for creating the relationship (i.e. adding a User to a Group) with the Controllers. There is no way to do this in a CRUDful way. You'll have to add odball functions like UsersController::add_to_group() or in other words create messy code.
I don't like to break the CRUD pattern, so when I first heard about the concept of modeling relationship I instantly fell in love with it. Basically what this is all about, is to create a Model and a Controller for your relationships. So in our Users<-HABTM->Groups example we would simply create an additonal Memberships Model. Our new relationship would be: User->hasMany->Groups through Membership. And Group->hasMany->Users through Membership. Now unfortunatly CakePHP doesn't support the through association, but there is a simple workaround to this. Instead of using the through operator we do this:
User->hasMany->Membership
Group->hasMany->Membership
Membership->belongsTo->User
Membership->belongsTo->Group
Because of CakePHP's recursive associations, we achieve a similar effect as the trough operator would have in Rails. Now the cool thing is that you can add additional fields to your memberships table like 'id', 'created', 'updated', 'note' and others.
In case you want to see it working, I setup a little scaffolding demo here:
http://demos.thinkingphp.org/relationship_modeling/
I'll probably have to take it down due to Spam soon, but meanwhile feel free to browse around and to perform non-destructive operations. I didn't add any validation, nor a check for forbidding double Memberships, but this would be easy.
In case you are interested in the code, here it comes:
{
var $name = "Membership";
var $belongsTo = array('User', 'Group');
}
{
var $name = "Users";
var $scaffold;
}
{
var $name = "Groups";
var $scaffold;
}
{
var $name = "Memberships";
var $scaffold;
}
And not to forget the SQL Dump:
[sql]CREATE TABLE `groups` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(11) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `memberships` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) default NULL,
`group_id` int(11) default NULL,
`created` datetime default NULL,
`updated` datetime default NULL,
`note` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;[/sql]
Alright, I hope this is similiar enough to the ThroughAssociation in Rails to scale, in case there are some RoR folks listening, let me know if I missed something.
-- Felix Geisendörfer aka the_undefined
Did you like this blog post? If so, please consider subscribing to the Blog RSS feed.
You can skip to the end and add a comment.
Felix Geisendorfer's Blog: Modeling relationships in CakePHP (faking Rails ThroughAssociation)...
...
This is already halfway implemented in Cake 1.2, but only for MySQL, i.e.:
class User extends AppModel {
var $hasAndBelongsToMany = array("Group" => array("with" => "Memberships"));
}
And the join table fields will be selected as 'Memberships' within the association.
That's great news, guess I should spent more time on Trac ; ). I'm not quite sure if I like the 'with' field, 'through' seems more intuitive to me, but I can life with it.
pretty cool. i've been asking about this in the google mailing list a few months back. thanks for the idea.
i hope 1.2 gets into a stable version soon. there are lots of nifty stuff there that i want to use, things such as this through association and behaviors (acts_as in rails, nice), but i don't want to migrate to it yet. i was never fond of being on the bleeding edge with edgerails, and i'd never risk using cakephp 1.2 on production apps. (at the risk of sounding too much of a rails copycat, it would make sense to call it edge cake. after all there's bake, from rake. :) )
Very nice Felix. Thanks for sharing. I hope Cake 1.2 formally supports this feature :)
[...] ThinkingPHP and beyond » Modeling relationships in CakePHP (faking Rails’ ThroughAssociation) Modelling ThroughAssociations in CakePHP (tags: cakephp associations model) [...]
I've use something similar to this for a three-way join table. Like, Users-Resources-Rights. For example, User bob has admin Rights for a classroom Resource. Sort of like a Person to Permissions to Object, coupled association.
Hi there,
nice work. Saved me some trouble. Of course I ran into new problems ;)
How do you get cake to actually perform an update statement instead of an insert for the new tables, if they have a combined primary key. Let's say you want to save the assocation between users and tags and want to add the count of each tag for each user. I made up a table for this, which contains a combined primary key user_id/tag_id and an additional count column. However cake just does not recognize the primary key. It performs new Inserts no matter if the user_id/tag_id combination is already present or not. Is there any way to solve this using CRUD or do I have to use custom SQL?
Andreas Stephan: Do not, I repeat, do not use primary composite keys, ever. They are evil and will only cause you trouble ; ). Give your table an id field which is primary and auto incrementing, and take care of forbidding double-entries by yourself. To me that's the best way to go (not only in CakePHP, but in general).
Heya Felix,
Do you have any clue about when/if core will start supporting:
This through association.
HABTM searching where a condition is in the foreign model.
Just wondering on these bits. right now, I've been using your faking hacks to get where I need to go....
Hey Walker: The 'with'-association mentioned by nate is working perfectly in Cake 1.2. I'm using it in a project and didn't have a single issue with it. However, keep in mind that in order to really model a relationship you'll still need a Model/Controller for the HABTM table.
Other then that, I'm using an association like this in my Gallery Model:
var $hasAndBelongsToMany = array('Picture' => array('with' => 'GalleryPicture'));
Let me know if this works for you.
Yeah! I definitely appreciate the 'with'.
As for the Model/Controller for the HABTM table, that the one piece of the puzzle that I'm more than eager to get rid of.
Felix: in your "Gallery->GalleryPicture
[... agh, my comment got cut off]
Anyways, in the example, are bot the Gallery and Picture models $HABTM "with"?
What's the correct association for the GalleryPicture model? $belongsTo both?
Bryan: Yeah, I use this for my GalleryPicture Model:
var $belongsTo = array('Gallery', 'Picture');
For my Picture Model however, I only use 1 association:
var $hasMany = array('CachedPicture' => array('dependent' => true));
This is because I never need to know what Gallery a picture belongsTo if I've not requested it as part of a set of GalleryPictures. Otherwise I'd use a similiar HABTMwith association as for my Gallery Model.
Usefull information!!
Unfortunately I had to find this out by myself. I'm using this kind of approach in my CMS to associate content elements to the navigation nodes thereby sorting them into different slots (main content, sidebar, ...) defined in the layout. So my connectors belong to *three* tables instead of just two. I don't know if this would be possible with TroughAssociations but this "workaround" seems to be much more flexible to me.
Ideally, the Through/With functionality will be ironed out in 1.2, but in the meantime I needed to use it on a current project. The only problem I'm having is that if I delete a "User", the associated record in the "Membership" isn't deleted along with it.
My solution was to include some logic in my controller - FindAllBy on my Membership model, then delete the found ids before deleting the User.
Is there a more efficient way to do that?
Sorry, nevermind... I had a foreignKey relationship defined in MySQL that was causing the problem.
[...] Modeling relationships in CakePHP (faking Rails’ ThroughAssociation) [...]
I'm sure this is a stupid question, but that fact alone doesn't usually stop me!
Having spent ages on one part of my system getting HABTM to play nicely, I then realised that I needed to join the tables in some queries in order to use the relationship in a useful way. The technique described here seems to be just the thing.
However, I'm stuck on how to get the controller for one model (e.g. User) to add the appropriate row into the join table (e.g. Membership). Since I was using HABTM I'm currently trying to pass an array of Membership/Membership ids and use $this->User->save($this->data) - but that's just not working :(
Oh well, on with the struggle. I just thought it may be worth posting here in case anyone can point out the error of my ways and/or to highlight that such stupid people exist ;)
Damien: I'm not sure I understand your problem properly, but you are welcome to either write it up here (using cakebin for code: http://bin.cakephp.org/add/damien) more lengthy or even better on the mailing list / #cakephp. HTH, Felix
First of all, thanks for the article! It helped a LOT.
Just to clarify on one snippet, quote above: "break the CRUD pattern"? Isn't the "pattern" really "MVC"? CRUD isn't really a "pattern" at all, at least not in the sense used here, in that, *any* select,insert,update,delete goings ons fall into the CRUD scope. If aou use straight up procedural code or CakePHP framework.
I realize it may sound nitpicky, but I just want to clarify...
Also, one thing that is NOT clear, still. How would you tackle the problem of changing a lot of memberships at once? Say, all memberships for a particular User? Something like, from a user interface perspective, a view that shows the Users name, and then a list of all their memberships. There are checkboxes next to each Membership. Check all, then click the "DELETE" submit button.
To extend this, since the Membership does not really contain the Group information, it really should be presented as a list of all possible Groups for any User, with checkboxes next to each. Plus, if the User has a Membership relationship with a particular Group, that Group is highlighted somehow.
How would you go about programming this using CakePHP? I mean, it sounds like this should be a User oriented CakePHP view, but you are really making changes to the Membership model, and not either User or the Group models. CakePHP (at least as described in the manual, and in scaffolded MVCs), assumes that you are working on one instance of a model at a time. So how would one build a data array of all the checkboxes that can then be processed, in a loop, as each item in the array being data for the Memberships model?
I hope that is clear! whew!
I have a very similar problem, and I figured out a solution, and it works, but I don't know if it is the best, most Cake-y way to do it.
I'm writing up an article that describes what my solution was. I hope to get it up within a week or so. Then I'll come back for abuse.
Thanks!
JDSilveronnelly: Sorry I'm a little too busy to give you full support here. But what you are looking for might be in Cake 1.2 -> Model::updateAll / Model::deleteAll.
HTH, Felix
Hi Felix,
in the SQL for a case using 'with' , the join table - should I give it a primary ID of its own, or use the 2 foreign keys as it is with a typical HABTM join table (unless I am mistaken).
Also, thanks for this discussion everyone - was very glad to find it today!!
Luke
I saw on the group that you say to use ID (which is my instinct too, as thats what I ve always done ) but the manual just is thin on HABTM examples. So I did that and it seesm to work OK, I get duplicates but I can add a beforeSave, I think? You mention some extra bits of code but I wasnt certain what you mean though?
Luke: Use an 'id' field in your join table, the manual is a good start and you'll see it greatly expand rather soon, but for now take my word for it.
What dupplicates do you get? If you use MySql5 you can make add a UNIQUE index to your 2 FK fields which will prevent dupplicates. Do you use a dedicated Model for the HABTM table? If so you can use beforeSave in it to your liking for records saved with it.
hey Felix, thats great. Thanks for the quick response!
Yes I was getting duplicates - I hadn't set the unique key - think I will do that, as well as add the beforeSave, in which I will check if there is already an entry in the db.
Also, I understand your point of the manual -- the 1.2 alpha manual stops right at the HABTM example :) and I noticed with a more typical HABTM one, that I hadnt put in an ID that the scaffold still looks for an ID - which never used to happen so I imagine they have built in to use IDs for HABTM tables now.
Thanks
Luke
sorry - I should have said I am using the latest nightly build of 1.2 and that a notice error was given by scaffold (and BAKE IIRC (think they must use same templates?) )
Oh, I should add that I found it tricky getting the modelnames and form of name for "with"=> "JoinTablename"
do I have it right that for
Plants and Rooms - the join table is called plants_rooms in MySQL, then the model filenames are
plant.php, room.php and plants_room.php, and then the model is
class PlantsRoom extends AppModel {
var $name = "PlantsRoom"
like that and in plant.php :
var $hasAndBelongsToMany = array(
"Room'" =? array("with" => "PlantsRoom"
it throws errors if I dont have this style, at least ;)
Great article! I was doing a similar thing without even knowing what it was called. (I just knew that it was like a hasAndBelongsToMany association join table with extra fields) Here is an example table (for PostgreSQL):
CREATE TABLE numeric_specs (
id SERIAL PRIMARY KEY,
numeric_spec_name_id INTEGER NOT NULL REFERENCES numeric_spec_names ON UPDATE RESTRICT ON DELETE RESTRICT,
item_id INTEGER NOT NULL REFERENCES items ON UPDATE RESTRICT ON DELETE RESTRICT,
source_id INTEGER NOT NULL REFERENCES sources ON UPDATE RESTRICT ON DELETE RESTRICT,
sorting_date TIMESTAMP NOT NULL,
display_date TEXT NOT NULL,
value NUMERIC(20,4) NOT NULL,
numeric_spec_unit_id INTEGER NOT NULL REFERENCES numeric_spec_units ON UPDATE RESTRICT ON DELETE RESTRICT
) WITHOUT OIDS;
CREATE UNIQUE INDEX
numeric_specs_ak ON
numeric_specs(numeric_spec_name_id, item_id, sorting_date);
To avoid the duplicate problem, there is a unique index on three of the columns (the foreign keys to the two tables that are being associated as a many to many or hasAndBelongsToMany relationship, plus the sorting_date field so that I can have different values for different dates).
I created a model for this table so I can insert, update and delete numeric_specs.
I believe that it would work the same way if I have made the terms in the UNIQUE INDEX be the foreign key, and made id be UNIQUE NOT NULL SERIAL.
Correction: I meant "Primary Key" in the last sentence of the post, not foreign key. Sorry.
Just what I needed.
Was scratching my head over how to relate Company, Person and Job Title. A Person can have a number of jobs, a company a number of people and a Person/Company combination can have a job title.
I added an additional model called EmploymentContract which linked the two tables and carried a job title attribute. Bingo
Thank you for the Article, Felix.
However it is still unlcear to me how to populate extra HABTM fields (useing the new "with"-associations).
How would you save data e.g. into the "created" and "updated" fields in your memberships model if you would use HABTM associations. How does the array has to look like?
To save HABTM you would normally just call
$this->data['Group']['Group'] = array($this->Group->id);
Then call $this->User->save($this->data);
There is a example here http://www.cakephp.org/files/OCPHP.pdf
Is Post model (Post HABTM TAG with TaggedPost; additional field is "date":
function beforeSave() {
if(!empty($this->data[‘Tag’])) {
$this->TaggedPost->save($this->data[‘Tag’]);
}
}
How exactly would the array $this->data[‘Tag’] look like?
Could you give an example?
TIA,
Alex
Alex: You save and manage your HABTM data with its own model. So instead of trying to save your Group with the User model, use the Group model. Same goes for the Tag instead of TaggedPost. HTC
Felix: DOH! Bingo! Thx a lot.
I always tried to follow the docs which explains how to save HABTM:
"...populate $this->data[‘OtherModel’][‘OtherModel’] with an array containing the ID (or IDs) of the records you want to associate to, and save the current model, making sure that $this->data[‘ThisModel’][‘id’] is in place. ..."
[...] Modeling relationships in CakePHP (faking Rails ThroughAssociation) [...]
This is such a common situation in any real application, it really confounds any real developer how the TOYBOYS at cakephp have never dealt/documented it. Total embarrasment.
@Chewbaca6000: Send me an email to felix@thinkingphp.org so we can get the necessary information added to the manual and to show you some of the commitment and energy that exists in this community ; ).
I do not believe this
Hi, I'm a little bit confused when it comes to creating the physical tables. I understand that we can set associations from CakePHP.
Can I do this?
CREATE TABLE IF NOT EXISTS `jobDurations_posts` (
`post_id` INT UNSIGNED NOT NULL ,
`jobDuration_id` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`post_id`, `jobDuration_id`) ,
INDEX fk_posts_has_jobDurations_posts (`post_id` ASC) ,
INDEX fk_posts_has_jobDurations_jobDurations (`jobDuration_id` ASC) ,
CONSTRAINT `fk_posts_has_jobDurations_posts`
FOREIGN KEY (`post_id` )
REFERENCES `posts` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_posts_has_jobDurations_jobDurations`
FOREIGN KEY (`jobDuration_id` )
REFERENCES `jobDurations` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION);
Or should I use this instead?
CREATE TABLE IF NOT EXISTS `jobDurations_posts` (
`post_id` INT UNSIGNED NOT NULL ,
`jobDuration_id` INT UNSIGNED NOT NULL ,
PRIMARY KEY (`post_id`, `jobDuration_id`) );
thanks for the post
Hi guys!
I've a question about HABTM associations.
I've 3 tables:
- articles
- characteristics
- articles_characteristics
"articles_characteristics" is a table that I've created for join articles and characteristics.
I need that in the table characteristics I can add a new column called "value".
When inserting a new article, my purpose is to compile the table "articles_characteristics" with this occourrency:
acticles_id | characteristics_id | value
For each article, I've a set of characteristics by insert.
es:
article: id:1, type:car
char_0: color -> white
char_1: engine -> electric
char_2: traction -> 4x4
this is the structure of the tables involved:
articles:
id | type
------------------
1 | car
characteristics: (are stored the generic characteristics assignable to articles)
id | description
-------------------------
1 | color
2 | engine
3 | traction
At the end of the insert into the table "articles_characteristics", I'd like have this situation:
articles:
id | type
----------------------------------
0 | ball
1 | car < --(last inserted)
articles_characteristics:
articles_id | chacteristics_id | value
-----------------------------------------------------------
1 | 1 | white
1 | 2 | electric
1 | 3 | 4x4
Data are inserted in unique session, as multiple rows (a lot of characteristics for each article).
The question is: is cakephp able to insert data in the table articles_characteristics how do I need by HABTM associations?
Thanks in advance!!
I have 2 models with habtm to each other. If I edit one item in any model (i.e. name field), when saving cakephp deletes all associations to other model in join table. weird...
I can workaround this, retrieving associations data and putting it into $this->data, but does it make sense, if I only want to edit details of item?
I'd go with approach in this article, rather than using habtm.
uh well, had an input in view which caused all the grief ) have to blame myself.
another question then - what if I want to edit only associations between models? I have found 2 functions at bakery, addAssoc and deleteAssoc, but maybe cakePhp supports it at the core and I just can't find? say, I have id's of items in both models, and I want to associate them, but without creating model for join table...
What is the syntax for multiple hasandbelongstomany relationships?
A user has multiple interests
and
the same user has multiple committees
and
the same user has multiple skills
What does the var $hasandbelongstomany array( ... ); look like in the user model?
What is the syntax for multiple hasandbelongstomany relationships?
I figured it out!
var $hasAndBelongsToMany =array('Interest' =>
array('className' => 'Interest', 'joinTable' => 'interests_users',
'foreignKey' => 'user_user_id',
'associationForeignKey' => 'interest_id',
'unique' => true,
'conditions' => '',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'finderQuery' => '',
'deleteQuery' => '',
'insertQuery' => '' ),
'Committee' =>
array('className' => 'Committee',
'joinTable' => 'committees_users',
'foreignKey' => 'user_user_id',
'associationForeignKey' => 'committee_id',
'unique' => true,
'conditions' => '',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'finderQuery' => '',
'deleteQuery' => '',
'insertQuery' => '' ),
'Skill' =>
array('className' => 'Skill',
'joinTable' => 'skills_users',
'foreignKey' => 'user_user_id',
'associationForeignKey' => 'officejob_id',
'unique' => true,
'conditions' => '',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'finderQuery' => '',
'deleteQuery' => '',
'insertQuery' => '' )
);
Is this current? Or is the "with" parameter good enough by now?
"I didn't add any validation, nor a check for forbidding double Memberships, but this would be easy." < - Ok, tell me ;)
I am trying to forbid double membership, can you put me on a lead how to do that ?
How can i use the Composite Key ?
This post is too old. We do not allow comments here anymore in order to fight spam. If you have real feedback or questions for the post, please contact us.
Nifty, this might come in handy for my next project. Keep them coming Felix! :)