How to order by the same filed twice by condition

Question

I have this model :

public class Post {
    private Long expiryDate;  // A timestamp
}

And repository as :

@Repository
public interface PostRepository extends JpaRepository<Post, Long> {
    Page<Post> findAllByOrderByExpiryDateDesc(Pageable pageable);
}

What I want to do:

When the expiry date has passed (post expired, current date greater than expiry date) => orderBy desc

Else, When the expiry date not yet passed (post not expired) => orderBy asc

Example: if I have the expiry date list: Monday, Tuesday, Wednesday, Thursday and Friday

and that today is Wednesday (Wednesday not yet expired).

Wanted result:

1. Wednesday
2. Thursday
3. Friday
4. Tuesday
5. Monday

Someone got any solution please?

Answer 1

You could try the following query:

SELECT p FROM Post p
ORDER BY CASE
   WHEN p.expiryDate > CURRENT_TIMESTAMP THEN p.expiryDate
   ELSE -p.expiryDate
END ASC

(you might need some casting to make it work, but since you didn't mention the RDBMS you're using, you'll need to check it out yourself)

Answer 2

I found the solution (with the help of @crizzis), This Query work perfectly :

@Query(value = "from Post as p ORDER BY " +
        "CASE WHEN p. expiryDate > :currentTime THEN -p.expiryDate ELSE null END desc," +
        "CASE WHEN p. expiryDate < :currentTime THEN -p.expiryDate ELSE null END asc")
Page<Post> findAll(Long currentTime, Pageable pageable);

For native query (h2 database example)

SELECT * FROM Post p
ORDER BY 
CASE WHEN p.expiryDate > currentTime THEN -p.expiryDate END desc,
CASE WHEN p.expiryDate < currentTime THEN -p.expiryDate END asc;