Static Sorting in Slick

How to pass sorting all the way through your Scala/Slick stack

When writing our project, we wanted to pass a static sorting object all the way through our stack from the API (where it was parsed automatically) to Slick, and have the database results be sorted automatically without any extra input from the end-user (ie you, the feature-developer).

(In this post I will assume you are familiar with Slick 3 and will always have the appropriate slick implicits and classes in scope.)

It isn’t obvious how to make slick do this automatic sorting for us. Imagine this table set up:

Just a table to store my dogs in. Now imagine I had a static Sorting type, and I wanted to sort a dog query by an object of type Sorting which I’d been supplied by our API:

That’s a pretty horrendous query. To interpret these sorting objects properly we’d have to do that on every single query.

If we ever added a new Sorting type we’d need to change every single dogs query to handle it correctly — and there’s no guarantee an Id sort would be interpreted the same way across any two dogs queries. There would be nothing stopping someone sneakily mapping Sorting.Id to Dog.name .

Ideal Syntax

So what do we actually want? As an end-user of our codebase, we want to be able to do this:

We would want this, somehow, to access a pre-defined mapping of the supplied sorting to the table columns. This would ensure every dog query would interpret Sorting.Id in the same way. We will define this mapping later.

And if we have told the compiler how to sort dogs by some Sorting but not all Sortings it should gracefully ignore sortings it’s not able to handle:

Lastly, if we haven’t told the system how to handle any Sorting for a table, it should fail compilation:

The above shouldn’t compile since in our imagined perfect world, we’ve only created the mapping for dogs.

Defaults and overrides

We can imagine the situation where many of our entities all have createdAt: ZonedDateTime / updatedAt: ZonedDateTime fields, and we would
have corresponding Sorting objects available for use.

We don’t want to have to write this out for every table however, since it will always be the same. So we need to be able obtain common functionality either through inheritance or a typeclass interface. I chose inheritance for
my implementation since our tables are joined by inheritance (with a shared ancestor HasUpdatedCreated — this could easily be a typeclass instead).

And in addition to this, we must be able to override this default for any particular table we choose. Just in case dogs.createdAt is filled with nonsense data.

Implementation

Ok so we know what we want. How does this fit into Slick’s types?

The Sorting type mutates a query, so on the most basic level what we need is some sort of construct representing a function (Query[T, E, Seq], Sorting) => Query[T, E, Seq] (here Query is Slick’s Query type).

This is equivalent to Query[T, E, Seq] => (Sorting => Query[T, E, Seq]). To see this, curry the initial function and imagine partially applying it with the initial Query only.

But we’d actually like this to be lazily implemented. Lazy as in ‘least amount of work for future developers’. Imagine if when we added a new Sorting we had to go and add it in to each one of these mappings. It would be a nightmare, especially if we had dozens of tables. We could even add a special sorting, Sorting.Unique, which we only want to interpret on one table. We should be able to have these mappings gracefully fall back to no sorting at all.

So what we actually want is something which looks a little like this:

which makes sense: If we don’t implement every sort then it’s a partial and not a total function.

But when we sort a query by a Sorting that has no implementation for our table, we don’t want to return None, we want to return an unmutated Query. So we must have two layers, the inner of which contains a partial function and
the outer of which contains a total function, ie the original type:

So let’s have a stab at it:

TableSortingOps looks a bit crowded, but all sortedBy is doing is checking the user’s top level implementation of sort, and falling back to the hidden partialSort if it’s not defined. It then returns the original query if we still didn’t find a match.

And that’s about it. Let’s implement some default sorts:

There’s nothing surprising there, we just define a mix-in sorter trait for a partial table.

Now let’s finally implement our Dog instance:

And now, assuming all the implicits we just defined are scope, we can finally do the following:

You don’t need to worry about what sorting is or how to interpret it, it’s all done automatically in the background based on your one-time initial setup. You pass it straight from the API and let the edges of your program handle it.

What would happen if we added a new sort, Sorting.Name and did not add this to dogSorter?

This would ignore the sort and return dogs.result. This is equivalent to sorting in a sql query by a constant: select * from dogs order by 19;

This, to me at least, is an important separation of concerns. There are two pieces of work: Implement an API that sorts and returns dogs, and interpret sortings for dogs correctly. A bug or omission in the latter does not mean the former is incorrect, and is arguably a completely separate piece of work with different input needed from the business.

And, finally, what happens if you add a new table, cats, and do not implement a Sorter[Cats, Cat]?

It doesn’t compile! You must implement a Sorter[Cats, Cat] before you can sort statically. It could be empty if you like, but you must at some point in the development of this cats query make the choice about how to sort.

If it instead just silently returned the query unsorted it would be confusing to work out which tables actually had a Sorter implemented; the fact sortedBy(sorting) compiles for a table is indication to you that someone has put thought how to sort the table so you don’t have to.

In other words, a prerequisite for sorting correctly is “Someone must have thought about how to sort things correctly” — which is obvious. The implicit sorter being in scope is how we tell the compiler that this requirement has been satisfied.

Asc or Desc?

The above has a glaring omission — you cannot specify asc or desc. Sorting.Id always goes to just the one thing.

I left it out for clarity and brevity; there are many ways you could fit the functionality in. The way we did it was to make eg Sorting.Id contain two properties asc and desc and then our sorters look like this:

It’s more verbose and not too pretty, but in the case of nullable columns it’s important to have this distinction because you need to choose whether null sorts high or low, and it needn’t be opposite cases for asc and desc — it
all depends on the context of your model.