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.