0

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. enter image description here

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;
  • I'm note sure, but this looks like you are creating a `n:m` relationship between two join tables, and you want to populate the third join table that is used to connect the other two join tables? – ndm Nov 10 '18 at 15:39
  • Exactly. I'm sorry for not being clear enough, but it was a hard day at work because of this problem :( – Chitarrista Nov 10 '18 at 18:58
  • That's not supported by CakePHP's `BelongsToMany` associations, you cannot create further associations with the model that is automatically created for a join table in such an association. You'd have to explicitly create concrete models for the join tables (see also the `through` option for `BelongsToMany` associations), and create associations for them, like `Products hasMany OptionsProducts` > `OptionsProducts belongsToMany ProductsTargets`, and save via those models/associations. – ndm Nov 10 '18 at 22:26
  • Thank you for your comment, I finally did it. Please, write it as an answer so I can mark it as answer and upvote it :) – Chitarrista Nov 12 '18 at 16:44

0 Answers0