JPA 基于集合元素匹配进行选择，或空集合

Question

我有一个单位，它有一个单位类型和一个组织。我有一份合同，其中包含单位类型和组织的集合。我想选择单元，以及在其集合中具有单元的 UnitType 的合约，或者如果没有匹配项，则选择具有空 UnitType 集合的 UnitType。

澄清一下，在每种情况下我都想选择单位。如果在合同的 UnitTypes 集合中存在具有单位指定类型的合同，那么我想选择该合同。如果这样的合同不存在，那么我想选择根本没有 UnitTypes 的合同。换句话说，我想要适用于该单位类型的合同，但如果它不存在，我将采用与单位类型无关的合同作为默认值。

示例 1：

Unit A has type XYZ.
Contract B has types [ ABC, DEF ]
Contract C has types []

在本例中，我将选择单位和合同 C，因为 B 的类型不匹配。

示例 2：

Unit A has type XYZ
Contract B has types [XYZ, ABC]
Contract C has types []

在本例中，我会选择合同 B，因为它与单位类型匹配。

以下查询适用于示例 2，但不适用于示例 1。

SELECT NEW mypackage.view.MyAggregateView( 
    u
    , MAX(sc.serviceDate)
    , c.survey.key )
FROM Contract c
    , Unit u 
    LEFT JOIN u.serviceCalls sc 
    WHERE c.organization.key = u.organization.key 
    AND u.organization.key = :organizationKey 
    AND ((u.unitType MEMBER OF c.unitTypes) 
        OR (c.unitTypes IS EMPTY))
    GROUP BY u, c.survey.key

如何在这两种情况下执行此操作并确保获得正确的合同？

又一个例子：

我最近又遇到了这个问题。我有一个区域，其中包含邮政编码的集合以及可选的组织集合。我还有一个单位，它与一个组织有一对一的联系，并且有一个邮政编码。我想获取该地区邮政编码内的所有适当单位。如果该地区没有组织，那么我应该获取邮政编码内的所有单位，否则我应该只获取其组织与该地区内指定的组织之一相匹配的单位。

这是我的查询

Select u.key, u.organization.key
from Unit u, Region r
where r.key = -1
and u.address.postalCode member of r.zips
and r.organizations is empty

此查询为我提供了所有预期结果。以下查询不会以任何方式限制结果集，因为它只是添加一个 OR，但不会给我任何结果。

Select u.key, u.organization.key
from Unit u, Region r
where r.key = -1
and u.address.postalCode member of r.zips
and ((r.organizations is empty) OR (r.organizations is not empty and u.organization member of r.organizations))

我正在使用 eclipse link 2.0.1 来对抗 postgres 9。我也使用 eclipselink 2.2.0 得到了相同的结果。

Answer 1

您的主要问题是“来自单位 u，合同 c ... 其中 c.organization.key = u.organization.key ”是单位和合同之间的内部联接。根据定义，如果没有匹配的合约，则永远不会返回结果。在条件的“或 c.unitTypes 为空”一半甚至有机会触发之前，行就会从结果集中删除。

还有第二个更微妙的问题，即如果您可能有多个合同引用相同的单位类型，则可能会在查询中返回重复的单位。不过，这种加入可能是无法避免的，因为您试图同时获得合同和单位，而不仅仅是单位。 （否则您可以使用存在/不存在而不是连接。）

现在，听起来您确实无法通过单个连接来完成您想要的逻辑。像“如果存在的话就采取某种东西，否则采取其他东西，但不是两者”之类的条件逻辑需要多个联接，然后需要 case/when 逻辑来选择使用哪一个。

此时我想知道您是否最好进行两个单独的查询。根据常见情况和应用程序的整体架构，运行两个简单查询并进行两次往返的性能甚至可能比进行复杂查询以避免往返更好。即使性能受到很小的影响，我也更喜欢这样的可读性和可维护性。

也就是说，如果我必须在 SQL 中执行此操作，并且它绝对必须在单个查询中，那么它是可行的，但一点也不漂亮：

  select u.*, c.* from (
    select unit_id, 
         coalesce(matching.contract_id, non_matching.contract_id) 
         as contract_id
      from Unit u
        **left** join Contract matching on ([match on unit type]) 
        left join Contract non_matching on ([unit types empty])
   ) subquery join Unit u on subquery.unit_id = u.unit_id 
     join Contract c on subquery.contract_id = c.contract_id

要么那样，要么将两个查询与 UNION ALL 组合在一起，并在返回后停止第一个结果。

然而，这两个选项都不能转换为 JPA/JPQL。 JPQL 没有 UNION 运算符，并且 JPQL 不支持任意条件的外连接，仅支持像使用“left join u.serviceCalls”那样导航关系。我不认为你可以将笛卡尔“来自合同 c，单元 u”变成外连接。

因此，对于 JPQL，我很遗憾地说没有好的方法可以在单个查询中获得您想要的内容。