Making HABTM relationships unique in CakePHP

Question

I have two models, called Book and Tag, which are in a HABTM relationship. I want a couple (book, tag) to be saved only once. In my models I have

var $hasAndBelongsToMany = array(
    'Tag' => array(
        'className' => 'Tag',
        'joinTable' => 'books_tags',
        'foreignKey' => 'book_id',
        'associationForeignKey' => 'tag_id',
        'unique' => true
    )
);

and viceversa, but the Unique flag does not help me; I can still save two times the same couple.

How do I do this in CakePHP? Should I declare the couple (book, tag) unique in the database directly, or will this make CakePHP go nuts? Is there a Cakey way to handle this situation?

EDIT: I tried making the couple unique with the query (I'm using MySQL)

ALTER TABLE books_tags ADD UNIQUE (book_id,tag_id);

but this does not work well. When I save more than one tag at a time, everything goes well if all the couples are new. If at least one of the couples is repeated, CakePHP fails to do the whole operation, so it does not save ANY new couple (not even the good ones).

Answer 1

In my opinion, any time you have data requirements, those requirements should be specified at the data(base) level. Enforce your rules at the lowest possible level. If the application can enforce those rules as well, and you choose to do so, then I'd say it can't hurt. I'd advise against using an application as the sole means of enforcing data constraints, though.

Answer 2

I would recommend that you put the constraint in the database schema. It would be easy if you could declare the combined columns as the primary key although I think this would cause cake some problems.

Depending on which RDBMS you use, you may be able to create a combined row index and declare a unique constraint on that.

You other option, for keeping this logic in Cake, would be to alter the beforeSave() function in the Book model, so that it first runs a find() on the passed data, and will only save if the find returns false (meaning there is no previous pair, meaning you've fulfilled your unique constraint).

One option that I don't promise will work, but looks like it might, would be the CakePHP Counter Cache behavior. Not sure it works by default with HABTM, but here is the link to the cake book (http://book.cakephp.org/view/816/counterCache-Cache-your-count) and the link to the addon that will make it work with habtm (http://bakery.cakephp.org/articles/view/counter-cache-behavior-for-habtm-relations)

Answer 3

Hackish way:

If you can have a third DB field (like the one above) do a varchar(32) that is UNIQUE that is an MD5 hash of the first two.

You'd have to modify all saves to be $md5_unique_field = md5( $field_one.$field_two );

CakePHP might allow for a custom validation or a custom model extension that could do this automatically.

I agree that this should be enforced at the DB level also though, and CakePHP should catch the errors.

Answer 4

The way Cake usually saves HABTM data is by erasing all existing data for the model from the HABTM table and inserting all the relationships anew from the data array you feed into save(). If you're exclusively using this way to deal with HABTM relationship, you shouldn't have a problem.

Of course, that's not always the ideal way. To enforce validation on the HABTM table, you need to create the model for it and add some custom validation, like this:

class BooksTag extends AppModel {

    var $validate = array(
        'book_id' => array('rule' => 'uniqueCombi'),
        'tag_id'  => array('rule' => 'uniqueCombi')
    );

    function uniqueCombi() {
        $combi = array(
            "{$this->alias}.book_id" => $this->data[$this->alias]['book_id'],
            "{$this->alias}.tag_id"  => $this->data[$this->alias]['tag_id']
        );
        return $this->isUnique($combi, false);
    }

}

Just make sure to save data using saveAll($data, array('validate' => 'first')) to trigger the validation.

You may have to specify the BooksTag model explicitly in the relationship:

var $hasAndBelongsToMany = array(
    'Tag' => array(
        ...
        'with' => 'BooksTag'
    )
);

If on top of that you're also enforcing the uniqueness on the DB level, all the better.