Sep192007

Modelizing HABTM join tables in CakePHP 1.2: with and auto-with models

One of CakePHP 1.2 coolest features are known as HABTM with models, which are particularly useful when you have a hasAndBelongsToMany binding between two models that may contain extra information (i.e: table fields) attached to the binding. They are also extremely useful when you need to make some model operations to the join table and feel too lazy to modelize it yourself, or when you have modelized the join table and need to tell CakePHP to use your own model as the join model.

Let’s start with a practical example, the classic Article -> hasAndBelongsToMany -> Tag binding. Set up the articles table in any way you like, and so the tags table. Now create the join table as follows:

CREATE TABLE `articles_tags`(
	`id` INT NOT NULL AUTO_INCREMENT,
	`article_id` INT NOT NULL,
	`tag_id` INT NOT NULL,
	PRIMARY KEY(`id`)
);

Yeah, I know, so much for extra fields: just the id. However feel free to add any extra fields yourself and the example here will still be valid. In fact, if there are more than two fields in the join table then CakePHP will assume that you are normally interested on those extra fields.

Assume you have set up some articles, some tags, and have joined articles with some tags. Let’s do a classic findAll on the Article model:

$records = $this->Article->findAll();
debug($records);

Bearing in mind the difference between the data you’ve set up, and the one we have on this example, the result may be something like this:

Array
(
    [0] => Array
        (
            [Article] => Array
                (
                    [id] => 1
                    [title] => My First Article
                    [body] => Hi there!
                    [created] => 2007-03-19 15:01:16
                    [modified] => 2007-03-19 15:31:06
                )
            [Tag] => Array
                (
                    [0] => Array
                        (
                            [id] => 1
                            [name] => tag1
                            [created] => 2007-09-19 03:04:33
                            [modified] => 2007-09-19 03:04:33
                            [ArticlesTag] => Array
                                (
                                    [id] => 1
                                    [article_id] => 1
                                    [tag_id] => 1
                                )

                        )
                    [1] => Array
                        (
                            [id] => 3
                            [name] => tag3
                            [created] => 2007-09-19 03:04:33
                            [modified] => 2007-09-19 03:04:33
                            [ArticlesTag] => Array
                                (
                                    [id] => 2
                                    [article_id] => 1
                                    [tag_id] => 3
                                )
                        )
                )
        )
    [1] => Array
        (
            [Article] => Array
                (
                    [id] => 5
                    [title] => My Second Article
                    [body] => Hi there, new article!
                    [created] => 2007-03-19 15:06:02
                    [modified] => 2007-03-19 15:06:02
                )
            [Tag] => Array
                (
                    [0] => Array
                        (
                            [id] => 2
                            [name] => tag2
                            [created] => 2007-09-19 03:04:33
                            [modified] => 2007-09-19 03:04:33
                            [ArticlesTag] => Array
                                (
                                    [id] => 3
                                    [article_id] => 5
                                    [tag_id] => 2
                                )
                        )
                    [1] => Array
                        (
                            [id] => 3
                            [name] => tag3
                            [created] => 2007-09-19 03:04:33
                            [modified] => 2007-09-19 03:04:33
                            [ArticlesTag] => Array
                                (
                                    [id] => 4
                                    [article_id] => 5
                                    [tag_id] => 3
                                )

                        )
                )
        )
)

Ok now you may be asking: what is up with that ArticlesTag array that is showing up there? Well, you may have also guessed it: that’s the information coming from the HABTM join table. Let’s take a closer look at the SQL statements generated by CakePHP that yielded these results:

SELECT `Article`.`id`, `Article`.`title`, `Article`.`body`, `Article`.`created`, `Article`.`modified` FROM `articles` AS `Article` WHERE 1 = 1;

SELECT `Tag`.`id`, `Tag`.`name`, `Tag`.`created`, `Tag`.`modified`, `ArticlesTag`.`id`, `ArticlesTag`.`article_id`, `ArticlesTag`.`tag_id` FROM `tags` AS `Tag` JOIN `articles_tags` AS `ArticlesTag` ON (`ArticlesTag`.`article_id` IN (1, 5) AND `ArticlesTag`.`tag_id` = `Tag`.`id`)

The first query does the normal Article search, this is where your findAll conditions would be inserted (so don’t think yet that you can attach conditions based on your join model). The next query finds all the tags linked through the join table to the set of Article IDs obtained during the first query. Yes, CakePHP 1.1 users: this query has also been optimized (CakePHP 1.1 runs one finder query per resulting row).

What if you want to only get those articles that are linked to a specific tag? You need then to query the join table. This is where auto-with tables can also help you out, since the join table has been auto-modelized for you (hence the auto part of the name). Try this:

$joinRecords = $this->Article->ArticlesTag->findAll(array('ArticlesTag.tag_id' => 3));
debug($joinRecords);

We now get:

Array
(
    [0] => Array
        (
            [ArticlesTag] => Array
                (
                    [id] => 2
                    [article_id] => 1
                    [tag_id] => 3
                )
        )
    [1] => Array
        (
            [ArticlesTag] => Array
                (
                    [id] => 4
                    [article_id] => 5
                    [tag_id] => 3
                )
        )
)

So you can either play around and get the Article IDs using Set extract:

$ids = Set::extract($joinRecords, '{n}.ArticlesTag.article_id');
debug($ids);

After which $ids look like:

Array
(
    [0] => 1
    [1] => 5
)

Or use bindModel to join the Article and Tag models to the join table, and then running the query:

$this->Article->ArticlesTag->bindModel(array('belongsTo' => array('Article', 'Tag')));
$joinRecords = $this->Article->ArticlesTag->findAll(array('ArticlesTag.tag_id' => 3));
debug($joinRecords);

The records now look like:

Array
(
    [0] => Array
        (
            [ArticlesTag] => Array
                (
                    [id] => 2
                    [article_id] => 1
                    [tag_id] => 3
                )
            [Article] => Array
                (
                    [id] => 1
                    [title] => My First Article
                    [body] => Hi there!
                    [created] => 2007-03-19 15:01:16
                    [modified] => 2007-03-19 15:31:06
                )
            [Tag] => Array
                (
                    [id] => 3
                    [name] => tag3
                    [created] => 2007-09-19 03:04:33
                    [modified] => 2007-09-19 03:04:33
                )
        )
    [1] => Array
        (
            [ArticlesTag] => Array
                (
                    [id] => 4
                    [article_id] => 5
                    [tag_id] => 3
                )
            [Article] => Array
                (
                    [id] => 5
                    [title] => My Second Article
                    [body] => Hi there, new article!
                    [created] => 2007-03-19 15:06:02
                    [modified] => 2007-03-19 15:06:02
                )
            [Tag] => Array
                (
                    [id] => 3
                    [name] => tag3
                    [created] => 2007-09-19 03:04:33
                    [modified] => 2007-09-19 03:04:33
                )
        )
)

And if you need more information associated with each Article you can of course play with the $recursive level of the findAll query.

What if for some reason you have already modelized your join table? Let’s say you created a model called ArticlesTag (located on app/models/articles_tag.php) that looks like this:

class ArticlesTag extends AppModel {
	var $name = 'ArticlesTag';
	var $belongsTo = array('Article', 'Tag');
}

How can we now tell CakePHP that the join table should be modelized using our model? Let’s go back to the Article model definition and make a small change where we define the with model:

class Article extends AppModel {
	var $name = 'Article';
	var $hasAndBelongsToMany = array('Tag' => array('with' => 'ArticlesTag'));
}

Remember how we had to bind the Article and Tag model to the auto-with model set up with CakePHP? Looking at our own version of the join model that shouldn’t be necessary anymore, so let’s try:

$joinRecords = $this->Article->ArticlesTag->findAll(array('ArticlesTag.tag_id' => 3));
debug($joinRecords);

You can now see that the yielded results are the same as those obtained when the auto-with model was previously binded.

Anyway, that’s a short introduction to CakePHP 1.2 with models. As usual with CakePHP, what seems to be a small addition gives us a new set of exciting possibilities.



Leave a Comment

23 Comments to "Modelizing HABTM join tables in CakePHP 1.2: with and auto-with models"

  1. Sep212007 at 6:44 am

    |Luke [Visitor] wrote:

    hey Mariano, this is a great tutorial! I have been using this ‘with’ thing for a while but didn’t grasp the full coolness — this is a very clear exposition of the methods and features, though!

    hats off to the cake devs for this :)

  2. Oct032007 at 4:14 am

    Tom [Visitor] wrote:

    There appear to be 3 types of join table in my data model..

    1. Pure join – table comprised of two or more foreign keys only and no other attribues (column headers)

    2. Join as parent – table comprised of two or more foreign keys only and no other attribues… wherein he table is also the parent to another table

    3. Join with attributes table comprised of two or more foreign keys and several other attribues

    Can I just make a rule, that their should be a cake model in cases 2 and 3 but in case 1 simply use the hasandbelongstomany syntax in th eparent models to imply the existence of the pure join?

  3. Nov042007 at 5:03 am

    John [Visitor] wrote:

    I just wanted to thank you for your example. Modelizing the join table makes finding so much cleaner and easier.

  4. Dec062007 at 10:34 pm

    goed gezien [Visitor] wrote:

    thanks Mariano,
    this tutorial helped me alot and saved me tons of time :)

    take care,
    goed gezien

  5. Feb272008 at 11:42 am

    Anders [Visitor] wrote:

    Hi there,

    awesome tutorial. The with association is the main reason I switched from 1.1 to 1.2, even if it is still beta.
    There should be a link in the temporary documentation to this post, it’s hard to find it via google, because you have to know the keywords you are looking for.

  6. Mar132008 at 4:16 am

    Dan [Visitor] wrote:

    Thanks for clarifying. Your writings are extremely useful to Cake users.

    Maybe you could answer this question?

    Say you have:

    Categories which HABTM Events which hasMany Occurrences. Occurrence has the field date_time.

    Is there a way to apply the modelizing technique to retrieve Categories bound to only those Events whose Occurrences have a DATE(date_time) = CURDATE() ?

    Again, much thanks on your contributions.

  7. Mar282008 at 2:23 pm

    Nick B [Visitor] wrote:

    Good article.. but what about if you have more than one HABTM association that you want to filter on? Eg Posts that have tags and exist in many languages…

    eg finding all posts in English with the ‘cake’ tag… (without all the queries, and then redoing the pagination..)

    Doesn’t seem to be any easy way of doing it as you have to choose one auto-with model to call the findAll() from. If anyone has any ideas..

    :(

  8. Apr062008 at 10:33 am

    reality [Visitor] wrote:

    This all looks very great but can be used only when you don’t need deeper levels of associations. Ok, what if i need (that is not uncommon) the author of the article ? If, as you say, i use recursion and my database model is a bit more complex (my article has also all kinds of associations) i get (50 and more) queries per click ! :/ I must unbind everything and add a bunch of other stuff… the amount of code baloons ! And most importantly mvc thing just collapses because i have to write bunch of things in controller to dynamically redesign the model, so im afraid habtm relations are far from perfect in cake models…

  9. Apr062008 at 11:47 am

    mariano.iglesias [Member] wrote:

    @reality: do not assume what you know of CakePHP to be all there is to it. Whenever you think you have a problem with an answer, it has most likely already been addressed in the Cake world.

    Next time, instead of jumping to wrong conclusions try to search internet a lot. You could, for example, find the Bindable behavior, which deals exactly with what you are complaining.

  10. Apr062008 at 11:54 am

    reality [Visitor] wrote:

    What about SAVING Article with associations to Tags ? :) Even using -with-, cake cleans your attributes in habtm relation table…

  11. Apr062008 at 11:57 am

    mariano.iglesias [Member] wrote:

    @reality: go through the google group, this is not the place to ask those questions. Again, your latest question has already been answered a GAZILLION times. Search before you ask.

  12. Apr062008 at 12:08 pm

    reality [Visitor] wrote:

    @mariano i am not complaining. I have found this bindable behaviour before. What remains true is that it is written by a user(you possibly ? ;) it is not implemented in cake as a part of a habtm strategy. All these ‘add-ons’ are great, but usually when you solve one problem they create two more additional ones… Apparently i need a bunch of stuff for every problem, its not cakey…

  13. Apr062008 at 12:16 pm

    mariano.iglesias [Member] wrote:

    @reality: I trust the Cake core and community, which have been in this for years, more than anyone else to decide what’s cakey and what isn’t.

    You are right, I implemented Bindable, because I understand a framework is not about providing solutions for every possible given problem, but to solve 80% of them out of the box, and lay out the playfield for you to easily solve the other 20%. And that’s exactly what CakePHP does.

  14. Apr142008 at 1:01 am

    paydjo [Visitor] wrote:

    SELECT `Article`.`id`, `Article`.`title`, `Article`.`body`, `Article`.`created`, `Article`.`modified` FROM `articles` AS `Article` WHERE 1 = 1;

    SELECT `Tag`.`id`, `Tag`.`name`, `Tag`.`created`, `Tag`.`modified`, `ArticlesTag`.`id`, `ArticlesTag`.`article_id`, `ArticlesTag`.`tag_id` FROM `tags` AS `Tag` JOIN `articles_tags` AS `ArticlesTag` ON (`ArticlesTag`.`article_id` IN (1, 5) AND `ArticlesTag`.`tag_id` = `Tag`.`id`)

    how to change the 2nd query with LEFT JOIN

    SELECT `Tag`.`id`, `Tag`.`name`, `Tag`.`created`, `Tag`.`modified`, `ArticlesTag`.`id`, `ArticlesTag`.`article_id`, `ArticlesTag`.`tag_id` FROM `tags` AS `Tag` LEFT JOIN `articles_tags` AS `ArticlesTag` ON (`ArticlesTag`.`article_id` IN (1, 5) AND `ArticlesTag`.`tag_id` = `Tag`.`id`)

  15. Jul112008 at 4:49 am

    xumix [Visitor] wrote:

    @mariano.iglesias: about saving additional data
    oh, rly it has been discussed? look at the model code and this
    http://groups.google.com/group/cake-php/browse_thread/thread/bf91d27adf090826?tvc=2

  16. Dec262008 at 6:45 pm

    Paul [Visitor] wrote:

    User Model
    var $hasAndBelongsToMany = array(‘event’,array(‘with’=>’EventsUser’));

    Event Model
    var $hasAndBelongsToMany = array(‘user’,array(‘with’=>’EventsUser’));

    Join Model
    class EventsUser extends AppModel {
    var $name = ‘EventsUser’;
    var $useTable = “events_users”;
    var $belongsTo = array(‘Event’, ‘User’);
    }

    I keep getting “Table arrays for Model Array” Errors. Whats wrong?

  17. Jan152009 at 1:50 pm

    nico.m [Visitor] wrote:

    Good article, I spent hours looking for this.

    But now I’ll keep on looking for this same behavior using $this->paginate()… I have a lot of Posts with the same Tag and don’t want to have enormous pages…

    any suggestion?

  18. Jan262009 at 8:46 am

    picca [Visitor] wrote:

    Man, you are totally life saver. At least you saved me hours of typing stupid, redundant code. Thank you.

  19. Feb042009 at 3:30 pm

    Christian [Visitor] wrote:

    Mariano, la verdad, excelente como siempre tus posts.
    Ahora, tengo un tema (q antes no pasaba, deje cake un tiempo x un proyecto y volvi para la 1.2), los resultados se agrupan… es decir, si tengo 2 veces linkeado el tag 3, en el result solo me aparece una vez. Si bien en el caso de tags esto es practico, para lo que yo estaba usando el join necesitaba que aparezcan todos. Sabes si esto es configurable? Desde ya gracias si me podes tirar una mano.
    ehm… keep it up bro!

  20. Apr072009 at 4:29 pm

    Jordi [Visitor] wrote:

    Mariano, you’re the best!

    Thanks for making my life easier with your explanation.

  21. Apr242009 at 6:18 am

    CChaos [Visitor] wrote:

    FINALLLY someone who actually EXPLAINS what the ‘with’ key is all about.

    Being a newb, I was banging my head for the past 5 hours on this. And of course there’s actually as simple of an answer as this.

    THANK YOU!

  22. Feb192010 at 3:42 am

    Hammy wrote:

    I could kiss you! This has been killing me.

  23. Jan132012 at 5:05 pm

    Juarez P. A. Filho wrote:

    Just amazing! Thanks for this awesome explanation.

 
Powered by Wordpress and MySQL. Clauz's design for by Cricava