Фильтрация запроса в postgreSQL (связь многие ко многим)

Question

Входные данные: PostgreSQL. Есть 3 таблицы: products, colors, products_to_colors. Связь многие ко многим - каждый продукт может иметь несколько цветов. Каждый цвет может быть у нескольких продуктов. Связь между цветами и продуктами реализована с помощью 3 таблицы products_to_colors.

Поля в таблицах:

products - два поля: id, name (остальные не имеют значение, упрощаем задачу =) )
colors - 2 поля: id, color (в color указывается сам цвет, например, зелёный)
products_to_colors - 3 поля: id, color_id, product_id

Я получаю данные из таблиц с помощью такого запроса:

SELECT products.*, string_agg(colors.color, ',') AS colors
FROM products
    LEFT JOIN products_to_colors ON products.id = products_to_colors.product_id
    LEFT JOIN colors ON colors.id = products_to_colors.color_id
GROUP BY products.id

Задача состоит в том, чтобы получать из БД только те продукты, у которых есть определённый цвет.

Например: я хочу получить только те продукты, у которых доступен цвет с id 1:

SELECT products.*, string_agg(colors.color, ',') AS colors
FROM products
    LEFT JOIN products_to_colors ON products.id = products_to_colors.product_id
    LEFT JOIN colors ON colors.id = products_to_colors.color_id
WHERE products_to_colors.color_id=1
GROUP BY products.id

Проблема. Товары возвращаются правильно, только те, у которых доступен цвет с id 1, но, в колонке colors указывается только цвет с id 1. Хотя у товара так же есть цвета с id 2, 3, 4, но они отсутствуют в колонке colors.

Как мне исправить мой запрос к БД, чтобы получить желаемый результат?

Answer 1

Для отбора продуктов, имеющих заданную характеристику, можно:

Добавить ещё одну копию таблицы для отбора:

SELECT products.*, string_agg(colors.color, ',') AS colors
FROM products
    LEFT JOIN products_to_colors ON products.id = products_to_colors.product_id
    LEFT JOIN colors ON colors.id = products_to_colors.color_id
    JOIN products_to_colors ptc ON products.id = ptc.product_id AND ptc.color_id=1
GROUP BY products.id

Посчитать для продукта количество записей с заданной характеристикой

SELECT products.*, string_agg(colors.color, ',') AS colors
FROM products
    LEFT JOIN products_to_colors ON products.id = products_to_colors.product_id
    LEFT JOIN colors ON colors.id = products_to_colors.color_id
GROUP BY products.id
HAVING SUM(CASE WHEN products_to_colors.color_id=1 THEN 1 END) > 0

БЛОГ НА HUSL

Фильтрация запроса в postgreSQL (связь многие ко многим)

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