Sorting by a Non-Entity Field

In my work for SceneSat I recently came across the need to sort entries of a table by a field that does not exist in said table but is built on the fly from other fields. Specifically, depending on the status of a show I want to use either the scheduled start time or the actual start time to sort it by.

Turns out, Spring-Data makes this incredibly easy. I already had a JpaRepository for accessing the table (using a Pageable to allow easy paging and sorting from a REST interface) so first I had to add a @Query:

@Query(value =
	"SELECT
		s AS show,
		CASE
			WHEN (s.status = 'scheduled')
       			THEN s.scheduledStart
       		ELSE
       			s.actualStart
       	END AS time
	FROM Show s")
fun findShows(pageable: Pageable): Page<Show>

You can even add additional WHERE or other clauses to the JPQL query, and Spring-Data will happily merge it with whatever you have in the Pageable.

However, now the return value is not really correct anymore. Spring-Data will return a tuple here and you will only realize that once you want to use your Show objects but are suddenly facing a ClassCastException. Also, even if the result was a show you would have now lost the time field (which may or may not be a problem).

JPA allow you to return non-entity data from queries such as these (called “projections” in section 4.8.2 in JSR 338 aka JPA 2.1), and Spring-Data extends this concept with its usual “just give me an interface and I’ll do the rest” magic (which I’m pretty much in awe of after finding out all of this).

interface ShowWithTime {
  val show: Show,
  val time: Date?
}

Now this type can be used as return type for the findShows method:

fun findShows(pageable: Pageable): Page<ShowWithTime>

And, even more miraculously, you can even use the time in your Pageable:

val shows = findShows(PageRequest.of(0, 10, Sort.by(desc("time"))))

I can’t really believe that this feature has managed to evade me for all this time but I’m really glad I stumbled upon it by blind luck!

2 replies on “Sorting by a Non-Entity Field”

With plain Java interface it is important to define projection interface with “get” methods:

interface ShowWithTime {
Show getShow();
Date getTime();
}

And in SELECT use javabeans name “show” like this: “SELECT s AS show, ..”

Wihtout “get” I got “is no access method” errors (trace includes TupleBackedMap).

So DO NOT USE like this in plain java interface:

interface ShowWithTime {
Show show();
Date rime();
}

Yes, absolutely. Luckily when you define a field “show” in Kotlin it turns it into a backing field and an accessor named “getShow” so that JPA is happy. 😄

2 replies on “Sorting by a Non-Entity Field”

Leave a Reply Cancel reply