QueryDSL: Syntax error parsing during rounding

Question

@Embeddable
public class Birthday {

    private LocalDate value;

    // Constructors, getters and setters
}

Entity:

@Entity
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private Birthday birthday;

     // Constructors, getters and setters
}

Query with deviding by 3:

List<Person> sortedPersons = query.selectFrom(person)
                               .orderBy(person.birthday.value.month().divide(3).ceil().asc())
                                .fetch();

Query using template:

 List<Person> sortedPersons = query.selectFrom(person)
                                   .orderBy(Expressions.dateTemplate(Integer.class, "QUARTER({0})", person.birthday.value).asc())
                                    .fetch();

Error:

Caused by: org.eclipse.persistence.exceptions.JPQLException:
Exception Description: Syntax error parsing [select 
from Person person
order by ceil(extract(month from person.birthday.value) / 3) asc].
[3165, 3254] The order by item is not a valid expression.
    at org.eclipse.persistence.internal.jpa.jpql.HermesParser.buildException(HermesParser.java:157) ~[com.ibm.websphere.appserver.thirdparty.eclipselink.2.7_1.0.76.jar:?]
    at [internal classes]
    at com.querydsl.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:101) ~[querydsl-jpa-4.2.2.HL-20230109.202119-8.jar:?]
    at com.querydsl.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:94) ~[querydsl-jpa-4.2.2.HL-20230109.202119-8.jar:?]
    at com.querydsl.jpa.impl.AbstractJPAQuery.fetch(AbstractJPAQuery.java:201) ~[querydsl-jpa-4.2.2.HL-20230109.202119-8.jar:?]

Birthday field is stored in the database as DATE

The problem is that if I remove method .ceil() then everything works great, but rounding is important for me without round up my query return incorrect data (I need to sort by quarter). I tried to use templates for this one but I did not help.

If I do not use .ceil I have this data with such ordering.

1. 01/11/2023
2. 03/30/2023
3. 04/29/2023
4. 03/31/2023
5. 03/30/2023
6. 08/18/2023

Answer 1

Changed sql-template. As it turned out, you need to take out a variable in the template itself :)

  List<Person> sortedPersons = query.selectFrom(person)
                                   .orderBy(Expressions.dateTemplate(Integer.class, "SQL('QUARTER(?)', {0})", person.birthday.value).asc())
                                    .fetch();