Как получить данные из EAV (entity–attribute–value)?

Question

[Станислав]

Привет! База: Postgres 13. Структура бд
https://www.db-fiddle.com/f/fXUsP2utgLneDKcASY3zA5/2

Пытаюсь получить данные (в идеале только с атрибутом указанным в условие where)

SELECT
    p.id,
    p.name,
    json_agg(
      json_build_object(
        'id', eav_attr.id,
    	'name', eav_attr.name,
        'text', eav_text.val,
        'real', eav_real.val,
        'boolean', eav_boolean.val
      )
    ) AS characteristics,
    COUNT(*) OVER() AS total
FROM product p
JOIN category c ON c.id = p.category_id
LEFT JOIN eav_entity eav_e ON eav_e.category_id = c.id
LEFT JOIN eav_attribute eav_attr ON eav_attr.id = eav_e.eav_attribute_id
LEFT JOIN eav_val_text eav_text ON eav_text.product_id = p.id
LEFT JOIN eav_val_real eav_real ON eav_real.product_id = p.id
LEFT JOIN eav_val_boolean eav_boolean ON eav_boolean.product_id = p.id
WHERE eav_text.attribute_id = 2
GROUP BY    
  p.id,
  p.name

но получается это characteristics (последнее значение перезаписывает предыдущие значения своего типа)

[
    {
        "id": 1,
        "name": "Описание",
        "text": "свойства продукта",
        "real": 100.2,
        "boolean": null
    },
    {
        "id": 2,
        "name": "Свойства",
        "text": "свойства продукта",
        "real": 100.2,
        "boolean": null
    },
    {
        "id": 3,
        "name": "Цена",
        "text": "свойства продукта",
        "real": 100.2,
        "boolean": null
    }
]

Т.е. в идеале в characteristics хотелось бы получить

[
    {
        "id": 2,
        "name": "Свойства",
        "val": "свойства продукта"
    }
]

ну или хотя бы так

[
    {
        "id": 1,
        "name": "Описание",
        "text": "описание продукта",
        "real": null,
        "boolean": null
    },
    {
        "id": 2,
        "name": "Свойства",
        "text": "свойства продукта",
        "real": null,
        "boolean": null
    },
    {
        "id": 3,
        "name": "Цена",
        "text": null,
        "real": 100.2,
        "boolean": null
    }
]

Comments

[Akina]

WHERE eav_text.attribute_id = 2
Это условие должно быть в ON. Либо все джойны, начиная с этого - INNER.

[Станислав]

Akina, спасибо! Но если в ON перадавать 2 параметра, то так же последние значения перезаписывают предыдущие

LEFT JOIN eav_entity eav_e ON eav_e.category_id = c.id AND (eav_e.eav_attribute_id = 2  OR eav_e.eav_attribute_id = 1)
LEFT JOIN eav_attribute eav_attr ON eav_attr.id = eav_e.eav_attribute_id
LEFT JOIN eav_val_text eav_text ON eav_text.product_id = p.id AND (eav_text.attribute_id = 2 OR eav_text.attribute_id = 1)
LEFT JOIN eav_val_real eav_real ON eav_real.product_id = p.id  AND (eav_real.attribute_id = 2 OR eav_real.attribute_id = 1)
LEFT JOIN eav_val_boolean eav_boolean ON eav_boolean.product_id = p.id  AND (eav_boolean.attribute_id = 2  OR eav_boolean.attribute_id = 1)

https://www.db-fiddle.com/f/fXUsP2utgLneDKcASY3zA5/3

Через INNER вообще ничего не находит.

JOIN category c ON c.id = p.category_id
LEFT JOIN eav_entity eav_e ON eav_e.category_id = c.id
LEFT JOIN eav_attribute eav_attr ON eav_attr.id = eav_e.eav_attribute_id
INNER JOIN eav_val_text eav_text ON eav_text.product_id = p.id
INNER JOIN eav_val_real eav_real ON eav_real.product_id = p.id
INNER JOIN eav_val_boolean eav_boolean ON eav_boolean.product_id = p.id

https://www.db-fiddle.com/f/fXUsP2utgLneDKcASY3zA5/4

[Gennady S]

Станислав, а что делает Ваш запрос без GROUP ? Он выдает три одинаковые строки, где различается только атрибут. Для каждого продукта выбираются все атрибуты, и для каждого из атрибутов выбираются три типа значений, уже относящиеся к продукту. Это изначально неверный подход. А если у Вас три строковых значения, одно булево и пять с плавающих запятой, в какую структуру это можно объединить? Ответ, никак. Это относится не к объекту с тремя значениями по типу, а к строкам. Тут мог бы выручить JSON-объект вида: { имя атрибута, тип значения, значение }. Замените избыточное и лишнее (!) p.name в группировке на eav_attr.id , уберите json_agg и получите более правдоподобный результат.

[Gennady S]

Станислав, есть несколько путей. Например, либо Вы получаете отдельные строки с группировкой по продукту и атрибуту, с избыточностью: продукт / атрибут / строковое значение / булево значение / численное значение. И на клиенте решаете, какое значение заполнено (ну или можно собирать в JSON с помощью CASE, завернуть с полем типа). Либо изначально храните значение атрибута в JSON, сразу указывая тип и значение, соответствующее типу. Нужна будет одна таблица для значений: атрибут / тип значения / значение JSON.

[Gennady S]

Станислав, а еще, в Вашей структуре, типа данных должен быть отнесен к eav_attribute. Цена всегда – число с плавающей запятой, Описание – всегда строка.