Группировка параметров товаров по группам в SQL

При работе с интернет-магазинами часто возникает задача вывести характеристики товара, сгруппированные по категориям (например, «Габариты», «Производитель»). В этой статье разберём, как написать правильный запрос на PostgreSQL для объединения параметров и их значений в JSON-массивы, сгруппированные по группам.

Исходная структура таблиц

У нас есть четыре связанные таблицы:

• amqp_param_groups - группы параметров (uuid, name);
• amqp_trade_parameters - параметры товаров (uuid, name, group_id);
• amqp_trade_parameters_values - значения параметров (uuid, value, param_id);
• amqp_trade_parameters_assignments - сводная таблица, связывающая товар с параметрами и их значениями.

Цель - для каждого товара получить массив групп, внутри которых перечислены параметры и их значения.

Проблема первоначального запроса

Исходный запрос использовал GROUP BY pa.param, что приводило к разбивке по отдельным параметрам, а не по группам. В результате каждая строка содержала только один параметр и одно значение. Чтобы объединить все параметры одной группы в один массив, нужно группировать по group_id и применять агрегацию JSON.

Правильный SQL-запрос для группировки

Вот корректный запрос, который выдаёт желаемый результат:

SELECT
    g.uuid AS group_id,
    jsonb_agg(DISTINCT jsonb_build_object('g_uuid', g.uuid, 'g_name', g.name)) AS g_name,
    jsonb_agg(DISTINCT jsonb_build_object('p_uuid', p.uuid, 'p_name', p.name)) AS p_name,
    jsonb_agg(DISTINCT jsonb_build_object('pv_uuid', pv.uuid, 'pv_value', pv.value)) AS pv_value
FROM amqp_trade_parameters_assignments AS pa
JOIN amqp_trade_parameters AS p ON p.uuid = pa.param
JOIN amqp_trade_parameters_values AS pv ON pv.uuid::text = pa.value::text
JOIN amqp_param_groups AS g ON g.uuid = p.group_id
WHERE pa.trade = :trade
GROUP BY g.uuid, g.name
ORDER BY g.name;

Этот запрос группирует данные по g.uuid (идентификатору группы), собирая все параметры и их значения в JSON-массивы с помощью jsonb_agg. DISTINCT исключает дубликаты, если один и тот же параметр привязан к нескольким значениям.

Альтернативный вариант с вложенными JSON

Если нужно получить более сложную структуру, например, массив объектов с параметрами и их значениями, можно использовать:

SELECT
    g.uuid AS group_id,
    jsonb_build_object('g_uuid', g.uuid, 'g_name', g.name) AS g_name,
    jsonb_agg(
        jsonb_build_object(
            'p_uuid', p.uuid,
            'p_name', p.name,
            'values', jsonb_agg(jsonb_build_object('pv_uuid', pv.uuid, 'pv_value', pv.value))
        )
    ) AS parameters
FROM amqp_trade_parameters_assignments AS pa
JOIN amqp_trade_parameters AS p ON p.uuid = pa.param
JOIN amqp_trade_parameters_values AS pv ON pv.uuid::text = pa.value::text
JOIN amqp_param_groups AS g ON g.uuid = p.group_id
WHERE pa.trade = :trade
GROUP BY g.uuid, g.name, p.uuid, p.name
ORDER BY g.name;

Почему это работает?

Ключевое отличие - группировка по g.uuid (или по g.uuid, g.name). Это объединяет все строки, относящиеся к одной группе. Функция jsonb_agg собирает значения из разных строк в один JSON-массив. Использование jsonb_build_object позволяет сформировать правильную структуру ключ-значение.

Советы по оптимизации

• Убедитесь, что на колонках uuid, param_id, group_id есть индексы - это ускорит JOIN.
• Если данных много, избегайте DISTINCT внутри агрегации, если в этом нет необходимости.
• Для вывода в PHP можно использовать json_decode для преобразования JSON-строк в массивы.