Saving HABTM records when not all join table columns are foreign keys

Question

I am trying to update tables with a has and belongs to many (HABTM) relationship.

When my join table looked like this:

CREATE TABLE IF NOT EXISTS `items_labels` (
  `item_id` int(11) NOT NULL,
  `label_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I use CakePHP, so I could update the tables with $this->Item->save($data) where $data was:

Array
(
    [Item] => Array
        (
            [id] => 1
        )
    [Label] => Array
        (
            [Label] => Array
                (
                    [0] => 4
                    [1] => 5
                    [2] => 7
                    [3] => 8
                )
        )
)

I've added a column to my join table, so it now looks like:

CREATE TABLE IF NOT EXISTS `items_labels` (
  `item_id` int(11) NOT NULL,
  `label_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

When I am saving $data, I also want to save a user id. The user id will be the same for all records in a single save operation.

Can someone help me understand what the $data array needs to look like in order to incorporate the user id? Thanks.

Answer 1

CakePHP's model::save() method won't do this for you AFAIK. I think you have to use model::saveAll() and call it on the "with" model. CakePHP is automagically aware of your join table and can modelise it without you having to create a physical model file and class yourself. All you should have to do is format the data array in the format saveAll expects.

I've not tried it, but something like the following should work.

<?php
class Item extends AppModel {
  var $name = 'Item';
  var $_habtmData = null;
  function beforeSave() {
    $this->unbindModel(array('hasAndBelongsToMany' => array('Label')));
    $this->_habtmData = $this->data['Label'];
  }
  function afterSave() {
    if (is_array($this->_habtmData) && !empty($this->_habtmData)) {
      foreach ($this->_habtmData['Label'] as $k => $labelId) {
        $this->_habtmData['Label'][$k] = array(
          'item_id' => $this->id,
          'label_id' => $labelId,
          'user_id' => $userId, // Get this from somewhere
        );
      }
      $this->bindModel(array('hasMany' => array('ItemsLabel')));
      $this->ItemsLabel->saveAll($this->_habtmData);
    }
  }
}
?>

This is all done in the model (as it should be), so your controller and views stay clean. We do it all in afterSave, so it only tries it if the Item data validates.

Essentially, we temporarily unbind the Item hasAndBelongsToMany Label association in beforeSave, so the HABTM data doesn't get saved and we store the HABTM data in a property of the model, so we can use it in afterSave (although it's probably still available then anyway).

We then bind the "with" model to Item with a hasMany association, and format the data as required by CakePHP's core model::saveAll() method, before finally calling it on the "with" model, passing in the new data.

It should work in theory - good luck and let me know how you get on ;-)

Answer 2

This should work:

Array
(
    [Item] => Array
        (
            [id] => 1
        )

    [Label] => Array
        (
            [Label] => Array
                (
                    [0] => Array
                        (
                            [label_id] => 4
                            [user_id] => 1
                        )

                    [1] => Array
                        (
                            [label_id] => 5
                            [user_id] => 1
                        )

                    [2] => Array
                        (
                            [label_id] => 7
                            [user_id] => 1
                        )

                    [3] => Array
                        (
                            [label_id] => 8
                            [user_id] => 1
                        )

                )

        )

)

It will generate multiple INSERTs, but will work with one save call.