I blogged previously about Server-Side Paging with PetaPoco and DataTables, which works well for about 80% of the SQL statements that require server-side paging. In fact, I already submitted a pull request in December 2011 to modify the rxOrderBy Regex used by PetaPoco during Page<T> query rewrite to support sub-select ordering containing ROW_NUMBER() PARTITION BY.
The latest query from the 20% unsupported category prompted my pull request to bypass the PetaPoco query rewrite logic entirely and allow the caller to provide the count and paging queries. After all, PetaPoco is a single-file micro-ORM that expects the user to understand SQL already. The secret sauce covers the low friction data access layer between the database and your POCOs, and not the SQL itself.
Currently, PetaPoco supports server-side paging with the following Page<T> method overloads that generate count and page results queries from a single SQL statement:
- public Page<T> Page<T>(long page, long itemsPerPage, Sql sql)
- public Page<T> Page<T>(long page, long itemsPerPage, string sql, params object args)
My pull request simply adds another Page<T> method overload that accepts the two SQL statements to use for the count and page results without calling the BuildPageQueries<T> method: public Page<T> Page<T>(long page, long itemsPerPage, Sql sqlCount, Sql sqlPage)
The following gist provides a sample set of queries using the new Page<T> overload:
There has not been much activity in the PetaPoco GitHub repository in over 8 months, so you might want to pull this change into your own fork if faced with similar server-side paging requirements.