JPA Select based on collection element match, or empty collection

Question

I have a Unit which has a UnitType and an Organization. I have a Contract which has a collection of UnitTypes and an Organization. I would like to select the unit, and either the contract that has the Unit's UnitType in its collection, OR the UnitType that has an empty UnitType collection if there is no match.

To clarify, in every case I want to select the Unit. If there exists a Contract which has the unit's specified type in the contract's collection of UnitTypes then I would like to select that contract. If such a contract doesn't exist, then I would like to select the Contract that has no UnitTypes at all. In other words, I would like the contract that applies to this unit's type, but if it doesn't exist I'll take the contract that is unit type agnostic as the default.

Example 1:

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

in this case I would select the unit and Contract C because B has no match on type.

Example 2:

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

In this case I would select Contract B because it matches the type of the Unit.

The following query works for Example 2, but not for Example 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

How do I make this work in both cases and ensure I get the correct Contract?

Yet Another Example:

I've recently run into this again. I have a region, which has a collection of zip codes and optionally a collection of organizations. I also have a Unit, which has a 1-1 to an organization and has a single zip code. I want to get all the appropriate units within the region's zip codes. If the region has no organizations then I should get all units within the zip codes, otherwise I should only get the units that have an organization that matches one of the organizations specified within the region.

Here's my query

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

This query gets me all of my expected results. The following query, which should in no way restrict the result set since it's only adding an OR, gives me no results.

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))

I'm using eclipse link 2.0.1 against postgres 9. I also got the same result with eclipselink 2.2.0.

Answer 1

Your main issue is that "from Unit u, Contract c ... where c.organization.key = u.organization.key " is an inner join between Unit and Contract. By definition, this will never return a result if there is no matching contract. The rows get dropped from the result set before the "or c.unitTypes is empty" half of the condition even has a chance to fire.

There is a second more subtle issue which is that, if you potentially have more than one contract referencing the same unit type, you could get duplicate units back in your query. This join may not be avoidable, though, since you're trying to get both the contracts and units, not just the units. (Otherwise you could use exists/not exists instead of joins.)

Now, it sounds like you really can't do the logic you want with a single join. Conditional logic like "take something if it exists, else take something else, but not both" would require multiple joins and then case/when logic to select which one to use.

At this point I'd wonder if you'd be better off with two separate queries. Depending on the common case and the overall architecture of your application, your performance might even be better running two simple queries and making two round trips, than making a complex query to avoid a round trip. Even if you take a small performance hit, I'd almost prefer that for readability and maintainability.

That said, if I had to do this in SQL, and it absolutely had to be in a single query, it would be doable but not at all pretty:

  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

Either that, or combine two queries together with a UNION ALL, and stop after returning the first result.

Neither option translates to JPA/JPQL, however. JPQL has no UNION operator, and JPQL does not support outer joins on arbitrary criteria, only on navigating relationships as you did with "left join u.serviceCalls". I don't think you can turn a Cartesian "from Contract c, Unit u" into an outer join.

So for JPQL, I'm sad to say there's no good way to get what you want in a single query.