Deletion of table entries by ID in an associated table

[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. ;)

[froschdesign]

DELETE `foo` FROM `foo`
INNER JOIN

This is not a part of ANSI SQL standard. So 👎 on this.
A subselect should help.

[michalbundyra]

This repository has been closed and moved to laminas/laminas-db; a new issue has been opened at laminas/laminas-db#104.