Compiled -- pagination with sortBy

[thoefer]

I know as of slick 2.1. one can use take and drop in precompiled Query's (with "Compiled(...)") involving limit and offset in SQL.

  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

  }

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). see fragments marked with "???". Is that even possible?!

[thoefer]

See also: http://stackoverflow.com/questions/27034653/slick-compiled-with-dynamic-sortby

[Greg Silin]

Bump...

Would be interested how folks tackled this in 3.0 as well.

[Stefan Zeiger]

On 2014-11-19 17:30, thoefer wrote:

The aspect I can't get to work is combine precompilation with dynamic sorting (depeding on the clicked header in the table layout). see fragments marked with "???". Is that even possible?!

It's possible if all sort columns have the same data type or can be converted to a single type for determining sort order. You can use a lifted Case.If... expression to compile the case match to SQL. But this is pretty much guaranteed to give you the slowest possible sort in the database because no information is available at compile time that could make it more efficient. You're better off making the distinction in Scala code and memoizing different compiled versions.

--
Stefan Zeiger
Slick Tech Lead
Typesafe - Build Reactive Apps!
Twitter: @StefanZeiger

[Jon Wu]

Hi Stefan,

I had a similar question, using Slick 3.1.

If all possible sort columns have the same datatype, would you be able to compile a query and make it performant? I wasn't sure if your last comment about slowness applied to that scenario, and was having trouble figuring out how to take dynamic sorts, even with the same type.

For example, what if I want to sort by modified, created, or lastUsed timestamps in either asc or desc order.

Theoretically I wanted to do something like this, but I wasn't sure what typed would be used:

// What would the type of orderBy be?
private val orderedDynamically = Compiled { (orderBy: ???, offset: ConstColumn[Long], limit: ConstColumn[Long]) =>
  CustomerImages.sortBy(_ => orderBy).drop(offset).take(limit)
}

// What would you pass in to specify CustomerImages.modified or CustomerImages.modified.desc?
// Would you use something like, CustomerImages.baseTableRow.modified.desc?
orderedDynamically((???, 40, 10))

Thanks,

Jon

[Sudheer Reddy Aedama]

I have a similar situation. Have you figured out the type of sortBy?

I am using play-slick 3.0.3 (which uses slick 3.2.x). I tried ConstColumn and LiteralColumn which do not work. 

Its been 5 years since this question was asked, I hope some one has figured it out by now! Thanks in advance.

Cheers,

Sudheer

On Wednesday, November 19, 2014 at 8:30:36 AM UTC-8, thoefer wrote: