SQL：根据其属性查找项目

Question

在PostgreSQL上，我有一个表项目和一个table item_attribute。因此，项目可以具有多个属性。

表：项目

ID	名称
1	A
2	B
3	C

表：Item_Attribute

ID	ITEM_ID	名称	值
1	1	FOO	bar
2	1	size	m
3	1	country	de
4	2	foo	baz
5	2	size	m
6	2	Country	de
7	3	其他	测试
8	3 3	尺寸	m
9	3	Country	现在

，我需要找到与给定属性的名称/值对匹配的项目

，因此，假设我想找到带有 country 带有值 de de country 属性的项目代码>和 size 带有值 m 结果集应为

ID	名称
1	A
2	B，

如果我想找到带有 country> country 的项目值 en 和 size 带有值 m 结果集应

ID	名称
3	C。

为

Answer 1

使用聚合方法，我们可以尝试：

SELECT i.id, i.name
FROM item i
INNER JOIN item_attribute ia
    ON ia.item_id = i.id
GROUP BY i.id, i.name
HAVING COUNT(*) FILTER (WHERE ia.name = 'country' AND ia.value = 'DE') > 0 AND
       COUNT(*) FILTER (WHERE ia.name = 'size' AND ia.value = 'M') > 0;

上述使用条件聚合以中等大小的饮食项目。

Answer 2

您可以在“ item_attribute ”表中找到满足您条件的所有“ item_id ”之间的交点，然后匹配“ item “表。

WITH filtered_ids AS (
    SELECT item_id FROM item_attribute WHERE name  = 'country' AND value = 'DE'
    INTERSECT
    SELECT item_id FROM item_attribute WHERE name  = 'size'    AND value = 'M'
)
SELECT * 
FROM item
WHERE id IN (SELECT * FROM filtered_ids)

您可以在“ filtered_ids ” CTE中堆叠尽可能多的条件。

检查演示在这里。

Answer 3

一个选项是将属性汇总到JSON值中，并使用功能非常强大的要找到这些项目，

select itm.*
from item itm
where exists (select null
              from item_attribute att
              where att.item_id = itm.id
              group by item_id
              having jsonb_object_agg(name, value) @> '{"country": "DE", "size": "M"}'
              )

我不确定这比计数的传统方法更快还是慢（请参阅Tim Biegeleisen的答案）。不过，这更灵活。

根据数据分配，对所有项目进行一次聚合可能更快：

select itm.*
from item itm
  join (
    select item_id, jsonb_object_agg(name, value) as attributes
    from item_attribute att
    group by item_id
    having jsonb_object_agg(name, value) @> '{"country": "DE", "size": "M"}'
  ) atr on atr.item_id = itm.id

这也使结果可以在结果的列中显示所有属性（作为JSON）。