I have a JPA query and following entities:
a product can either have a person or a team. Not both. Now i want a query that finds all products for a specific person, as single person AND ALSO as as team member (so the result list should contain both cases). So I have my query structured as follows:
public List<Product> getProductsFor(Person person){
CriteriaBuilder cb = getEntitymanager().getCriteriaBuilder();
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root<Product> root = query.from(Root.class);
Join<Product, Team> teamJoin = root.join(Product_.team, JoinType.LEFT);
Predicate asSinglePerson = cb.equal(root.get(Product_.person), person);
Predicate asTeamMember = cb.isMember(persons, teamJoin.get(Team_.members));
Predicate teamOrSingle = cb.or(asTeamMember, asSinglePerson);
query.where(teamOrSingle);
return getEntityManager().createQuery(query).getResultList();
}
However, EclipseLink is building following (Postgre)SQL code from above:
SELECT t1.*
FROM
Product t1 LEFT OUTER JOIN team t2 ON (t2.ID = t1.team_ID),
team_person t3,
person t4
WHERE
(t1.person_ID = 1 OR t4.ID = 1)
AND t3.team_ID = t2.ID
AND t4.id = t3.person_ID
Some parenthesis were omitted, but the query is the same. This query returns only products where the person is member of a team, but not the ones that the person is responsible of as a single person.
What I would want is something like this:
SELECT t1.*
FROM
Product t1 LEFT OUTER JOIN team t2 ON (t2.ID = t1.team_ID),
team_person t3,
person t4
WHERE
t1.person_ID = 1
OR (t3.person_ID = t4.ID AND t3.team_ID = t2.ID AND t4.ID = 1)
Since the OR-Operator is the most-outer one in my query, why isn't it in the resulting SQL?
Seperating the query into 2 parts (one for the team part, one for the single part) is not an option since we have pagination and filtering built into this one query (which doesn't influence the result, so I omitted it in the listing).
Thanks for your help.
The 'and' clauses are not related to your 'or' statement in any way, and are there because of the join semantics you are using to build your query. A 'get' on a relationship is defined using an inner join and is required to filter results, and these joins can be done within the From clauses or appended to the where clause.
You will need to explicitly use a Left outer join on the team.members relationship to get the query results you are looking for, similar to how you've done the product.team relationship.