CODEWITHSUNDEEP
sqlspring-bootjpaspring-data-jpaspring-data
Idriss
Idriss

Reputation: 418

How to order by the same filed twice by condition

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?

Upvotes: 0

Views: 245

Answers (2)

Idriss
Idriss

Reputation: 418

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;

Upvotes: 0

crizzis
crizzis

Reputation: 10716

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)

Upvotes: 1

Related Questions