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();