[FIXED] Sorting of dynamically computed values using Spring Pageable

Issue

I have the following entity:

@Entity
@Table(name = "events")
public class Event {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private OffsetDateTime startTime;
    private OffsetDateTime endTime;
    private Long duration;
    
    public void setDuration() {
        if (Objects.nonNull(startTime) && Objects.nonNull(endTime)) {
            duration = Duration.between(startTime, endTime).toSeconds();
        }
    }
}

It represents some event in the system that may last for some time. When new entity is created, startTime is set to OffsetDateTime.now(). duration property is set when event is finished and endTime is set.

When Event entities are fetched, duration is dynamically computed as Duration.between(startTime, OffsetDateTime.now()).toSeconds().

I am trying to fetch Event entities using Specification and Pageable object in order to sort objects easily:

eventRepository.findAll(Specification, Pageable)

As you can imagine, sorting does not work for the events that are not finished (as duration in DB is set to null). Are there any possibilities to somehow set default value of a property while fetching it from DB?

Solution

You can add a @Formula("coalesce(duration, 0)") int durationForSort; field and sort by that if you want this.

Answered By - Christian Beikov