Java – filter collections by multiple attributes of their elements – querydsl
I am studying the dynamic filter component based on query DSL and using spring data for query execution Therefore, I create a predicate instance from the received data AD and pass it to querydslpredicateexecutor For dynamic access to entity properties, I use a generic pathbuilder that types entity classes
Consider the following (Simplified) codes:
class Offer { List<LanguageToName> names; } class LanguageToName { String name; String language; }
When I try to query offer entities, I have the attribute 'ABC' in its collection name element. I just need to create a predicate, as shown below:
pathBuilder.getCollection("names",LanguageToName.class).any().getString("name") .like("%" + fieldData.getFieldValue() + "%");
However, I can't come up with a solution to use pathbuilder to filter collections by containing multiple properties of objects When I use And () when attaching the above code and accessing the collection again through the pathbuilder variable, I will naturally get the result equivalent to attaching the SQL query using and exists... Which is not the desired result I also try to use getcollection() Contains(), but I can't create expression < languagetoname >, which will describe this situation
Is there any way to create a predicate that can filter entities through multiple attributes of elements in the collection, which is a field of query entities?
Solution
I encountered the same problem in the project
Suppose your two classes are mapped to entities:
@Entity @Table(name = "Offer") public class Offer { @Id String id; @OneToMany(fetch = FetchType.LAZY,mappedBy = "offer") List<LanguageToName> names; } @Entity @Table(schema = "dcsdba",name = "Language_To_Name") public class LanguageToName { @Id String id; @ManyToOne(fetch= FetchType.LAZY) @JoinColumn(name="Offer_id") private Offer offer; String name; String language; }
Use any() for a simple query:
BooleanExpression namesFilter = QOffer.offer.names.any().name.eq("Esperanto");
Map to
select offer0_.id as id1_7_ from offer offer0_ where exists ( select 1 from dcsdba.language_to_name names1_ where offer0_.id=names1_.offer_id and names1_.name=? )
Subquery:
BooleanExpression namesFilter = JPAExpressions.selectOne() .from(languageToName) .where(languageToName.offer.eq(QOffer.offer) .and(languageToName.name.eq("Esperanto"))) .exists();
Map to:
select offer0_.id as id1_7_ from offer offer0_ where exists ( select 1 from dcsdba.language_to_name languageto1_ where languageto1_.offer_id=offer0_.id and languageto1_.name=? )
It exactly matches the previous SQL You can add other conditions, such as:
BooleanExpression namesFilter = JPAExpressions.selectOne() .from(languageToName) .where(languageToName.offer.eq(QOffer.offer) .and(languageToName.name.eq("Esperanto")) .and(languageToName.language.like("E%"))) .exists();