I have a complex association in my db.
I'm trying to create a new record in targets
. Every target has many products and every product has many options. Join tables both have a joinData column (points).
I have no problem populating products_targets
records
echo $this->Form->hidden("products.{$i}.id", ['value' => $Product->id]);
echo $this->Form->hidden("products.{$i}._joinData.product_id", ['value' => $Product->id]);
echo $this->Form->hidden("products.{$i}._joinData.target_id", ['value' => $Element->id]);
echo $this->Form->number("products.{$i}._joinData.points")
I can't find a way to populate my options_products_products_targets
table, that is a join table between options_products
and products_targets
.
I've tried by using this code:
echo $this->Form->number("products.{$i}.options.{$j}._joinData.points");
but clearly this is not the right way. How could I save a BelongsToMany association on a BelongsToMany association like this?
Here is a screenshot of the involved tables, shortened SQL will follow.
CREATE TABLE IF NOT EXISTS `mg`.`targets` (
`id` INT NOT NULL AUTO_INCREMENT,
`status_id` INT NOT NULL DEFAULT 1
PRIMARY KEY (`id`),
CONSTRAINT `fk_targets_1`
FOREIGN KEY (`status_id`)
REFERENCES `mg`.`statuses` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mg`.`products_targets` (
`id` INT NOT NULL AUTO_INCREMENT,
`product_id` INT NOT NULL,
`target_id` INT NOT NULL,
`points` DOUBLE NOT NULL DEFAULT 0
PRIMARY KEY (`id`),
CONSTRAINT `fk_products_targets_1`
FOREIGN KEY (`product_id`)
REFERENCES `mg`.`products` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_products_targets_2`
FOREIGN KEY (`target_id`)
REFERENCES `mg`.`targets` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mg`.`products` (
`id` INT NOT NULL AUTO_INCREMENT,
`status_id` INT NOT NULL DEFAULT 1,
`offer_id` INT NOT NULL,
`technology_id` INT NOT NULL,
`typology_id` INT NOT NULL
PRIMARY KEY (`id`),
CONSTRAINT `fk_products_1`
FOREIGN KEY (`status_id`)
REFERENCES `mg`.`statuses` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_products_2`
FOREIGN KEY (`offer_id`)
REFERENCES `mg`.`offers` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_products_3`
FOREIGN KEY (`technology_id`)
REFERENCES `mg`.`technologies` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_products_4`
FOREIGN KEY (`typology_id`)
REFERENCES `mg`.`typologies` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mg`.`options_products` (
`id` INT NOT NULL AUTO_INCREMENT,
`option_id` INT NOT NULL,
`product_id` INT NOT NULL
PRIMARY KEY (`id`),
CONSTRAINT `fk_options_products_1`
FOREIGN KEY (`option_id`)
REFERENCES `mg`.`options` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_options_products_2`
FOREIGN KEY (`product_id`)
REFERENCES `mg`.`products` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `mg`.`options_products_products_targets` (
`id` INT NOT NULL AUTO_INCREMENT,
`options_product_id` INT NOT NULL,
`products_target_id` INT NOT NULL,
`points` DOUBLE NOT NULL DEFAULT 0
PRIMARY KEY (`id`),
CONSTRAINT `fk_options_products_products_targets_1`
FOREIGN KEY (`options_product_id`)
REFERENCES `mg`.`options_products` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_options_products_products_targets_2`
FOREIGN KEY (`products_target_id`)
REFERENCES `mg`.`products_targets` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE)
ENGINE = InnoDB;