Implement Many to many relations and generate table

1

Hey guys

I have a big issue about relational database in Cakephp. I built a databases that involve, among others a many to many relation between two tables. Therefor I made three tables “branches”, “projects” and the link between them to materialize the relation, “branches_projects”, as follows

CREATE TABLE branches (
branch_id INTEGER NOT NULL AUTO_INCREMENT,
namebranch VARCHAR(50),
PRIMARY KEY(branch_id)
) ENGINE = InnoDB;

CREATE TABLE projects (
project_id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(100),
beginning DATE,
end DATE,
task_id INTEGER,
PRIMARY KEY(project_id),
CONSTRAINT FOREIGN KEY (task_id) REFERENCES tasks (task_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;

CREATE TABLE branches_projects (
branch_id INTEGER,
project_id INTEGER,
PRIMARY KEY(branch_id,project_id),
CONSTRAINT FOREIGN KEY (branch_id) REFERENCES branches (branch_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (project_id) REFERENCES projects (project_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;

Additionally, I made other tables, one of them called “tasks” which is linked with a one to many relation with “projects” (“tasks” is also linked with other tables, but that is not important now)

CREATE TABLE tasks (
task_id INTEGER NOT NULL AUTO_INCREMENT,
name VARCHAR(250),
status VARCHAR(50),
visit_id INTEGER,
blueprint_id INTEGER,
rdi_id INTEGER,
payment_status_id INTEGER,
hes_id INTEGER,
purchase_order_id INTEGER,
PRIMARY KEY(task_id),
CONSTRAINT FOREIGN KEY (visit_id) REFERENCES visits (visit_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (blueprint_id) REFERENCES blueprints (blueprint_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (rdi_id) REFERENCES rdis (rdi_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (payment_status_id) REFERENCES payment_status (payment_status_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (hes_id) REFERENCES hes (hes_id)
ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders (purchase_order_id)
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB;

I baked them, receiving a message sort of “cake can´t build all relations this time”. The link between “projects” and “tasks” works perfectly, but the link with “branches does not. It generates the three tables, but I intended to have a table where I could select the project and the branch where it belongs, along with the tasks associated. Here is the ProjectController table (the view function part)

public function view($id = null)
{
    $project = $this->Projects->get($id, ['contain' => ['Tasks']
    ]);
    //I added this line
    $project = $this->Projects->get($id, ['contain' => ['Branches']]);
    //end
    $this->set('project', $project);
}

please help…

2

If you just started this project and you can do it, I would reccomend follow CakePHP conventios on naming. For example in your tasks table use just id and not task_id.
Perhaps you can use it as it is now, but conventions are great help.

Also the reccomendation is to have a unique id filed in your join table (branches_projects)

Relationships are defined in the table classes as described here: https://book.cakephp.org/3.0/en/orm/associations.html

Your question is not really clear for me but if I understood correctly and your table class associations are set, than this is what you need:

$project = $this->Projects->get($id, ['contain' => ['Tasks', 'Branches']]);

If this is not you want use matching for Branches.

3

Thanks for your advice! I realize I wasn’t follow the database conventions accurately, so I’ll proceed as you suggest

Thanks!