How to visualize only the last 10 orders in a case overview page using a Custom Query and APIs?

Q&A
1

Hello everybody,

It may be a simple question but no matter how hard I try, I can’t find a solution.

Here is the context:
Similar to the “Travel Request” tutorial, I have a case overview page but with one Table widget that allows me to see the status of my order (pending, rejected or approved). For instance:

Cust. | Status

  1 | approved

  2 | rejected

  3 | pending

I can view my first 10 orders using the Default query “findByUserId” and, following the “Getting started tutorial”, using a bdm API:
…/API/bdm/businessData/ats.model.DistributionOrder?q=findByUserId&p=0&c=10&f=userId={{session.user_id}}

And everything works well.

Nevertheless, when I place my eleventh order, it is impossible for me to review it. Of course, I could put “c=11” in the bdm API above but I don’t want to change it otherwise my list of orders would be too long.
That’s why I would like to be able to visualize one of these 2 options:

  1. orders from the last 5 days only: I have already tried using a Custom query but performing operations on dates in JPQL is actually more complicated than it seems. Although, I tried to do so with the help of the documentation provided by Bonita:
    https://en.wikibooks.org/wiki/Java_Persistence/JPQL
    and using the following functions: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP but nothing works.

Therefore, I tried something that seemed more accessible to me, the 2nd option below

  1. my LAST 10 orders: I tried to do this with a Custom query:

SELECT d
FROM DistributionOrder d
WHERE d.userId = :userId
AND d.persistenceId >= :persistenceId-10
ORDER BY d.persistenceId ASC

Then, I simply use this API in my case overview page:
…/API/bdm/businessData/ats.model.DistributionOrder?q=query2&p=0&c=10&f=userId={{session.user_id}}

And yet, it doesn’t work.

If someone has already encountered this problem, do you have a solution for me? I will be grateful because I no longer know how to proceed

Best Regards,
Clément

2

Dear all,

I found a way to get around my problem ! I simply add a custom query ranking the orders in **descending ** order of persistenceId:

SELECT d
FROM DistributionOrder d
WHERE d.userId= :userId
ORDER BY d.persistenceId DESC

Hope it will help.

Regards,
Clément