Обновление записей в БД при связи многие ко многим

Question

Изучаю SQL. У меня есть таблица products_to_colors она связывает 2 таблицы colors и products, связь многие ко многим. Содержит поля id (просто id записи в таблице, хз зачем сделал эту колонку), product_id, color_id

 id | color_id | product_id
----+----------+-----------
1   | 1        | 7
----+----------+-----------
2   | 2        | 7
----+----------+-----------
3   | 3        | 7
----+----------+-----------
4   | 4        | 7

Столкнулся с задачей обновления данных в этой БД. Представим, что пришёл новый запрос и у продукта изменились цвета, например, продукт с id 7, теперь должен иметь цвета [1, 3, 5, 7], было [1, 2, 3, 4]. Не могу написать адекватный SQL запрос для такой задачи. Конечно можно пройтись с помощью 2 циклов и ЯП (языка программирования) (1 цикл удаляет, 2 добавляет), сделав кучу запросов к БД, но это какой-то говнокод и никакой оптимизации, я так делать не хочу.

Скорее всего должно быть 2 запроса к БД, первый удаляет ненужные связи из products_to_colors, второй добавляет необходимые.

С запросом для удаления связей вроде как всё более менее понятно, сделал так (не знаю насколько это правильно):

DELETE FROM products_to_colors
    WHERE color_id = ANY (
            SELECT color_id FROM products_to_colors WHERE product_id=7
        )
        AND color_id != ANY('{1,3,5,7}')
        AND product_id=7;

Остаётся функция добавления связей с цветами, которых до этого не было. Тут я никак не соображу, что делать. Ну я могу пройтись по массиву со списком id цветов с помощью ЯП и проверить, есть ли нужная связь в БД, если нет, то добавлять. Но это опять куча запросов и какой-то говнокод.

Нашёл такой пост но там немного другая ситуация, не знаю как это адаптировать к моей задаче. По SQL (особенно postgres) очень мало информации в интернете, особенно примеров. Не понятно как учиться писать хороший код =)

Answer 1

Принципиально это выглядит так:

WITH cte AS ( DELETE FROM products_to_colors
              WHERE (product_id, color_id) IN ( {список пар на удаление} )
            )
INSERT INTO products_to_colors VALUES {список пар на вставку} ;

fiddle

Answer 2

В итоге решил задачу так:

DELETE FROM products_to_colors
WHERE color_id IN (
        SELECT color_id FROM products_to_colors WHERE product_id={product_id}
    )
    AND color_id != ALL({new_colors})
    AND product_id={product_id};

INSERT INTO products_to_colors (color_id, product_id)
    SELECT el, {product_id} FROM unnest({new_colors}) AS el WHERE el NOT IN (
        SELECT color_id FROM products_to_colors WHERE product_id={product_id}
    );

Где: {new_colors} - новые цвета, которые должны быть привязаны к продукту, {product_id} - id продукта

БЛОГ НА HUSL

Обновление записей в БД при связи многие ко многим

Ответы (2 шт):