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
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
Defaults and overrides
We can imagine the situation where many of our entities all have
createdAt: ZonedDateTime /
updatedAt: ZonedDateTime fields, and we would
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.
Ok so we know what we want. How does this fit into Slick’s types?
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
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
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
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
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
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
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
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
desc — it
all depends on the context of your model.