Saving extra fields in the join table (for HABTM models)

An interesting question came up on IRC today, which essentially boils down to:
“How to save extra fields in the join table for HABTM models, while creating a new record for one of the involved models all at once?”

I’ve seen this question float around here and there, but do not recall any specific solutions…

So here, I propose one method which requires a temporary hasMany bind between the model which we are saving and the join table model, then using saveAll() to save the data.

Let’s take a look at an example, where we have the good ol’ Post HABTM Tag.

We are trying to save a new Post, for which we already know a Tag.id (a pretty standard HABTM save scenario), but now we also need to save an extra field ‘status’ into our join table.

//our data array
$this->data['Post']['title'] = 'Hello';
$this->data['Post']['post'] = 'My new post';
$this->data['PostsTag'][0]['tag_id'] = 15;
$this->data['PostsTag'][0]['status'] = 'disabled';

$this->Post->bindModel(array('hasMany'=>array('PostsTag')));
$this->Post->saveAll($this->data);

And that’s all there is to it, we’ve now saved our Post as well as our relation to Tag and the required extra field. Of course, in reality, your data will come from the form, but hopefully you get the gist of this idea.

  • Andreas

    There’s also a “with-association” in CakePHP to do this. I personally never used it so I’m not sure if it (still) works.
    gwoo mentioned it in a presentation: http://www.cakephp.org/files/OCPHP.pdf

  • teknoid

    @Andreas

    That’s exactly what we are using for this method. (i.e. the PostsTag model is our ‘with’ or the join table model, which is automagically created for you).

  • http://www.fianchetto.org luke

    hiya Teknoid!

    read MAriano Iglesias post on this matter – it is very nice one.
    Luke aka BoobyWomack

  • teknoid

    @luke

    I certainly have a while ago ;)

    … but it doesn’t talk about saving the data to the join table

  • http://www.pixelstudios.es Victor

    Hey Teknoid, im having problems with this particular situation (saving an extra field to a HABTM table), i cant get it to work, i’ve read some related articles about this but i just dont get the concept, would you mind if i send you and email with a detailed explanation?

    Mine is victor.nunez (at) pixelstudios (dot) es

  • teknoid

    @Victor

    Sorry, I don’t really have time to do support :)
    Please post your question at the google group or the IRC channel and I’m sure you’ll get a better/faster answer.

  • http://mark-story.com Mark Story

    As others mentioned using a ‘with’ relationship will give you a named model on the join table. If a class with that name exists it will be used, otherwise an AppModel instance will be constructed. You can obviously use this model just like any other model.

  • teknoid

    @Mark Story

    Sure, and I do utilize that ‘with’ model here. Is there another approach that will allow me to save extra fields into the join table together with another HABTM model? I haven’t seen one…

  • Pingback: Signets remarquables du 23/09/2008 au 27/09/2008 :: Cherry on the…

  • http://www.innovationsopen.com mzee.richo

    Im currently working on large applications , but really having problems with this same issue . Op we come up with a cool simpler solution . thanks guys

  • frank

    I tried your aproach and it works, except the field for the post.id isn’t saved in the PostsTag… any ideas where the error can be??

    this is my array:

    Array
    (
    [User] => Array
    (
    [username] => testcompany2
    [titel] => sdfsdf
    [vorname] => sdfsdf
    [nachname] => sdfsdf
    [passwort] => sdfds
    [email] => sfsdfsqs@daytwo.at
    [password] => 00c54af5751b71908fee6e35b41590ae2d6cd3aa
    [activated] => 1
    [type] => 3
    )

    [Company] => Array
    (
    [id] => 2
    )

    [CompaniesUser] => Array
    (
    [0] => Array
    (
    [company_id] => 2
    )

    )

    )

    and this the code:
    $this->data['CompaniesUser'][0]['company_id'] = $id;
    $this->data['CompaniesUser']['created'] = getdate();

    $this->User->bindModel(array(‘hasMany’=>array(‘CompaniesUser’)));
    $this->User->saveAll($this->data);

  • http://teknoid.wordpress.com teknoid

    @frank

    I don’t see anything related to Post.id in your data sample. My only guess is that you meant Company.id, but it should still be Company.0.id if you are using saveAll().
    At any rate, best to ask for this kind of support at the google group ;)

  • frank

    thx for your quick replay, no i meant the user_id (i insert the user into the usertable and then i will insert an entry in the CompaniesUser table) – i will ask at the google group!

  • jwswj

    @frank

    I’ve hit the same wall, did you ever solve you issue? (Can’t find your post on Google Groups)

  • http://teknoid.wordpress.com teknoid

    @jwswj

    Just post the question again. Be sure to provide the sample array you are trying to save, and explain the basic model scenario. Try not too post too much code (simplify, if needed) it’s very hard to read some random code trying to pinpoint the problem.

  • tekomp

    This post helped me a ton, as do many of your other posts. Have you tried this on RC4? I’m getting a PHP Notice on the saveAll:

    Notice (8): Undefined index: PostsTag [CORE/cake/libs/model/model.php, line 1551]

    Model::saveAll() – CORE/cake/libs/model/model.php, line 1551

  • http://teknoid.wordpress.com teknoid

    @tekomp

    I haven’t yet, I’ll give it a shot later on…

  • Nachopitt

    Hey tekomp, I had that warning in 1.2 stable also, and I just realized that you have to do Model::bindModel($assoc, false) to keep the binding permament, because I think the model bindings are restored inside the saveAll method because of a find operation. Hope that it helps you.

  • http://teknoid.wordpress.com teknoid

    @Nachopitt

    Good advice, thanks for sharing it.

  • Pingback: links for 2009-02-08

  • tekomp

    Thanks Nachopitt… worked perfect!

  • Tom

    I’m not sure this works anymore? I followed this all but I can’t seem to get it… Can you not save your association data, creating the new row in the HABTM table at the same time? Do you have to save to the HABTM join table first and then come back and save extra data?

    I get some sort of validation error or something.

  • http://teknoid.wordpress.com teknoid

    @Tom

    Works perfectly well.

    Where do you have the extra row in the join table or the HABTM table, those two are very different? If you have the extra row in the HABTM table, this is not what you are looking for…

    • Tom

      the join table, sorry. the one that says ingredients_recipes in the case of Recipes HABTM Ingredients (alphabetical order table name right?)

      I have extra columns on ingredients_recipes … I want to associate the ingredient to a recipe, but then have extra data say “quantity” or what not.. more specifically that’s going to be another id for another association. It’s a weird association I guess, but I don’t want (can’t have) multiple join tables for what I need. This extra data has to be on the join table.

      Normally in the past I’ve just made use of two separate saves or afterSave() etc. I’d rather kinda not do that, maybe get proper validation etc.

      • Tom

        Thank God I was doing it wrong. Well I was doing it right, but I pluralized the HABTM association field form select input. So I realized I had another problem after just trying to save normally without saving extra data to the table. My bad. Good to note and double check every single field and whether its plural or not.

  • http://teknoid.wordpress.com teknoid

    @Tom

    Well, while I was posting the response… you got it solved. Good news ;)

  • chirayu

    I have problem in saving extra field. Pls see my code and suggest:
    class Holidaypackage extends AppModel{
    var $name = ‘Holidaypackage';//add the following $hasMany definition

    var $hasAndBelongsToMany = array(
    ‘Addservice’=>array(
    ‘className’ => ‘Addservice’,
    ‘joinTable’ => ‘addservices_holidaypackages’,
    ‘foreignKey’ => ‘holidaypackage_id’,
    ‘associationForeignKey’ => ‘addservice_id’,
    ‘unique’ => false,
    ‘with’ => ‘AddservicesHolidaypackage’
    )
    );
    } //end of holidaypackage

    table structure:
    created table addsevices_holidaypackages(
    id char(36) not null primary key,
    addservice_id char(36) not null,
    holidaypackage_id char(36) not null,
    frequency smallint not null default 1
    );

    I am not getting data of frequency and getting ids for both above.

    Any suggetions or help?

  • DaveT

    Hi Teknoid,

    Would you explain the intermediate [0] in $this->data['PostsTag'][0]['tag_id'] = 15;, please.

    Would this allow multiple inserts into the same associated table? Something like?:
    $this->data['PostsTag'][0]['tag_id'] = 15;
    $this->data['PostsTag'][1]['tag_id'] = 16;
    $this->data['PostsTag'][2]['tag_id'] = 17;

    Thanx, DaveT.

  • http://teknoid.wordpress.com teknoid

    @DaveT

    That’s exactly it. The extra [0] or ['whatever'] index is needed for saveAll() to store multiple records.

    • DaveT

      Thanx Teknoid,

      OK, I seem to get the how (at least in this simple case, I have a more complex situation that I’m currently muddling through), now what about the why?
      It looks to me like something related to the array indexing of the data structure passed back to the associated model.

      Do you have a more clear explanation on exactly ‘what’ the [0] index is referring to?

      Thanx again, DaveT.

  • http://teknoid.wordpress.com teknoid

    @DaveT

    ‘0’ or not doesn’t really make a difference, it simply allows you to have an “extra” index in your array to have multiple records without overriding any existing Model.field values.

    Now, this is only true for a hasMany relationship (for associated model) or multiple records for a single model.

    I have some more detailed explanations posted on how saveAll() works in different scenarios (a quick search should give you some hints).
    It is also covered in the manual.

  • Pingback: Planète Du Geek | Hazout Ilane » CakePHP : Relation HABTM avec champs supplémentaires

    • http://www.planetedugeek.fr Hazout Ilane

      Thanks for this link

  • Daniel Connolly

    You can do this without the temporary hasMany bind.

    Just create your form fields like:

    $this->data['Tag'][0]['PostTag']['tag_id'] = 15;
    $this->data['Tag'][0]['PostTag']['status'] = ‘disabled';

    $this->data['Tag'][1]['PostTag']['tag_id'] = 16;
    $this->data['Tag'][1]['PostTag']['status'] = ‘enabled';

  • mike

    Hey folks,

    this is a realy helpful post and the solutions are working mostly as exptected. But I just don’t figure out how to update (not add) multiple habtm’s with a additional field.

    For example I edit a Post and select multiple tags at once in a cake form selectbox. in the posts_tags table I have an additional field that holds lets say the date of that change. How do I do this? I tried it for hours and I am not able to make it work.

    Every help is appreciated!!

    Regards
    Michael

  • http://www.dibyendu.net/lifeblog Dibyendu Mitra Roy

    Hi All,

    At the outset I must thank the author for posting such an excellent topic and also others for participating in it. Well I believe one of the most powerful mechanism of CakePHP is the relationship binding and working on the data models based on that relationship. And among the 3 types of relationship that we can have in the CakePHP, the most intriguing is that of HABTM. No prize for guessing that it has attracted lot of discussion.

    I am writing this to share with you what I have found a “way-out” to the problem of saving extra information in the join table of HABTM. Here is scenerio:

    I wanted to make a multi-lingual website wherein administrator will be able to modify the page contents for all the languages at one go. That is without actually editing each of the language records. So essetially we will have 3 tables. viz.,

    1. contents
    2. languages
    3. contents_languages

    The last one is the map table.
    And among other things in the map table is having 2 extra fields – title and description. This would basically store the translations for page title and page content

    MODEL
    ===========================================================
    In my content model I have specified the HABTM relationship:

    class content extends AppModel {

    var $name = ‘content';

    //The Associations below have been created with all possible keys, those that are not needed can be removed
    var $hasAndBelongsToMany = array(
    ‘Language’ => array(
    ‘className’ => ‘Language’,
    ‘joinTable’ => ‘contents_languages’,
    ‘with’ => ‘ContentsLanguage’,
    ‘foreignKey’ => ‘content_id’,
    ‘associationForeignKey’ => ‘language_id’,
    ‘unique’ => true,
    ‘conditions’ => ”,
    ‘fields’ => ”,
    ‘order’ => ”,
    ‘limit’ => ”,
    ‘offset’ => ”,
    ‘finderQuery’ => ”,
    ‘deleteQuery’ => ”,
    ‘insertQuery’ => ”
    )
    );

    }

    CONTROLLER
    ===========================================================

    Here is how my controller function looks:

    function add() {
    $languages = $this->Content->Language->find(‘all’);
    $this->set(compact(‘languages’));
    if (!empty($this->data)) {
    $this->Content->create();

    $this->Content->bindModel(array(‘hasMany’=>array(‘ContentsLanguage’)));

    if ($this->Content->saveAll($this->data)) {
    $this->Session->setFlash(__(‘The Content has been saved’, true));
    $this->redirect(array(‘action’=>’index’));
    } else {
    $this->Session->setFlash(__(‘The Content could not be saved. Please, try again.’, true));
    }
    }

    }

    Notice that I have created a model-binding on-the-fly: Content hasMany ContentsLanguage. Also I have used saveAll() method to save data in contents model and multiple data on the contents_languages model.

    VIEW
    ===========================================================
    Now lets look at the view page:

    create(‘Content’);?>

    input(‘name’);
    echo $form->input(‘code’);
    //echo $form->input(‘Language’);
    $i = 0;
    foreach($languages as $language) :
    $fld_id = “ContentsLanguage.”.$i.”.language_id”;
    echo $form->input($fld_id, array(“type” => “hidden”, “value” => $language['Language']['id'])); // Dynamically creating hidden field to store id
    $fld_title = “ContentsLanguage.”.$i.”.title”; // Dynamically creating title field for user to input
    echo $form->input($fld_title);
    $i++;
    endforeach;

    ?>

    end(‘Submit’);?>

    Notice that I have dynamically created the title field for which the input will appear from form. So if we have 3 languages then there would be 3 text fields in the form.

    Hope, you will like my post. Any response to this e-mail address is welcome roy@dibyendu.net

  • Jeff

    I know it’s an old thread but I hope somebody’s looking. Instead of creating a single record in my join table, Cake’s inserting two records, one with the relationship and another with just the extra field. Don’t know what I’m doing wrong. My data array looks like this:

    Array
    (
    [Project] => Array
    (
    [id] => 21
    )

    [ProjectsName] => Array
    (
    [0] => Array
    (
    [isorigin] => 1
    )

    )

    [Name] => Array
    (
    [name] => Mobile 9.0
    [user_id] => 1
    )

    )

    Name is a new insert to an existing project. isorigin is my extra field in the join table.

  • teknoid

    @Jeff

    Based on your data array, it looks you are saving a bunch of models, yet non of them look like the one you’d be inserting in the “join” table…

  • Jeff

    Sorry if that didn’t look all that clear. I have a project table and a name table and I have a project_names table as the join table. The ProjectsName model is my join table model which has an additional field isorigin. The project already exists in the db and the name is being created. Currently, I get two records in my join table. One has the correct project_id and name_id but no value for isorigin. The other has no values for project_id and name_id but a value of 1 for isorigin. How should my data array be set up to create this as one record?

    • Leonardo

      I had that same problem with this method, i don’t know if you sorted it out, but i solved it unbinding the hasAndBelongsToMany relationship first.

      unbindModel(array(‘hasAndBelongsToMany’=>array(‘Language’)));
      bindModel(array(‘hasMany’=>array(‘ContentsLanguage’)));

      if you know another way to solve this please post it, i was going crazy with this

  • Ray

    Your method for saving extra fields on habtm association table works just fine.

    Unless I try to combine it with counter cache behaviour http://bakery.cakephp.org/articles/danaki/2009/05/29/counter-cache-behavior-for-habtm-relations. Model::saveAll() throws an “undefined index PostsTag”.

    Do you have any idea why that does not work?

  • teknoid

    @Ray

    Sorry, I am not familiar with that behavior.

  • asdzxc

    Hello Teknoid, i just have a question:

    How will i update each extra field of the Joint Table?

    a sample scenario would be this:

    $this->Form->input(‘Tag.Tag’, array(‘multiple’=>’checkbox’));

    so it would output some related tags as checkbox, than the field for the ‘extra field’.

    How would i do that? Thanks in advance Teknoid, your reply would be a great help to people like me who needs your great skill.

  • teknoid

    @asdzxc

    You should be able to get the extra field just by having input(‘Tag.other_field’)…
    The checkbox is a just an available convenience option. You can certainly build the form with a foreach() and inject other fields as option.

    Unless I am misunderstanding the question.

  • asdzxc

    Hello teknoid, i think i found the answer,

    the Tag.Tag would query all the tags related to that posts so if there are like 3 tags on it for example:

    [Post]
    =>[Tag]
    =>[1]beverages
    =>[2]pastries
    =>[3]sweets

    in the Form field it would produce (by default is select type):

    $this->Form->input(‘Tag.Tag’);

    an input box or check box with the 3 tag data which are beverages,pastries and sweets.

    so my question was, how would i add another field for each of those data where the added field would be extra field in the ‘With’ database

    eg:

    posts_tags

    . . .

    my solution was, i had to query them one by one, present them for each of the data than add the extra field beside them,in my case i used a checkbox, i had condition there checking the result from the checkbox if its checked then i would take its id and compare that id from the array index of the extra field data(which was also looped,so it would get the id of the data) when it comes to saving the extra field in the with association, i had to make my own function to save it :)

    so thats it, well my question was that if its there a possible way to add an extra field for each data result from $this->Form->input(Tag.Tag);

    i think my solution is the perfect response to MIKE’s question (mike on January 7th, 2011 at 3:55 PM #)

    well i Hope it helps :D