Does JPQL have a "SUBSET" function

Question

I have a simple data model as follows:

• Entity 1: EmployeeAsset. This has a reference to an Employee object.
• Entity 2: EmployeeJobPosition. This also has a reference to an Employee. It also has a reference to a JobPosition (this is another entity class) object which in turn contains a collection of OrganisationUnitEntry (this is another entity class) objects.

As a parameter, I have a collection of OrganisationUnitEntry objects that the user selected (let's call this ouEntries). I need a query that would return all EmployeeAsset objects that are related to a JobPosition that has an OrganisationUnitEntry collection on it that is a superset of ouEntries (i.e. ouEntries is a subset of ...jobPosition.organisationUnitEntries).

So the query could be built dynamically with something like:

SELECT ea
FROM EmployeeAsset ea, EmployeeJobPosition ejp
WHERE ea.employee = ejp.employee
AND :orgUnitEntry1 MEMBER OF ejp.jobPosition.organisationUnitEntries
...
AND :orgUnitEntryN MEMBER OF ejp.jobPosition.organisationUnitEntries

(where orgUnitEntry1 - N are just the elements in ouEntries).

However a subset type function would be a lot neater and then I could put this in a static / named query which I usually prefer. Something like

...
AND :ouEntries SUBSET OF ejp.jobPosition.organisationUnitEntries

Any ideas about how best to write such a query?

Answer 1

Can you use an IN?

SELECT ea
FROM EmployeeAsset ea, EmployeeJobPosition ejp join ejp.jobPosition.organisationUnitEntries o
WHERE ea.employee = ejp.employee
AND o IN :ouEntries

Depending on your JPA provider, you may need to compare the ids with the IN instead of the objects. EclipseLink should allow you to compare objects (>=2.4).