CakePHP hasAndBelongsToMany vs hasMany through

Question

This is only my 3rd CakePHP app, and the first to require a HABTM type association. I'm working with Cake 2.1, in which there was a change to HABTM parameters allowing for extra information to be saved by setting the 'unique' key to 'keepExisting'. In a normal HABTM association you have a table with 2 fields, each foreign keys. I think I need one with 3 foreign keys. Is that possible by using the 'unique' key, or should I use a hasMany through association?

Here's my schema:

/* repair_orders */
CREATE TABLE repair_orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    number VARCHAR(16),  //Auto-generated repair order number 
    created DATETIME DEFAULT NULL,
    modified DATETIME DEFAULT NULL
);

/* Employees */
CREATE TABLE employees (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(16), 
    created DATETIME DEFAULT NULL,
    modified DATETIME DEFAULT NULL
);

/* op_codes */
CREATE TABLE op_codes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(16),
    sale_material DECIMAL(10,2),
    cost_material DECIMAL(10,2), 
    created DATETIME DEFAULT NULL,
    modified DATETIME DEFAULT NULL
);

The op codes are a essentially a list of items and their cost.

A repair order can have many op codes. I also need to keep track of which employee is assigned to the specific op codes on each repair order. So I think that requires another table, something like:

/* repair_order_assignments */
CREATE TABLE repair_order_assignments (
    repair_order_id INT(16),
    op_code_id INT(16),
    employee_id INT(16)
);

So my associations would be:

Repair_Order_Assignment hasMany Employee, Op_Code
Employee belongsTo Repair_Order_Assignment
Op_Code belongsTo Repair_Order_Assignment

Repair_Order_Assignment hasAndBelongsToMany Repair_Order
Repair_Order hasAndBelongsToMany Repair_Order_Assignment

According to the manual, when hasAndBelongsToMany associations are saved, the association is deleted first. You lose the extra data in the columns as it is not replaced in the new insert. However, it goes on to note that in 2.1 there is a unique variable that can be set to save the extra data. Will this setup work with the unique key or should I use a hasMany through approach?

Dunhamzzz Dunhamzzz · Accepted Answer · 2012-04-27T15:41:27

I've found the only thing that a HABTM relationship for is tags and multiple categories.

Any sort of membership, friendship or following relationship I use has many through because sometimes it's nice to attach extra data to a relationship, even if it's just created/modified. Another thing that puts me off HABTM is the replace-everything-upon-update logic. Causes a lot of bother saving different parts of a model.

CakePHP hasAndBelongsToMany vs hasMany through

1 Answers