Splitting a page with a custom query in slick
I am using Slick to connect to Postgres database in our application. I have a generic filtering logic where the filter object will be passed from the UI and it should return paginated results. The Filter object must be shared to be reusable. The pseudocode of the filter object is shown below:
Filter = {
type: table
prop: List_of_conditions
page : 1
size : 10
}
I am currently creating native SQL from a Filter object and executing it. However, I cannot use take
and drop
before the request is actually executed. It currently gets all the results and then deletes unneeded entries. I know how to do this with slick requests, but not sure how to use pagination with my own requests?
val res = StaticQuery.queryNA[Entity](queryStr).list.drop((filter.pageNo- 1) * filter.pageSize).take(filter.pageSize)
I use Slick 2.1
source to share
When you are using plain sql, you cannot use collection statements to build your 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
source to share