JPA Criteria Builder OneToMany Restrictions

Question

I have a Parent with a OneToMany associations with a Child Table.

I'm trying to write a query with CriteriaBuilder to restrict the results returned from the Child table.

I'm adding a Predicate, something like

cb.equal(parent.get("children").get("sex"), "MALE")

If the Parent has a son or SON and Daughter it's returning that parent but also returning all the children they have.

Hibernate fires off the first query with my predicates but the second query to get the children only uses the JoinColumn in the where clause it doesn't include

cb.equal(parent.get("children").get("sex"), "MALE").

Thoughts?

I am using a SetJoin

children = parent.joinSet("children", JoinType.LEFT)

CLARIFICATION:

public static Specification<Parent> findPlanBenefits(Integer parentId) {
    return (parent, query, cb) -> {
        Predicate predicates = cb.conjunction();
        List<Expression<Boolean>> expressions = predicates.getExpressions();

        //Parent Criteria
        expressions.add(cb.equal(parent.get("parentId"), parentId));

        //Children Criteria
        SetJoin<Parent, Children> children = parent.joinSet("children", JoinType.LEFT);

        Predicate sex = cb.equal(children.get("sex"), "MALE");
        children.on(sex);

        return predicates;
    };
}

Answer 1

I am afraid, the JOIN ON does not work as you expect in your answer. JOIN ON only tells how to join, and NOT how relationships are loaded.

So, in order to solve your problem you will have to filter the children after they are loaded, or fetch manually all male children with a separate query.

In order to check how JOIN ON works, you could try also the corresponding JPQL query.

---

UPDATE

OP told that the JPQL queryselect p from parent p join fetch children c where p.parentId = :parentId and c.sex = "MALE" works.

The corresponding CriteriaQuery would look like:

CriteriaQuery<Parent> criteria = cb.createQuery((Class<Parent>) Parent.class);
Root<Parent> parent = criteria.from(Parent.class);

criteria.select((Selection<T>) parent);
SetJoin<Parent, Children> children = parent.joinSet("children", JoinType.LEFT);

Predicate sexPredicate = cb.equal(children.get("sex"), "MALE");
parent.fetch(children);
//parent.fetch("children");//try also this

criteria.where(sexPredicate);

Answer 2

When you create a JOIN (especially when property is collection type, not SingularAttribute, you have to use it to build the criteria, so use

cb.equal(children.get("sex"), "MALE").

instead of

cb.equal(parent.get("children").get("sex"), "MALE").