sql - 如何在类似 EAV 的设计中查询多条记录

Question

我有两张 table 。一个具有一些对象，如用户/人，另一个具有额外的属性。

我创建了一个演示集来测试它。不要被里面的数据搞糊涂了。这样可以轻松地将其插入查询控制台。

SELECT * FROM
(
    SELECT * FROM (VALUES 
    ('object_1'), 
    ('object_2'), 
    ('object_3'),
    ('object_4')) AS objects (id)
) AS o
LEFT JOIN (
    SELECT * FROM (VALUES 
        ('object_1', 'name', 'john'), 
        ('object_1', 'lastname', 'joans'),
        ('object_2', 'name', 'john'),
        ('object_2', 'lastname', 'johnson'),
        ('object_3', 'name', 'joan'),
        ('object_3', 'lastname', 'johnson')
    ) AS properties (o_id, property, value)
) AS p ON o.id = p.s_id;

我想找到名称为“john”且姓氏为“johnson”的对象。正确答案当然是“object_2”我该怎么做？

通过以下我没有得到任何结果(显然):

WHERE 
    (p.property = 'name' AND p.value = 'john') 
AND
    (p.property = 'lastname' AND p.value = 'johnson')
;

通过以下我得到 3 个对象，

WHERE 
    (p.property = 'name' AND p.value = 'john') 
OR
    (p.property = 'lastname' AND p.value = 'johnson')
;

本来想的方向是加入属性两次。但是你必须为每个新属性重复这个技巧。

SELECT * FROM
(
    SELECT * FROM (VALUES 
    ('object_1'), 
    ('object_2'), 
    ('object_3'),
    ('object_4')) AS objects (id)
) AS o
LEFT JOIN (
SELECT * FROM (VALUES 
    ('object_1', 'name', 'john'), 
    ('object_1', 'lastname', 'joans'),
    ('object_2', 'name', 'john'),
    ('object_2', 'lastname', 'johnson'),
    ('object_3', 'name', 'joan'),
    ('object_3', 'lastname', 'johnson')
) AS properties (s_id, property, value)
) AS p1 ON o.id = p1.s_id
LEFT JOIN (
SELECT * FROM (VALUES 
    ('object_1', 'name', 'john'), 
    ('object_1', 'lastname', 'joans'),
    ('object_2', 'name', 'john'),
    ('object_2', 'lastname', 'johnson'),
    ('object_3', 'name', 'joan'),
    ('object_3', 'lastname', 'johnson')
) AS properties (s_id, property, value)
) AS p2 ON o.id = p2.s_id
WHERE 
    (p1.property = 'name' AND p1.value = 'john') 
AND
    (p2.property = 'lastname' AND p2.value = 'johnson')
;

(是的，丑陋的重复表格，但很方便？)

我知道数据库设计有点奇怪，但这是因为某些属性应该是可扩展的。我知道这是一种模式。( https://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model )

我也考虑过 GROUP BY，HAVING，但不确定该怎么做。

Answer 1

你可以使用 exists

SELECT * 
  FROM objects AS o
 WHERE EXISTS
         ( SELECT 1 
             FROM properties AS p
            WHERE p.property = 'lastname' 
              AND p.value = 'johnson'
              AND o.object = p.object
         )
    AND EXISTS
         ( SELECT 1 
             FROM properties AS p
            WHERE p.property = 'name' 
              AND p.value = 'john'
              AND o.object = p.object
         )