JPQL combination of AND and OR and BETWEEN doesn't work

Support
#1

I have

String queryStr = "select e from callbook$Event e";

…

 queryStr += " where ";

…

 queryStr += "(e.start between :start and :end) or (e.end between :start and :end)";

And it throws:

JpqlSyntaxException: Errors found for input jpql:[select e from callbook$Event e where (e.start between :start and :end) or (e.end between :start and :end)]  
CommonErrorNode [<unexpected: [@30,76:76='.',<68>,1:76], resync=e.end between :start>]  
CommonErrorNode [<unexpected: [@40,104:104=')',<35>,1:104], resync=:end>]

However when I replace the last one with

queryStr += "(e.start between :start and :end)";

it works. Why?!
Also, it causes strange behavior of this site’s parser:
image

#2

If I’m not wrong, using setParameters() you bind values to positional parameters. Try to use :start1, :end1 and :start2, :end2 passing 4 parameters, not 2.

queryStr += “(e.start between :start1 and :end1) or (e.end between :start2 and :end2)”;

#3

Unfortunately no. For

queryStr += " and ((e.event.start between :start and :end) or (e.event.end between :start2 and :end2))"

I get same error

JpqlSyntaxException: Errors found for input jpql:[select e from callbook$EventCard e where e.card.id = :cardId and ((e.event.start between :start and :end) or (e.event.end between :start2 and :end2))]
CommonErrorNode [<unexpected: [@45,111:111='.',<68>,1:111], resync=e.event.end between :start2>]
CommonErrorNode [<unexpected: [@57,147:147=')',<35>,1:147], resync=:end2>]

I don’t know why…

#4

Hi,

I think your usage of between is not correct. It is a macro that should be used @between. See: Macros in JPQL - CUBA Platform. Developer’s Manual

#6

Mario,

moment1, moment2

– start and end points of the time interval where the value of field_name should fall into. Each of the points should be defined by an expression containing now variable with an addition or subtraction of an integer number.
Is not what I need…
And to be honest the usage of between I saw in sample-timesheets/HolidaysCache.java at f33c187fae12227da7060ef505344e47da8dd4fc · cuba-platform/sample-timesheets · GitHub
But idk why I can’t use same thing in my project

LoadContext<EventCard> loadContext = new LoadContext<>(EventCard.class);
        if (viewName != null) {
            loadContext.setView(viewName);
        }
        String queryStr = "select e from callbook$EventCard e where e.card.id = :cardId";

//..
       
        if (startDate != null || endDate != null) {
            if (startDate == null) startDate = new Date(1999,1,1);
            if (endDate == null) endDate = new Date(2030,1,1);
            queryStr += " and ((e.event.start between :start and :end) or (e.event.end between :start2 and :end2))";
        }
       


        LoadContext.Query query = loadContext.setQueryString(queryStr)
                .setParameter("cardId", card.getId());
//...
      
        if (startDate != null || endDate != null) {
            query.setParameter("start", startDate);
            query.setParameter("end", endDate);
            query.setParameter("start2", startDate);
            query.setParameter("end2", endDate);
        }
      //..
        return dataManager.loadList(loadContext);
#7

So from your initial post, the problem is here:

(e.end between :start and :end)

and is because you’re using end as field name.

A quick look at the JPQL documentation shows that END is a reserved word, so my guess is that your statement is confusing the parser.

Try changing the field name to endDate or something like that, and that should fix it.

And in the example you gave from Cuba’s code, it looks like they also avoid using end as a field name.