Search code examples
spring-bootquerydslcriteriaquery

Spring Boot QueryDSL BooleanExpression - "OR" condition depending on the value in table

I use Spring Boot and QueryDSL to combine a sql query and predicate. The problem is that I have to create a predicate to fetch data from table based on email BUT:

  1. email can be in Freight.sender.email
  2. OR in Freight.message.senderAddress

where Freight, Message, Sender are of course tables. In table Freight we can have empty sender_id or message_id and depending on this I have to fetch rows by email from Freight.sender.email OR Freight.message.senderAddress (if Freight.sender is null)

Is it possible to create such a predicate that compares email from request query param with Freight.sender.email and only if Freight.sender.email doesn't exist, then my predicate shold search email in Freight.message.senderAddress

public Predicate build(Map<String, String> filters) {
        return new OptionalBooleanBuilder(Expressions.asBoolean(true).isTrue())
                .notNullAnd(qFreight.loadingAddress::containsIgnoreCase, filters.get(LOADING_ADDRESS))
                .notNullAnd(qFreight.unloadingAddress::containsIgnoreCase, filters.get(UNLOADING_ADDRESS))
                .notNullAnd(qFreight.loadingDate.eq(filters.get(LOADING_DATE) != null ? LocalDate.parse(filters.get(LOADING_DATE)) : now()), filters.get(LOADING_DATE))
                .notNullAnd(qFreight.unloadingDate.eq(filters.get(UNLOADING_DATE) != null ? LocalDate.parse(filters.get(UNLOADING_DATE)) : now()), filters.get(UNLOADING_DATE))
//MY ATTEMPT - NOT WORKING:
        .notNullAnd(qFreight.sender.email.eq(filters.get(SENDER_EMAIL)).or(qFreight.emailMessage.senderAddress.eq(SENDER_EMAIL)), filters.get(SENDER_EMAIL))
                .build();
}

public class OptionalBooleanBuilder {

    private BooleanExpression predicate;

    public OptionalBooleanBuilder(BooleanExpression predicate) {
        this.predicate = predicate;
    }

    public <T> OptionalBooleanBuilder notNullAnd(Function<T, BooleanExpression> expressionFunction, T value) {
        if (nonNull(value)) {
            return new OptionalBooleanBuilder(predicate.and(expressionFunction.apply(value)));
        }
        return this;
    }

    public BooleanExpression build() {
        return predicate;
    }

    public <T>OptionalBooleanBuilder notNullAnd(BooleanExpression expression, T value) {
        if(nonNull(value)){
            return new OptionalBooleanBuilder(predicate.and(expression));
        }
        return this;
    }
}

UPDATE

After suggestion from:

private Predicate addPredicate(OptionalBooleanBuilder builder, String email) {
    if (nonNull(email)) {
        return builder.notNullAnd(qFreight.sender.email.coalesce(qFreight.emailMessage.senderAddress.eq(email)).asBoolean(), email).build();

    return builder.build();
}

I get error:

    antlr.NoViableAltException: unexpected AST node: (
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2169) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2089) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:827) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:621) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:325) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:273) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:276) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]
        at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:192) ~[hibernate-core-5.4.17.Final.jar:5.4.17.Final]


2020-12-24 00:45:35.535 ERROR 16068 --- [nio-9090-exec-8] p.a.m.s.filter.JwtAuthorizationFilter    : Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: ( near line 3, column 52 [select freight
from pl.appwise.mtf.freight.domain.model.Freight freight
where ?1 = ?1 and freight.user.id = ?2 and coalesce(freight.sender.email, freight.emailMessage.senderAddress = ?3)
order by freight.loadingDate desc]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: ( near line 3, column 52 [select freight
from pl.appwise.mtf.freight.domain.model.Freight freight
where ?1 = ?1 and freight.user.id = ?2 and coalesce(freight.sender.email, freight.emailMessage.senderAddress = ?3)
order by freight.loadingDate desc]
2020-12-24 00:45:37.906  INFO 16068 --- [   scheduling-1] ilAccoun
Solution

  • Use Freight.sender.email.coalesce(Freight.message.senderAddress). Freight.message.senderAddress will be NULL if Freight.message is NULL. For optional associations left outer joins are used by default, so this should cause no issue. Otherwise, explicitly use a left join yourself.