Удаление группы при удалении ее елементов SQL
Есть три таблицы: data (контент страницы), section (секции для группировки), hub (сгруппированный контент).
hub
| page | data | section |
|---|---|---|
| index | 1 | NULL |
| about | 2 | 1 |
| about | 3 | 1 |
| about | 4 | NULL |
| contacts | 5 | 2 |
| contacts | NULL | 1 |
contacts / NULL / 1на интерфейсе включит в себя:data 2,3
section
| id | title |
|---|---|
| 1 | foo |
| 2 | bar |
| 3 | baz |
3 / bazеще не использовалась (ее не надо удалять)
data
| id | content |
|---|---|
| 1 | some text - 1 |
| 2 | some text - 2 |
| 3 | some text - 3 |
| 4 | some text - 4 |
| 5 | some text - 5 |
Поступает запрос на удаление двух блоков контента 2, 5, удаляю DELETE FROM data WHERE id IN(2,5), при удалении data=5, секция 2 тоже должна быть удалена.
Замысел такой:
получить
idсекций у блоков 2, и 5>>>посчитать сколько всего элементов у секции>>>посчитать количество элементов секции которые будут удалены>>>если количество элементов у секции удаляемых и общее количество совпадает удалить секцию>>>удалить блоки изhub
Вот что получилось:
DELETE FROM `section`
WHERE `id` IN(
SELECT `hub`.`section`
FROM (
# всего элементов у секции
SELECT `to`.`section`, COUNT(`to`.`section`) AS `count`
FROM `hub` AS `to` INNER JOIN (
SELECT `section`, COUNT(`section`) AS `count`
FROM `hub`
WHERE `data` IN(4,5) AND `section` IS NOT NULL
GROUP BY `section`) AS `from`
ON `to`.`section` = `from`.`section`
GROUP BY `to`.`section`) AS `hub`
INNER JOIN (
# количество элементов секции которые будут удалены
SELECT `section`, COUNT(`section`) AS `count`
FROM `hub`
WHERE `data` IN(4,5) AND `section` IS NOT NULL
GROUP BY `section`) AS `del` USING(`section`)
WHERE `del`.`count` = `hub`.`count`)
Смущает размер запроса и дублированный SELECT:
SELECT `section`, COUNT(`section`) AS `count`
FROM `hub`
WHERE `data` IN(4,5) AND `section` IS NOT NULL
GROUP BY `section`) AS `del` USING(`section`)
Думал создать временную таблицу, но к ней можно можно подключиться раз.
Можно ли сделать как то лаконичнее? И верна ли реализация логики удаления?
CREATE TABLE `data` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`content` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
CREATE TABLE `hub` (
`page` varchar(64) NOT NULL,
`section` int(11) UNSIGNED NULL DEFAULT NULL,
`data` int(11) UNSIGNED NULL,
UNIQUE (`page`, `section`, `data`) USING BTREE,
INDEX `page-I`(`page`) USING BTREE,
INDEX `section-I`(`section`) USING BTREE,
INDEX `data-I`(`data`) USING BTREE,
CONSTRAINT `page-FK` FOREIGN KEY (`data`)
REFERENCES `data` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB;
CREATE TABLE `section` (
`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
-- исходные данные
INSERT INTO section VALUES
(1, 'foo'),
(2, 'bar'),
(3, 'baz');
INSERT INTO data VALUES
(1, 'some text - 1'),
(2, 'some text - 2'),
(3, 'some text - 3'),
(4, 'some text - 4'),
(5, 'some text - 5');
INSERT INTO hub (`page`, `data`, `section`) VALUES
('index', 1, NULL),
('about', 2, 1),
('about', 3, 1),
('about', 4, NULL),
('contacts', 5, 2),
('contacts', NULL, 1);
Ответы (1 шт):
Ну если я верно понял связи, то
DELETE data, hub, section
FROM data
LEFT JOIN hub ON data.id = hub.data
LEFT JOIN section ON section.id = hub.section
WHERE data.id IN(2,5)
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=68d0e16fd3baf885eecf746888dcf045
Чтобы не пытаться прибить лишнее и получить ошибку, надо создать внешний ключ hub-section и добавить в запрос на удаление IGNORE.
UPDATE
В соответствии с исправлениями исходных данных и демонстрацией требуемого результата:
- Создаём внешний ключ hub -> section
ALTER TABLE hub
ADD FOREIGN KEY (section) REFERENCES section (id);
- Добавляем в запрос на удаление слово IGNORE.
DELETE IGNORE data, hub, section
FROM data
LEFT JOIN hub ON data.id = hub.data
LEFT JOIN section ON section.id = hub.section
WHERE data.id IN(2,5)
И получаем то, что требуется: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ba43e1b024ddb4ad4c271586f0c4c555