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

1
0
-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

2) 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

1 answer

1
0
-1
This one is the BEST answer!

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

Notifications