Order by various columns in JPQL / Hibernate

Question

I have a session table. Each session has "session_start_time" and "session_end_time". While the session is open, the end time is empty. I want to get a list of sessions and order it according to the following logic:

• If the session is open (without end time), order by start time.
• If the session is closed, book by the end of time.

Sort of:

 ORDER BY (session_end_time == null) ? session_start_time : session_end_time

I use JPA and JPQL for queries and use Hibernate for execution. What would you recommend?

NOTE . I would prefer not to add CASE to SELECT, but to keep it clean instead, so I get a list of sessions without additional unnecessary fields.

Answer 1

ORDER BY nvl (session_end_time, session_start_time)

nvl is an oracle function. I am sure that these are functions for other DBMS

Found this for sleep mode: nvl in HIBERNATE: How to simulate NVL in HQL

ORDER BY: https://forum.hibernate.org/viewtopic.php?f=25&t=997220&start=0

Answer 2

whether

ORDER BY CASE 
    WHEN session_end_time IS NULL THEN session_start_time 
    ELSE session_end_time 
END 

work in your DBMS? This does not add a field.

Otherwise, you can calculate it inside a subquery, but not include it in the final sentence SELECT:

SELECT field1, field2 FROM (
    SELECT field1, field2, CASE WHEN session_end_time... END AS dummyfield
) Q
ORDER BY Q.dummyfield