Question 20492 in Mysql Question posted in
The official MySQL documentation can be found here.

How to use pageable sort function in custom JPQL? – Mysql

I have a jpql as:

@Query("SELECT b FROM Booking b WHERE "
            + "(:officeId is null OR b.officeId = :officeId) AND "
            + "(:status is null OR b.status = :status) AND "
            + "(:type is null OR b.officeType = :type) "
            + "ORDER BY CASE WHEN b.guestId in (SELECT userId FROM MeetingRoomQuotaRequest) THEN 0 ELSE 1 END")
    Page<Booking> findAndOrderByQuotaRequestExists(
            @Param("officeId") String officeId,
            @Param("status") Status status,
            @Param("type") OfficeType type,
            Pageable pageable);

In this JPQL I am searching the query according to officeId, status, type optional fields so if they are null then I just ignore them. It is working good if I don’t send the sort function. But if I send sort parameter then it is getting error:
enter image description here

Pageable sort function is added to the end of the sql and it is getting syntax error:

[SELECT b FROM Booking b WHERE (:officeId is null OR b.officeId = :officeId) AND (:status is null OR b.status = :status) AND (:type is null OR b.officeType = :type) ORDER BY CASE WHEN b.guestId in (SELECT userId FROM MeetingRoomQuotaRequest) THEN 0 ELSE 1 END order by b.createdOn desc];

As you see at the end it is adding order by b.createdOn desc but it must be separated just with comma(,) instead of putting 'order by' again.

How can I modify the jpql to get page with optional params and also pageable sort options ?

Tags:

2

Answers


  1. 0

    JPQL generates queries from static rules it has been defined on. It cannot know that you have already defined one order by explicitly. So you can get around this by putting b.guestId in (SELECT userId FROM MeetingRoomQuotaRequest) clause in where clause of first statement.

    Login or Signup to reply.
  2. 0

    Ya you noticed yoi can add the raw ORDER BY to the query directly, you need to ass Sort object instance and add it to the findAndOrderByQuotaRequestExists

    JpaSort.unsafe("CASE WHEN b.guestId in (SELECT userId FROM MeetingRoomQuotaRequest) THEN 0 ELSE 1 END")
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search