CODEWITHSUNDEEP
postgresqlscalapaginationslick
Yadu Krishnan
Yadu Krishnan

Reputation: 3522

Pagination with native query in slick

I am using Slick to connect to Postgres Database in our application. I have a generic filtering logic, where a Filter object will be passed from the UI, and it should return results with pagination. The Filter object should be generic so that it can be re-used. Pseudo code of filter object is given below: 

Filter = {
type: table
prop: List_of_conditions
page : 1
size : 10
}

Currently, I am building a native SQL from the Filter object and executing it. However, I am not able to use take and drop before the query is actually getting executed. It is currently getting all the results, and then dropping the unnecessary records. I know how to do it with the slick queries, but not sure how to use pagination with the native queries?

val res = StaticQuery.queryNA[Entity](queryStr).list.drop((filter.pageNo- 1) * filter.pageSize).take(filter.pageSize)

I am using Slick 2.1

Upvotes: 3

Views: 1378

Answers (2)

Artsiom Miklushou
Artsiom Miklushou

Reputation: 754

I haven't tested it but i would suggest you to try to move .list call to the end

val res = StaticQuery.queryNA[Entity](queryStr).drop((filter.pageNo- 1) * filter.pageSize).take(filter.pageSize).list

Upvotes: 0

cvogt
cvogt

Reputation: 11270

When you are using plain sql, you can't use the collection operators to build a query. You have to do it all in SQL:

val limit = filter.pageSize
val offset = (filter.pageNo- 1) * filter.pageSize
val res = StaticQuery.queryNA[Entity](queryStr ++ s" LIMIT $limit OFFSET $offset").list

Upvotes: 1

Related Questions