java - 使用 Hibernate Criteria 自加入

Question

无法找到一种方法来为这种伪搜索索引表上的条件添加排序依据:

----------------------------------
| id | person_id | field | value |
----------------------------------
| 1  | 1         | fname  | John |
| 2  | 1         | lname  | Smith|
| 3  | 1         | zip    | 3000 |
| 4  | 2         | fname  | John |
| 5  | 2         | lname  | Doe  |
| 6  | 2         | zip    | 3000 |
----------------------------------

search_index 表是人员实体的索引，它可以具有任意数量的任意类型的属性(当然，这是现实生活问题的简化示例)。一个“人”可以设置 fname，另一个“人”则不能，依此类推。然后客户端应用程序发送通用搜索请求，例如((fname=John OR fname=Karl) AND lname=Smith and zip=3000) - 这种东西。因此该系统相当通用 - search_index 中的可搜索字段列表和任何类型的 boolean 搜索查询。 orderby 也是如此 - 可以是任何可搜索字段。

在 SQL 中我会做类似的事情:

SELECT si1.person_id from search_index si1
LEFT OUTER JOIN search_index si2 ON si1.id = si2.id
WHERE si2.field='lname'
AND si1.person_id in (select person_id from search_index where field = 'zip' and answer = '3000')
ORDER BY si2.value

按“lname”对找到的结果进行排序。

使用 HQL 可能是可行的，但搜索条件是根据搜索字符串动态构建的，并且 Hibernate Criteria API 使其比动态 HQL 构建要容易得多。

Answer 1

1. 为什么要 LEFT JOIN 同一个表，然后在 WHERE 子句中使用 JOINED 表？这相当于 INNER JOIN。

2. 当您已经连接两个表时，为什么还要进行额外的子选择。

3. 为什么还要通过 ID 来连接两个表？在您的这个查询中，您连接相同的行(因为您使用表 ID 作为连接条件)。

此查询是您要查找的内容吗？

SELECT si1.person_id 
from search_index si1
LEFT OUTER JOIN search_index si2 ON si1.person_id = si2.person_id AND si2.field='lname' 
WHERE si1.field = 'zip' and si1.answer = '3000'
ORDER BY si2.value

HQL 和 Criteria 查询只能在导航现有关联时为您提供帮助(自连接则不然)。因此在这种情况下您应该使用 native 查询。

现在，回到您的查询。您可以删除 JOIN 并将其写为:

SELECT si1.person_id 
from search_index si1
WHERE si1.field='lname'
AND si1.person_id in (select person_id from search_index where field = 'zip' and answer = '3000')
ORDER BY si1.value

在这种情况下，您可以编写 HQL 查询:

select p.id
from SearchIndex si
join si.person p
where 
   si.field = :field1 and
   p.id in (select si2.person.id from SearchIndex si2 where si2.field = : field2 and answer = :answer)
order by si.value

更新

如果“lname”字段仅对排序有用，那么您需要将子查询移至主查询中，并将“lname”移至联接中:

SELECT si1.person_id 
from search_index si1
where si1.field = 'zip' and si1.answer = '3000'
LEFT OUTER JOIN search_index si2 ON si1.person_id = si2.person_id AND si2.field='lname' 
ORDER BY si2.value

由于自连接语法，这需要 native 查询。