一对多查询元组

Question

我有一个与实体 2 具有一对多关系的实体 1。实体 2 存储属性并具有字段"键"和"值"。

现在我需要查询所有具有某些键/值组合（AND 逻辑）的 Entity1

SELECT e1 FROM Entity1 e Join e.props e2 
WHERE 
e2.key = :key1 and e2.value = :value1
And
e2.key = :key2 and e2.value = :value2 -- wont work

Answer 1

一种容易推广到更多键/值对的方法是使用条件聚合：

SELECT entity_id
FROM e.props
GROUP BY entity_id
HAVING SUM(CASE WHEN e2.key = :key1 and e2.value = :value1 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN e2.key = :key2 and e2.value = :value2 THEN 1 ELSE 0 END) > 0;

对于更多条件，您可以向having添加更多子句。

Answer 2

JPA 理解存在：

SELECT e1 FROM Entity1 e1 
WHERE exists (select 1 from Entity2 e2 
              where e2.entity1.id = e1.id
                and e2.key = :key1 and e2.value = :value1)
  AND exists (select 1 from Entity2 e2 
              where e2.entity1.id = e1.id
                and e2.key = :key2 and e2.value = :value2);

Answer 3

select e1.* 
from entity1 e1
join entity2 e21 on e21.e1_id = e1.id and e21.key = :key1 and e21.value = :value1
join entity2 e22 on e22.e1_id = e1.id and e22.key = :key2 and e22.value = :value2

Answer 4

使用这个

SELECT some_filed FROM Entity1 AS e1 Join e1.props AS p 
WHERE 
(p.key = :key1 and p.value = :value1)
and
(p.key = :key2 and p.value = :value2);