Habtamable behavior

The basic idea behind this behavior is that you (well, once in a while) need to save two HABTM models at the same time or search across both models, which are involved in the HABTM relationship.

By default cake does a nice job of saving HABTM models and their relation, but you need to know the ID(s) of at least one of the models that’s involved.
Also, you cannot supply HABTM model conditions, because cake will not build a JOIN to apply the conditions to both models properly.

So, again, this behavior attempts to solve some common issues with HABTM associations.

To give an example we could have a Location Model, which hasAndBelongsToMany Address.
In the Location Model we can have some arbitrary info, such as location name, location value, location capacity, etc., etc.
In the Address Model, of course, we’ll store the relevant address.

The usage is quite simple…

In the Location Model add:

public $hasAndBelongsToMany = array(‘Address’);
public $actsAs = array(‘Habtamable’);

The behavior will do a look-up in the addresses table, to see if a given address already exists.
It will also take care of validating the models.
Once the models are saved (using this behavior or not), you have an established HABTM relationship in the database. Now, using this behavior you can do a search by supplying the conditions of both models. See the updates below for more info…

Silly me, I’ve overlooked an important issue…
As of today’s writing (9/26/2009), the behavior will validate the models one at a time, which obviously looks ugly (and super user-unfriendly) when it comes to UI.
I’m working on fixing this at the moment…
This feature is now working with the latest update (maybe a bit hackish, however).

Grab the behavior here:
http://github.com/teknoid/cakephp-habtamable-behavior

Update (10/8/2009):
You can now pass the following settings:
joinType
fieldsToSkip
habtmModel
See behavior’s README for more details

Update (10/8/2009):
Validates two HABTM models at once, based on their own validation rules.
Will only do the fake re-bind, if the HABTM model is present in the conditions.

Update (10/7/2009):
It also dawned on me that searching across HABTM models, has always been a pain. So the latest revision published on the above date, will take care of that for you.

$this->Location->find('all', array('conditions' => array('Location.is_active' => 1, 'Address.city' => 'Miami')));

The behavior will do a “fake” hasOne bind, as outlined in some other posts here, and build a JOIN to perform the search.
For now I’m only supporting INNER JOIN, but after I get done with validation the behavior will accept a few additional settings to improve the searching and joining.
(And ultimately make it work with pagination).

P.S. Any feedback is greatly appreciated.

  • zyro

    hi,

    first of all, i only had a look at the find-related parts of the behaviour. so ive got no idea if the points ill mention have any impact on the validate/save parts of the behaviour.

    1) line 57 (checkHabtmConditions())
    the return value will be false in case strpos returns 0 –> wrong… idea:
    return strpos($searchableConditions, $this->settings[$Model->alias]['habtmModel']) === FALSE ? FALSE : TRUE;

    2) line 63-78 (changeBind())
    i think the join conditions got messed up here.
    key of the first condition should be value of the second condition and vince versa.

    3) general issue: multiple habtm relations
    it is assumed, i think that a model has a maximum of 1 habtm relation. this causes massive problems if u plan to do a search with conditions including a habtm related model that is not the first one in the $Model->hasAndBelongsToMany array.

    these were my observations so far – really a nice idea to stuff the join trickery things (older blog posts etc.) up in a behaviour. But without the mentioned points being improved/fixed the behaviour is just not usable – again i can just comment on the find realted parts of the behaviour. didnt test any habtm saving.

    greets, zyro

    • zyro

      hi again,

      found another problem i think:
      line 57 (checkHabtmConditions()):
      grabbing the keys of the flattened array works as long as the conditions are plain meaning 1 level deep. for example a condition like:
      array(‘or’ => array(‘condi1′ => blah, ‘condi2′ => bleh))
      causes the strpos of checkHabtmConditions() to match against the string ‘or’ :(

      zyro

  • http://teknoid.wordpress.com teknoid

    @zyro

    Thanks for all your feedback, I’ll updating with a new version soon. (Including test cases).
    Some of these issues are already addressed in my development.

  • jayarjo

    Is it only me having having the feeling that all those abstractions like HABTM create new problems instead of those they try to “resolve”. Are those new problems really less time consuming to someone? :(

  • http://teknoid.wordpress.com teknoid

    @jayarjo

    As mentioned, habtm relations are common, but you should not use habtm to solve every problem just because it’s available.
    It’s up to you as a developer to come up with the best design for your application.

    … and the short answer is “no”, habtm can be quite handy if used correctly.

  • Sitex

    Doesn’t work for me. Waiting for the new version.

  • http://teknoid.wordpress.com teknoid

    @sitex

    Thanks for trying it out, but it would be a little more helpful, if you don’t mind explaining what “doesn’t work”. ;)

    • Sitex

      There are two models. Reviews HABTM Genres.

      I try to get reviews with a certain genre:
      $reviews = $this->paginate( ‘Review’, array(‘Genre.title’=>’thriller’) );

      But it said: “SQL Error: 1054: Unknown column ‘Genre.title’ in ‘where clause'”

  • http://teknoid.wordpress.com teknoid

    @sitex

    Thanks for quick responses. pagination should produce the two queries, one is for count, the other one fetches the results. Do you see a join built for the count (and not for the second one)?…. if so it’s a quick change to get the second query to bind properly and i’ll have it ready in 47 – 158 minutes.

  • http://ericboehs.com Eric Boehs

    Thanks for habtamable. It’s made my current project at work much easier.

    I did however need some more features. So I added them :).

    http://github.com/ericboehs/cakephp-habtamable-behavior

    This allows multiple models to be associated with the ‘parent’ model all in one save(). Read the readme for an example/explanation.

    I hope to see my additions in the next version of habtamable. ;)

    Thanks again.

  • http://teknoid.wordpress.com teknoid

    @Eric Boehs

    Very cool. I’ll be sure to check out your updates.
    I’m going to back to working on it next week or so, so hopefully with a little help it’ll grow into something decent ;)

  • emptywalls

    Thank you so much for this, I’ll be trying it out tonight when I get home. I used your ‘dealing with calculated fields’ behavior with great success, this habtm behavior looks like exactly what i needed as well.

  • Victor

    Hi, I’m trying to work in the behaviuor you wrote, but i’m running into a “small” (i know what’s wrong, but don’t know why) problem:
    So I have:
    class Tag extends AppModel {
    var $name = ‘Tag';
    var $hasAndBelongsToMany = array(
    ‘Picture’ => array(
    ‘className’ => ‘Picture’,
    ‘joinTable’ => ‘pictures_tags’,
    ‘foreignKey’ => ‘tag_id’,
    ‘associationForeignKey’ => ‘picture_id’,
    ‘unique’ => false
    )
    );
    var $actsAs = array(‘Habtamable’);
    }

    then

    class Picture extends AppModel {
    var $name = ‘Picture';
    var $hasAndBelongsToMany = array(
    ‘Tag’ => array(
    ‘className’ => ‘Tag’,
    ‘joinTable’ => ‘pictures_tags’,
    ‘foreignKey’ => ‘picture_id’,
    ‘associationForeignKey’ => ‘tag_id’,
    ‘unique’ => false
    )
    );
    }

    and surely all the needed tables

    Then I try to search over this association
    $this->Picture->find(‘all’, array(
    ‘conditions’=>array(
    ‘Picture.online’=>’1′,
    ‘Tag.tag’=>’van’
    )
    ));
    I get nothing but I see the query which if simplified manually just in here (I select all the fields) looks as the following:

    SELECT * FROM `pictures` AS `Picture` INNER JOIN `pictures_tags` AS `PicturesTag` ON (`PicturesTag`.`tag_id` = `Picture`.`id`) INNER JOIN `tags` AS `Tag` ON (`Tag`.`id` = `PicturesTag`.`picture_id`) WHERE `Picture`.`online` = 0 AND `Tag`.`tag` = ‘van’

    And as you see the tables are joined on wrong fields (`PicturesTag`.`tag_id` = `Picture`.`id`) and (`Tag`.`id` = `PicturesTag`.`picture_id`).

    why is that? am I doing something wrong?

    Thanks for the answer in advance

  • Victor

    I figured out what was the issue, but this is weird (at least for me):

    I changed
    var $name = ‘Picture';
    var $hasAndBelongsToMany = array(
    ‘Tag’ => array(
    ‘className’ => ‘Tag’,
    ‘joinTable’ => ‘pictures_tags’,
    ‘foreignKey’ => ‘tag_id’, ‘picture_id’, false
    )
    );

    but in the book of CakePHP (http://book.cakephp.org/view/83/hasAndBelongsToMany-HABTM), it says that foreign_key must be the same as the name of the class, in my case this is ‘Picture’, but not ‘Tag’.

    Any comments on that? is there some weird stuff goign around?

    also if someone want to pose such a conditioning on tags as array(‘tag1′, ‘tag2′), the behaviour won’t work.

    and finally, here (http://teknoid.wordpress.com/2008/08/06/habtm-and-join-trickery-with-cakephp/) you wrote that that (super-)nice feature was rolled into the bahaviour, how do i use it?

  • Victor

    sorry for the spaming, but the previous comment did not come out nicely, so I rewrite how changed class look like now:
    class Picture extends AppModel {
    var $name = ‘Picture';
    var $hasAndBelongsToMany = array(
    ‘Tag’ => array(
    ‘className’ => ‘Tag’,
    ‘joinTable’ => ‘pictures_tags’,
    ‘foreignKey’ => ‘tag_id’, —— changed from ‘picture_id’
    ‘associationForeignKey’ => ‘picture_id’, —— changed from ‘tag_id’
    ‘unique’ => false
    )
    );
    }

  • Victor

    “also if someone want to pose such a conditioning on tags as array(‘tag1′, ‘tag2′), the behaviour won’t work.”

    that was wrong, sorry.

  • http://teknoid.wordpress.com teknoid

    @Victor

    With all the comments I lost of track of what the actual problem is…
    The examples I have in the readme is pretty much all this behavior does at the moment.

    The searching is still quirky at best, but it works for two models at least I’ve not run into any trouble. Also for two models the saving should work without a problem.

    Be sure to setup your associations correctly. As long as that is done, it should work fine. For searching all it really does is a fake hasOne binding to force the JOIN’s as explained in the other posts.

  • Victor

    ok

    I’ve got this working
    http://teknoid.wordpress.com/2008/08/06/habtm-and-join-trickery-with-cakephp/
    I asked before.

    I’m very sorry for posting that many shit, erase it please.

    there is only one issue with the correct name conventions for the foreignKey and associationForeignKey,

  • Victor

    the actual problem is in my very first post and the beginning of the second, where I explained why i ran into a problem and pointed out that it is unclear which must be foreignKey and wich associationForeignKey. The way it is explained in cake it doesn’t work (i checked twice), but when these keys are swapped – it works.

  • http://www.gulosolutions.com Zach Wilson

    Man, this is terrific. I’m always fumbling over HABTM behaviors that have multiple relationships.

    No problems whatsoever using this behavior.

    Teknoid rules!

  • http://teknoid.wordpress.com teknoid

    @Zach Wilson

    Good to hear.

  • http://www.gulosolutions.com Zach Wilson

    @teknoid Should this model association be working with this behavior?

    http://bin.cakephp.org/view/1252931078

  • http://teknoid.wordpress.com teknoid

    @Zach Wilson

    Possibly with saveAll().
    I need to look at the code, but it’s rather late :)

  • http://www.gulosolutions.com Zach Wilson

    Thanks, but I nixed this implementation all together. It wasn’t doing exactly what I needed after all.

    The good thing is that I definitely see value in it down the road. Thanks for your response and posting it.

  • http://www.asecondsystem.com Will

    Hi,

    I’m struggling to get this working. I have a habtm relationship like this:

    ‘Article’ => array(
    ‘className’ => ‘Content’,
    ‘joinTable’ => ‘articles_editions’,
    ‘foreignKey’ => ‘edition_id’,
    ‘associationForeignKey’ => ‘article_id’,
    ‘unique’ => true,
    ‘order’ => ‘ArticlesEdition.id’,
    )
    in my content model. Its relating content of type article to content of type edition. Is it then possible to search for articles that belong to a paticular edition? or articles that belong to an edition whose publication date is in the past?

    Should this behaviour work for this self referntial situation?

    Thanks for any help, im completely lost!

    Will.

  • http://teknoid.wordpress.com teknoid

    @Will

    What have your tried?
    What was the SQL produced?

    If you know edition ID, it would be much easier to just search the join table model to get all the relevant articles. For dates simply do a find(‘all’) on the editions table to get all the relevant articles.

    • http://www.asecondsystem.com Will

      Hi teknoid, thanks for getting back to me.

      The main issue is searching for a phrase in the articles, and excluding those that don’t belong to a edition that has been released. so in that case i don’t know the edition id. The ultimate goal is a paginated list of articles that belong to released editions.

      Ive tried adding the Habtamable behaviour, and searching with things like

      $articles = $this->Content->find(‘all’, array(‘conditions’ => array(‘Content.title like’ => “%$term%”, ‘Edition.title’ => ‘issue 6′)));

      which creates sql:
      Query: SELECT `Content`.`id`, `Content`.`title` FROM `contents` AS `Content` WHERE `Content`.`title` like ‘%free%’ AND `Edition`.`published` = ‘issue 6′

      so no joins in sight.

      When I look at the data when fetching an edition, the articles that belong to it are in a Article key. but not the other way round. ie, when i look at a fetched article, there is no edition key. bit of a worry.

      Because of that, i tried sarching using the edition first and trying to look at associated article titles like this:

      $articles = $this->Content->find(‘all’, array(‘fields’=> array(‘Content.id’, ‘Content.title’) ,’conditions’ => array(‘Content.published_date “2010-02-20″, ‘Article.title =’ =>”Free” )));

      but that just gets a 1054: Unknown column ‘Article.title’ in ‘where clause error. :(

      Clearly – im lost.

  • http://www.asecondsystem.com will

    me again.

    Should this work with cakephp1.3? i get a complete meltdown when i iniclude it in the acts as array. eek.

    Warning (2): Illegal offset type in isset or empty [CORE/cake/libs/cache.php, line 531]
    Notice (8): Trying to get property of non-object [CORE/cake/libs/model/datasourcesDboSource::read() – CORE/cake/libs/model/datasources/dbo_source.php, line 795
    Notice (8): Undefined index: with [CORE/cake/libs/model/datasources/dbo_source.php, line 964]
    Notice (8): Trying to get property of non-object [CORE/cake/libs/model/model.php, line Notice (8): Trying to get property of non-object [CORE/cake/libs/model/datasources/dbo_source.php, line 823] 112
    Fatal Error (256): ConnectionManager::getDataSource – Non-existent data source [CORE/cake/libs/model/connection_manager.php, line 102]

  • http://teknoid.wordpress.com teknoid

    @will

    Hi, this seems overly strange.
    The behavior was actually built on 1.3.
    If anything, it would break with 1.2 .

  • http://www.pixelstudios.es Victor

    Hello Teknoid! im looking for a solution like this one but applied to a hasmany relationship, in my case i have:

    Product hasMany ProductStock
    ProductStock belongs to Product

    I want to be able to search like this:
    $this->Product->find(‘all’, array(‘conditions’ => array(‘ProductStock.stock_number’ => 0)));

    Is this possible using this behavior? I’ve tried already but i get this error:
    SQL Error: 1054: Unknown column ‘ProductStock.stock_number’ in ‘where clause’

    Let me know if you can, thanks! ;)

  • luglio7

    Mmmh, this Behavior seems not working in a plugin … or i’m a stupid :)

  • http://teknoid.wordpress.com teknoid

    @luglio7

    It is not intended to work as a plugin.

    • luglio7

      Ouch not… i tried to use it in a cakephp plugin (in the directory /app/plugin/administration/models/behaviors) but it seems not working.

    • luglio7

      Obviously “administration” is the name of my cakephp plugin :)

  • http://vivwebsolutions.com Adam Friedman

    Hey, great job on this. I found a pretty serious bug however. In the changeBind() method you swapped the hasOne join primary keys, so when I pr()’d it, I got a large array of your ‘spoof’ bindings, which included these bad joins:
    CategoriesStory.category_id = Story.id
    Category.id = CategoriesStory.story_id

    See how they’re swapped?

    Full pr():
    Array
    (
    [hasOne] => Array
    (
    [CategoriesStory] => Array
    (
    [foreignKey] =>
    [type] => INNER
    [conditions] => Array
    (
    [0] => CategoriesStory.story_id = Story.id
    )

    )

    [Category] => Array
    (
    [foreignKey] =>
    [type] => INNER
    [conditions] => Array
    (
    [0] => Category.id = CategoriesStory.category_id
    )

    )

    )

    )

    Fix:
    Replace the entire method with my update here:

    /**
    * Fake model bindings and construct a JOIN
    */
    private function changeBind(&$Model) {

    $Model->bindModel(array(
    ‘hasOne’ => array(
    $Model->hasAndBelongsToMany[$this->settings[$Model->alias]['habtmModel']]['with'] => array(

    ‘foreignKey’ => FALSE,
    ‘type’ => $this->settings[$Model->alias]['joinType'],
    ‘conditions’ => array(
    $Model->hasAndBelongsToMany[$this->settings[$Model->alias]['habtmModel']]['with'] . ‘.’ .$Model->hasAndBelongsToMany[$this->settings[$Model->alias]['habtmModel']]['foreignKey'] . ‘ = ‘ .$Model->alias . ‘.’ . $Model->primaryKey)
    ),
    $this->settings[$Model->alias]['habtmModel'] => array(
    ‘foreignKey’ => FALSE,
    ‘type’ => $this->settings[$Model->alias]['joinType'],
    ‘conditions’ => array(
    $this->settings[$Model->alias]['habtmModel'] . ‘.’ . $Model->{$this->settings[$Model->alias]['habtmModel']}->primaryKey . ‘ = ‘ . $Model->hasAndBelongsToMany[$this->settings[$Model->alias]['habtmModel']]['with'] . ‘.’ .
    $Model->hasAndBelongsToMany[$this->settings[$Model->alias]['habtmModel']]['associationForeignKey']
    )))));

    }

  • http://vivwebsolutions.com Adam Friedman

    Another bug, line 54 here — https://github.com/teknoid/cakephp-habtamable-behavior/blob/master/models/behaviors/habtamable.php:

    $searchableConditions = implode(‘.’, Set::flatten(array_keys($query['conditions'])));

    if I send in a condition on the HABTM foreign table that is ONLY in the array value (not in the key), then your check on line 54 does not detect it and throws a false negative, then your beautiful functionality stays silent.

    For ex:

    ‘conditions’ => array(
    ‘Category.id = 3′,
    )

    will NOT be detected, while

    ‘conditions’ => array(
    ‘Category.id’ => ‘3’,
    )

    will be detected.

    Arbitrary, hm?