Let's say I have a data model like this (pseudocode):
@Entity
Person {
@OneToMany
List<PersonAttribute> attributes;
}
@Entity
PersonAttribute {
@ManyToOne
AttributeName attributeName;
String attributeValue;
}
@Entity
AttributeName {
String name;
}
I have a Spring-Data-JPA repository defined such as:
public interface PersonRepository extends PagingAndSortingRepository<Person, Long>, QueryDslPredicateExecutor<Person>{}
I see in the QueryDSL documentation that there is a mechanism to Join from the Person to the PersonAttribute, but it looks like you need access to the QueryDsl Query object, which the client of the repository wouldn't have.
What I would like to do with my Predicate is to find all those Persons that have an AttributeValue (there's one join) with a value of "blue" and an AttributeName (there's another join) with a name of "eyecolor". I'm not sure how I would do that with an any()
and enforce that I only get those with eye_color=blue and not those with shoe_color=blue.
I was hoping I could do something like this:
QPerson person = QPerson.person;
QPersonAttribute attribute = person.attributes.any();
Predicate predicate = person.name.toLowerCase().startsWith("jo")
.and(attribute.attributeName().name.toLowerCase().eq("eye color")
.and(attribute.attributeValue.toLowerCase().eq("blue")));
but with the any()
in there it just matches anything with an attribute value of "blue" and anything with an "eye color" attribute regardless of color. How I can make those conditions apply to the same attribute within the set?
You can't directly join a column in a predicate but you can create an any() expressions like this
QPerson.person.attributes.any().attributeValue.eq("X")
This approach has the restriction that the join expression QPerson.person.attributes.any()
can be used in only one filter. It has though the benefit that this expression is internally converted into a subquery which doesn't conflict with paging.
For multiple restrictions you will need to construct a subquery expression explicitly like this
QPersonAttribute attribute = QPersonAttribute.personAttribute;
new JPASubQuery().from(attribute)
.where(attribute.in(person.attributes),
attribute.attributeName().name.toLowerCase().eq("eye color"),
attribute.attributeValue.toLowerCase().eq("blue"))
.exists()
In addition to QueryDslPredicateExecutor
you can also use Querydsl queries via Spring Data like this
public class CustomerRepositoryImpl
extends QuerydslRepositorySupport
implements CustomerRepositoryCustom {
public Iterable<Customer> findAllLongtermCustomersWithBirthday() {
QCustomer customer = QCustomer.customer;
return from(customer)
.where(hasBirthday().and(isLongTermCustomer()))
.list(customer);
}
}
Example taken from here https://blog.42.nl/articles/spring-data-jpa-with-querydsl-repositories-made-easy/