CODEWITHSUNDEEP
sqljpapredicatecriteria-api
Solver42
Solver42

Reputation: 166

LEFT JOIN with AND operator using Criteria API

I am trying to build dynamic queries using JPA and Criteria API but without metamodels.

With the tables:

Foo:
ID
1
2
3
4

Bar: 
ID   FooID   Number
1    2       44
2    2       55
3    3       55

I want to retrieve all Foo entities where no matching Bar has Number 44. (Expecting Foo 1, 3, 4)

The SQL should look something like this:

select distinct *
from Foo foo0_
left join Bar bar0_ on foo0_.ID=bar0_.FooID and bar0_.Number=44
where bar0__.id is null;

My code looks something like this:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Foo> cq = cb.createQuery(Foo.class);
Root<Foo> fooRoot = cq.from(Foo.class);
cq.select(fooRoot).distinct(true);

List<Predicate> allPredicates = new ArrayList<Predicate>();
List<Predicate> orPredicates = new ArrayList<Predicate>();
List<Predicate> andPredicates = new ArrayList<Predicate>();
andPredicates.add(cb.equal(fooRoot.join("bars", JoinType.LEFT).get("number"), 44));
andPredicates.add(cb.isNull(fooRoot.join("bars", JoinType.LEFT).get("ID")));
orPredicates.add(cb.and(andPredicates.toArray(new Predicate[andPredicates.size()])));
allPredicates.add(cb.or(orPredicates.toArray(newPredicate[orPredicates.size()])));

cq.where(allPredicates.toArray(new Predicate[allPredicates.size()]));
TypedQuery<Foo> query = em.createQuery(cq);
query.getResultList();

But that generates this SQL:

select distinct *
from Foo foo0_
left outer join Bar bar1_ on foo0_.id=bar1_.FooID
left outer join Bar bar2_ on foo0_.id=bar2_.FooID
where bar1_.Number=44 and (bar2_.id is null);

and returns no Foo.

Any ideas? Thank you!

Upvotes: 2

Views: 6996

Answers (1)

Neil Stockton
Neil Stockton

Reputation: 11531

You don't specify which JPA implementation this is for, and clearly the SQL may differ for each.

That aside, your joining is wrong. Each join call will do a JOIN, and you only want 1 join if what you write is correct. Also you can make use of JPA 2.1 "ON" conditions on the join (rather than putting it in the WHERE).

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Foo> cq = cb.createQuery(Foo.class);
Root<Foo> fooRoot = cq.from(Foo.class);
cq.select(fooRoot).distinct(true);

Join barJoin = fooRoot.join("bars", JoinType.LEFT);
Predicate onCond = cb.equal(barJoin.get("number"), 44);
barJoin.on(onCond);

Then do the same as your question except using the barJoin; no idea what those AND/OR conditions are trying to do - your suggested SQL has none of that.

Upvotes: 5

Related Questions