CODEWITHSUNDEEP
querydsl
Simon Lenz
Simon Lenz

Reputation: 2812

Writing conditional query in QueryDsl

I have an entity with a field called date and a field called creationDate. The first can be null, the latter not.

And now I want to get all items from a certain timespan. If date is not null, then use date. If it is null, then we should use creationDate as a backup for the check.

To achieve this I'm trying to assemble a com.querydsl.core.types.Predicate that I then can pass to my datarepositories findAll method. (I am using Spring data repositories).

What I have so far:

    QItem item = QItem.item$;
    BooleanExpression predicate = new CaseBuilder()
            .when(item.date.isNotNull())
            .then((Predicate) item.date.between(startDate, endDate))
            .otherwise(item.creationDate.between(startDate, endDate));

And I'm receiving this exception while using my peace of code:

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: case near line 3, column 7 [select item$
from com.example.entities.Item item$
where case when (item$.date is not null) then (item$.date between ?1 and ?2) else (item$.creationDate between ?1 and ?2) end]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: case near line 3, column 7 [select item$
from com.example.entities.Item item$
where case when (item$.date is not null) then (item$.date between ?1 and ?2) else (item$.creationDate between ?1 and ?2) end]] with root cause

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node: case near line 3, column 7 [select item$
from com.example.entities.Item item$
where case when (item$.date is not null) then (item$.date between ?1 and ?2) else (item$.creationDate between ?1 and ?2) end]
    at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]

What is going wrong here?

Am I using the CaseBuilder even for the right purpose?

regards

Upvotes: 2

Views: 4058

Answers (2)

Robert Bain
Robert Bain

Reputation: 9586

Am I using the CaseBuilder even for the right purpose?

The CaseBuilder is for creating a case statement, E.G.

SELECT CASE WHEN some_column = 'A' THEN 'It is A'
            WHEN some_column = 'B' THEN 'It is B'
            ELSE 'It is a different value' END AS some_alias
FROM some_table;

At each conditional in the case statement, the return value is specified. In your example, you're looking to choose boolean logic conditionally. This isn't what a case statement is for.

Upvotes: 1

Simon Lenz
Simon Lenz

Reputation: 2812

Okay, I replaced the Case-based predicate with something like

Predicate withDate = new BooleanBuilder(item.date.isNotNull())
        .and(item.date.between(startDate, endDate));
Predicate withoutDate = new BooleanBuilder(item.date.isNull())
        .and(item.creationDate.between(startDate, endDate));

Predicate combined = new BooleanBuilder(withDate)
        .or(withoutDate);

and it works. I would still be interested in what was wrong with my first approach.

Upvotes: 4

Related Questions