Spring Data JPA - Is it possible to sort on a calc

Question

Suppose you have the following Entity:

@Entity
public class Game {

    @Id
    @GeneratedValue
    private Integer id;

    private String name;

    private Calendar startTime;
    private int durationInSeconds;

    public GameStatus getStatus() {
        if( startTime.after(Calendar.getInstance()))
        {
            return GameStatus.SCHEDULED;
        } else {
            Calendar endTime = Calendar.getInstance();
            endTime.setTime(startTime.getTime());
            endTime.roll(Calendar.SECOND, durationInSeconds);

            if( endTime.after(Calendar.getInstance())) {
                return GameStatus.OPEN_FOR_PLAY;
            }
            else {
                return GameStatus.FINISHED;
            }
        }
    }
}

If my GameRepository is a PagingAndSortingRepository, how can I get a page of results, sorted by the status property?

I currently get:

java.lang.IllegalArgumentException: Unable to locate Attribute  with the the 
given name [status] on this ManagedType [org.test.model.Game]

Which I can understand since status is indeed no JPA attribute. Is there a way around this?

(I am using Hibernate underneath, so anything Hibernate specific is also ok)

Answer 1

The problem is that Spring Data's PageRequest sort is done on the database layer by forming the ORDER BY clause.

You could create a @Formula column, e.g.

@Entity
public class Game {
...
     // rewrite your logic here in HQL
     @Formula("case when startTime >= endTime then 'FINISHED' ... end")
     private String status;

Then it will be possible to use the new column in sort order as everything you write in the formula will be passed to ORDER BY clause.