Pagination in databases, the division of query results into manageable subsets, is often implemented in terms of SQL constructs like LIMIT or FETCH FIRST, and OFFSET. This works well when the result is compromised of one-to-one relationships. On the other hand, hypothesise what happens if the relationship is a one-to-many as in the following DDL [1]:
An author can write n books as expressed in the foreign key constraint declared on Book’s authorId column. A SELECT * FROM statement joining the two tables by the author’s ID will produce a row for each book the author has written. This means that if you have 3 authors and each one has authored 3 books, the query will return 9 rows:
An attempt at formulating a query that sorts the result by author name and fetches the result’s first page, were the page can have at most 2 authors, might go like this:
Perhaps to one’s surprise, the above SELECT statement, with a LIMIT of 2 and an OFFSET of 0, will only return books authored by Antonopoulos:
The books authored by Chomsky as well as Antonopoulos’s book, ‘Mastering Ethereum’, weren’t included in the result because it’s the many-side of the relationship (i.e., books) which is paginated as opposed to the one-side (i.e., authors). Paging the one-side of a one-to-many relationship isn’t a trivial problem to solve in pure SQL without re-writing part of the query as a sub-select:
In practice, I find it inconvenient to formulate a query in this way in order to accommodate pagination. It doesn’t lend itself easily to runtime string manipulation, and therefore, automating paging, so that the developer isn’t forced to think about limits/offsets every time he writes a query, is harder.
Most popular vendor DBMSs offer window functions for performing calculations over ranges of rows. One such function is DENSE_RANK. This function ranks each row against the rest of the rows to produce a sequence of numbers starting from 1. Rows equal in rank have the same sequence number. DENSE_RANK is an alternative solution for the one-to-many pagination problem as it helps us simulate logical counts and offsets based on the author’s primary key column:
This SELECT statement uses DENSE_RANK combined with ORDER BY to produce rows such that row x and y have the same rank if and only if row x’s author ID and name are equal to row y’s author’s ID and name:
With the offset_ rank column, we can scroll forwards or backwards taking into account that duplicate authors in the result have the same rank. Say we want to get records starting from offset 1. Keeping in mind rank = offset + 1, this requirement would be simply expressed as:
To add a logical count, another rank, representing the count, is re-computed over the sub-select’s result:
In the beginning of this post, we wanted to fetch the first page of the result with a limit of 2 authors. Here’s how to achieve this with DENSE_RANK:
Note how DENSE_RANK allows us to more or less wrap around the main query rather than changing it. We can even take this one step further:
By moving the computation of the offset rank outside of the main query, we have the possibility to easily write string manipulation code in the application that dynamically, and transparently, applies pagination to queries.
Edit (27/06/2017): Upon revisiting the final query, I realised that it can be reduced to:
2: The described solution won't solve the one-to-many paging problem should the result be ordered by a column in the Book table. I'll leave it as an exercise for the reader to figure out why that's the case.