Slick - Compiled with dynamic sortBy

Question

I know as of slick 2.1. one can use ConstColumn for take and drop in precompiled Query's using "Compiled".

private val findXXXCompiled = Compiled { 
    (someId:Column[Long], sortBy:???, drop:ConstColumn[Long], take:ConstColumn[Long]) =>
    val q = findXXX(someId) // returns a Query

    // I want to use query composition on "q" in order to further restrict my result:
    q.sortBy {
      case (_, name, state) => sortBy match {
        case ??? => name.asc
        case ??? => name.desc
        case ??? => state.asc
        case ??? => state.desc
      }
    }.drop(drop).take(take) // possible since slick 2.1. as described above using type ConstColumn
}

The example code above is being triggered by a user from a UI with a table layout. If a user clicks on the "name" header then the table should be sorted according to the "name" - the same for "state".

The aspect I can't get to work is combine precompilation with dynamic sorting (depeding on the clicked header in the table layout). Dynamic sorting of course works when not precompiling a query. But as method "findXXX" is pretty complex I definately need to precompile as of performance reasons. Any hint on how to achive precompilation with dynamic sorting?

See also: https://groups.google.com/forum/#!topic/scalaquery/my4EYt51qEM

Culpepper

The calling code already needs to know what kind of sorting needs to be done (it's passing in the sortBy parameter), so why not have it call a different function for each column? If that's too much overhead, if you move the match up a level and then call a complete sortBy function based on what column you matched on, does that affect precompilation? – Poikilothermic

(old question but has bounty so is there still interest?) naively I'd suggest ConstColumn[String] but guess that won't work. best use a compiled query per sorting and use a map to look them up, that solves your problem - assume that's what you used? guess hard to write without repeating some code but should still be rather compact. like me to make a suggestion? – Detoxify

Answer 1

Slick doesn't provide a way to memoize compiled queries for a dynamic sortBy. I figured the only way to solve this issue would be to memoize the compiled query for each sort column and lookup the query based on the dynamic sort key. i.e., build a map where key is sort column name and value is the compiled query with sortBy for the column.

Of course, this won't work for you if your compiled query has a join and you want to sortBy a column from the table that's being joined.

If performance is not a concern and you have no other reason to use compiled queries, just don't use them.

Kingly