Skip to content
This repository was archived by the owner on Jan 29, 2020. It is now read-only.
This repository was archived by the owner on Jan 29, 2020. It is now read-only.

Deletion of table entries by ID in an associated table #147

Open
@automatix

Description

@automatix

Sometimes the database contains tables like foo and bar with an n:m relationship between them, that is implemented as an association table foo_bar (with FOREIGN KEYs to foo.id and to bar.id).

CREATE TABLE IF NOT EXISTS `my_db`.`bar` (
  `id` INT UNSIGNED NOT NULL,
  ...
  PRIMARY KEY (`id`)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `my_db`.`foo` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(50) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `my_db`.`foo_bar` (
  `bar_id` INT UNSIGNED NOT NULL,
  `foo_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`bar_id`, `foo_id`),
  CONSTRAINT `fk_foo_bar_bar`
    FOREIGN KEY (`bar_id`)
    REFERENCES `my_db`.`bar` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_foo_bar_foo`
    FOREIGN KEY (`foo_id`)
    REFERENCES `my_db`.`foo` (`id`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

It would be nice to be able to delete all foo entries by bar.id. In SQL it works like this (s. here):

DELETE `foo` FROM `foo`
INNER JOIN `foo_bar` ON `foo_bar`.`foo_id` = `foo`.`id`
INNER JOIN `bar` ON `bar`.`id` = `foo_bar`.`bar_id`
WHERE `bar`.`id` = ?

In Zend\Db it doesn't seem to be implemented. At least I don't see any possibility to set the value for between DELETE and FROM, s. here (also no way to set multiple tables after FROM). If I'm wrong, please show, how it works. Otherwise this can be considered as a feature request. ;)

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions