How to filter entity's @OneToMany relationships with JPA Criteria API?

260 Views Asked by At

I have a Parent entity with 5 relationships. I am using a findAll(Specification<Parent> spec) in my JpaRepository to filter the Parent by 3 of its relationships.

The last two relationships are unidirectional @OneToManys, List<Child> name and List<Child> description. In the SearchCriteria pojo that I use to create the Specification, there is a field Language that I want to use to filter each list. There will always be only 1 of each Child related to the Parent with the same Language.

So I want my list of filtered Parents to be returned with a list of 1 name and 1 description. However, since I've been having trouble I deleted the 2nd list so that I am only working on List<Child> name

Inside my SpecificationRepository.java my first try was

private Specification<Parent> createSpecifications(SearchCriteria searchCritera) {
   Specification<Parent> specification = findaAllServices();

   specification = specification.and(filterOne(searchCritera.getFilterOne()));
   specification = specification.and(filterTwo(searchCritera.getFilterTwo()));
   specification = specification.and(filterThree(searchCritera.getFilterThree()));

   return specification.and(filterTranslations(searchCriteria.getLanguage()));
}

private Specification<Parent> filterTranslations(String language) {
   return (root, query, builder) -> {
      return builder.equal(root.join(Parent_.CHILD).get(Child_.LANGUAGE), language);
}

It seemed to me that this single statement should be all that is needed but it did nothing. My database queries were unchanged. I have done a lot of research since then and I have found multiple answers that reinforce my original idea - How to filter both entity and @OneToMany relationship by criteria api so I'm unsure why it doesn't work for me but I exhausted all the possibilities (like using a multi-directional relationship as recommended there)

So I moved on to trying to implement the change using a Subquery like so

   Subquery<Child> subquery = query.subquery(Child.class);
   Root<Child> childRoot = subquery.from(Child.class);
   subquery.select(childRoot).where(builder.equal(childRoot.get(Child_.LOCALE_CD), language));
   return subquery.in(query);

However that that returns an error saying that the SqlNode's text did not refernece expected number of columns. I tried any variation I could think of and I got around the error by changing the return statement to a duplicate return builder.equal(childRoot.get(Child_.LOCALE_CD), language); but then I was back to getting a nonfiltered list.

I attempted to use a Join On instead but had no luck there either and I'm all out of ideas.

0

There are 0 best solutions below