Hi all,

i expose my problem :
I have in my model a relation ACTION with this format
ID | ID_ACTION | EVENT_ID
with EVENT_ID a foreign key which is an integer.

i would like now to select in ACTION all the tuples and sort them according to a list of EVENT_ID.
For instance, if i have in ACTIONS the following:
ID | ID_ACTION | EVENT_ID
1 | a | 1
2 | b | 2
3 | c | 2
4 | d | 3

And the sorted list of event_id L=(2,3,1), i would like the previous tuples list to be sorted as follows :
ID | ID_ACTION | EVENT_ID
2 | b | 2
3 | c | 2
4 | d | 3
1 | a | 1

So i wonder if there is a way to parameter a Hibernate Query using a list to order my results. I've never seen such thing before, even in SQL, but i think some people here could help me :)

Thanks a lot !

Cédric

The only way I know to do that, is to execute a query that replaces the EVENT_ID with their order position.

In your example: L=(2,3,1), you should substitute EVENT_ID=2 with 1, EVENT_ID=3 with 2 and EVENT_ID=1 with 3.

The ugly part is how to do that, because it will only work if you are using a database capable of doing that (ie Oracle). In oracle you may use CASE WHEN satatements

An example of JPA/Hibernate query if you use Oracle:



An alternative option is to order the result in memory after recovering it.

Thank you very much for your answer !