'JPA Specifications not working when trying to OR predicates together

I'm having trouble trying to get a working Specification for this query. My data model looks something like this:

            Item { 
                Person person, 
                Place place
            }

            Person {
                Address address
            }

            Place {
                Address address
            }

            Address {
                String country
                String state
            }

An item can have either a Person or a Place associated with it, but not both. I want to query a list of Items based on if their Address matches either an associated Person or Place's address by searching against country AND state. My current implementation looks something like this:

            public static Specification<Item> locationIs(String country, String state) {
                return (root, query, criteriaBuilder) -> {
                    Expression<String> personCountry = root.get("person").get("address").get("country");
                    Expression<String> placeCountry = root.get("place").get("address").get("country");
                    Expression<String> personState = root.get("person").get("address").get("state");
                    Expression<String> placeState = root.get("place").get("address").get("state");

                    Predicate personCountryMatch = criteriaBuilder.like(personCountry, "%" + country + "%");
                    Predicate personStateMatch = criteriaBuilder.like(personState, "%" + state + "%");
                    Predicate personMatch = criteriaBuilder.and(personCountryMatch, personStateMatch);

                    Predicate placeCountryMatch = criteriaBuilder.like(placeCountry, "%" + country + "%");
                    Predicate placeStateMatch = criteriaBuilder.like(placeState, "%" + state + "%");
                    Predicate placeMatch = criteriaBuilder.and(placeCountryMatch, placeStateMatch);

                    Predicate match = criteriaBuilder.or(personMatch, placeMatch);

                    return match
                };
            }

If I return just personMatch or just placeMatch it appears to work perfectly for searching on that, but as soon as I try to or them together the whole query returns no results. I've tried creating Specifications from each predicate and combining them that way using , same result.

javasqljpa


Solution 1:[1]

Ended up just using subqueries for this instead, did something like

criteriaBuilder.or(criteriaBuilder.exists(subquery1), criteriaBuilder.exists(subquery2))

And that worked fine

Solution 2:[2]

While subqueries can be a good alternative, the query you seemed to be aiming for originally would have been:

Join<Person> person = root.join("person", JoinType.LEFT);
Join<Person> place = root.join("place", JoinType.LEFT);

Predicate personCountryMatch = criteriaBuilder.like(person.get("country"), "%" + country + "%");
Predicate personStateMatch = criteriaBuilder.like(person.get("state"), "%" + state + "%");
Predicate personMatch = criteriaBuilder.and(personCountryMatch, personStateMatch);

Predicate placeCountryMatch = criteriaBuilder.like(place.get("country"), "%" + country + "%");
Predicate placeStateMatch = criteriaBuilder.like(lace.get("state"), "%" + state + "%");
Predicate placeMatch = criteriaBuilder.and(placeCountryMatch, placeStateMatch);

Predicate match = criteriaBuilder.or(personMatch, placeMatch);

return match

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 BinaryForest
Solution 2 Chris

Related Questions