Hello,

I have a question concerning HQL. In my model I have:
- a Sale entity which has n SaleHistoryEntries.
- each SaleHistoryEntry is associated to a status and has a date.
- a Status has a code name (TREATED, IN_TRANSIT, COMPLETED, CANCELED, etc.)

For example:


I would like to make a HQL request to get all the Sales that are in a certain state (that is that their latest SaleHistoryEntry has a particular Status.

For example (using the previous model example). I would like to fetch the Sales that are currently in the COMPLETED state. I should therefore receive A and D.

I have read the HQL documentation and have tried a few things but I don't know how to retrieve the latest of the SaleHistoryEntries of the sale.

Any idea or clue would be greatly appreciated. At the limit, I could put a currentStatus flag on the SaleHistoryEntry but I don't believe I should need that.

Thanks for any help,

Francois

something like that i guess should do the job

Hi pkleindl,

Thank you for your answer but unfortunately your answer does not include the complexity of wanting the latest status.

With my example and your code, if I used the status: IN_TRANSIT, I would receive sales A and C when I would only want to receive sale C (the one that has a "current" status of IN_TRANSIT).

The complexity of this request is to request the latest saleHistoryEntry of a sale and then check the status.

Any other ideas?

Thanks,

Francois

I've been able to solve this using sql:



Is there any way to do this using HQL?

Hm..

Try add


so you only take those records where the date matches the highest date of the SaleHistoryEntries for the same sale

Not sure if it works that way in HQL and I didn't try out the code, hope you get the idea

Rating appreciated if it works out :o)