Filter collection by multiple attributes of its elements - QueryDSL

Question

I am working on a dynamic filter component based on QueryDSL with the use of SpringData for query execution. Thus I create Predicate instances from the received data ad pass it to QueryDslPredicateExecutor. For dynamic access to entity attributes I use generic PathBuilder typed to the entity class.

Consider the following (simplified) code:

class Offer {
    List<LanguageToName> names;
}
class LanguageToName {
    String name;
    String language;
}

When I try to query Offer entites, that have in their collection name element with attribute 'abc', I simply create the predicate as follows:

pathBuilder.getCollection("names", LanguageToName.class).any().getString("name")
    .like("%" + fieldData.getFieldValue() + "%");

However, I was unable to come up with a solution to filter the collection by multiple attributes of the containing objects with the use of PathBuilder. When I append the code above with .and() and access the collection again via the pathBuilder variable, I naturally get the result equivalent to appending sql query with AND EXISTS..., which is not the desired result. I also tried to use getCollection().contains(), but I was unable to create the Expression<LanguageToName> that would describe such case.

Is there a way to create a Predicate that would filter entities by multiple attributes of the elements from a collection, that is a field of the queried entity?

Answer 1

I had similar issue and finally solved this with subquery (however, it seems to me that it works only for 1 level of nestedness).

My initial predicate was (it was making 2 independent sub-queries):

Predicate predicate = codeTable.customer.id.eq(customerId)
                .and(codeTable.qualifierResults.any().customerQualifier.type.eq("TARGET_TYPE"))
                .and(codeTable.qualifierResults.any().customerQualifier.referenceType.code.eq("TARGET_CODE"));

But the correct predicate that I ended up with was:

BooleanExpression customerQualifierCondition = JPAExpressions
        .selectFrom(codeTableQualifierResult)
        .where(codeTableQualifierResult.in(codeTable.qualifierResults),
                codeTableQualifierResult.customerQualifier.type.eq("TARGET_TYPE"),
                codeTableQualifierResult.customerQualifier.referenceType.code.eq("TARGET_CODE"))
        .exists();
Predicate predicate = codeTable.customer.id.eq(customerId).and(customerQualifierCondition);

The idea is to write 1 separate sub-query where you apply all necessary conditions at once (instead of applying them for your collection independently).

Answer 2

I ran across the same problem in my project. My workaround is to build the exists subquery manually.

Assuming that your both classes are mapped as 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;
}

A simple query with any():

BooleanExpression namesFilter = QOffer.offer.names.any().name.eq("Esperanto");

maps 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=?
    )

A subquery:

BooleanExpression namesFilter = JPAExpressions.selectOne()
            .from(languageToName)
            .where(languageToName.offer.eq(QOffer.offer)
                    .and(languageToName.name.eq("Esperanto")))
            .exists();

Maps 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=?
    )

which matches perfectly previous SQL. You can add additional conditions like:

BooleanExpression namesFilter = JPAExpressions.selectOne()
            .from(languageToName)
            .where(languageToName.offer.eq(QOffer.offer)
                    .and(languageToName.name.eq("Esperanto"))
                    .and(languageToName.language.like("E%")))
            .exists();