JPA Criteria API Predicates for multiple requirements in child collection

Question

Consider the following scenario:

public class Parent {
    private List<Child> childs;
}

public class Child {
    private Target target;
    private Value value;
}

public class Target {
    private Long id;
}

public class Value {
    private Long id;
}

For every determined Target-object Parent-object will have Child-object that has Value-object.

I get filter in format of HashMap<Long>, List<Long>> where key is Target-object's id and value is list of desired Value-object id's. What I need to get with JPA Criteria API is for example all Parents with following conditions:

has child with targetId 1 with valueId in(1,2,3)
AND has child with targetId 2 with valueId in(1,4)

So I need to exlude the results that dosen't meet all the requirements. Is there a way to achieve this with JPA Criteria API. I have tried the following:

List<Predicate> predicates = new ArrayList<>();
...
List<Predicate> subPredicates = new ArrayList<>();
Path<Child> child = root.join(Parent_.child, JoinType.LEFT);
for (Map.Entry<Long, List<Long>> entry : map.entrySet()) {
    Long targetId = entry.getKey();
    List<Long> valueIds = entry.getValue();
    subPredicates.add(cb.and(cb.equal(child.get(Child_.target), targetId), child.get(Child_.value).in(valueIds)));
}
predicates.add(cb.and(subPredicates.toArray(new Predicate[subPredicates.size()])));

But obviously it doesn't work. Is this even possible with this kind of class structure?. Can anyone help me with this?

Your approach seems fine. What's wrong with it? Include errors and/or query output. If it doesn't work, I'd try with a list of subqueries like explained here: http://stackoverflow.com/a/4668015/870122 — perissf, Mar 10 '16 at 08:40
Your question is confused. Are you passed a Map> or are you passed a Map>, ?>. If the later, what is the value? — K.Nicholas, Mar 11 '16 at 14:53

score 1 · Answer 1 · answered Jul 04 '19 at 06:16

I would try to create two join, both with the child table, and setting a condition in each join, so if one of the two is not met, no result would appear.

For example:

Path<Child> childCondition1 = root.join(Parent_.child, JoinType.INNER);
Path<Child> childCondition2 = root.join(Parent_.child, JoinType.INNER);

List<Predicate> predicates = new ArrayList<>();
predicates.add(cb.and([condition1 over childCondition1],[condition2 over childCondition2,]));

Another option (with JPA 2.1 in advance, probably more eficient):

Path<Child> childCondition1 = root.join(Parent_.child, JoinType.INNER);
    childCondition1.on([condition1 over childCondition1]);
Path<Child> childCondition2 = root.join(Parent_.child, JoinType.INNER);
    childCondition2.on([condition2 over childCondition2]);

JPA Criteria API Predicates for multiple requirements in child collection

1 Answers1