Compiled -- pagination with sortBy

281 views
Skip to first unread message

thoefer

unread,
Nov 19, 2014, 11:30:36 AM11/19/14
to scala...@googlegroups.com
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

unread,
Nov 20, 2014, 3:18:16 AM11/20/14
to scala...@googlegroups.com

Greg Silin

unread,
Aug 24, 2015, 8:35:29 PM8/24/15
to Slick / ScalaQuery
Bump...

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

Stefan Zeiger

unread,
Aug 25, 2015, 7:47:21 AM8/25/15
to scala...@googlegroups.com
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

unread,
Jul 13, 2017, 9:16:24 PM7/13/17
to Slick / ScalaQuery
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

unread,
Oct 17, 2019, 7:35:33 PM10/17/19
to Slick / ScalaQuery
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:
Reply all
Reply to author
Forward
0 new messages