Paging

From Drillbridge Wiki
Jump to: navigation, search

This section discusses paging in Drillbridge versions 1.3.3 and later. Earlier versions of Drillbridge included a paging feature that has now been deprecated. Documentation for old-style paging can be found here, although you are encouraged not to use this old-style of paging and it will only work on Drillbridge versions earlier than 1.3.3.

As of Drillbridge 1.3.3, paging has made a comeback. Paging is implemented in a server-side manner. This means that the client will load the current results from the query, and if the next page/previous page button is clicked, a new SQL request will be made to the relational database in order to retrieve the relevant results. This is in contrast to earlier versions of Drillbridge that just pull back the entire result set. Implementing server-side paging is straightforward but requires a few steps.

Setting up paging

There are three steps needed to implement paging

  1. Turn on the "Enable paging" option on the report administration page
  2. Enter a row-counting query for the report
  3. Rework the main query to include the two paging tokens

Row counting query

In order to implement paging, Drillbridge requires that the exact number of rows for a query be known. This allows the Drillbridge interface to determine how many pages of data there are. Rather than attempt to guess how many rows there are in the result set, Drillbridge reports with paging enabled require what's known as a "row counting query". The row counting query is syntactically very similar to the main query, but typically is modified to be a variant of the main query using COUNT(*) instead of the normal items in the SELECT portion of the query. The SELECT COUNT(*) query is executed on the initial drill request for a report with paging enabled. The value is then cached for future pages so that the user doesn't have to wait for the query to run (the SELECT COUNT(*) query may take some time to execute).

Rework the main query to include the two paging tokens

Reports with paging enabled must include two additional tokens in order to work: %%OFFSET%% and %%LIMIT%%. The offset token is the row offset to start with. This will be 0, 10, 20, or some multiple of the current page size. The limit token is the number of rows to return. A simplistic implementation of the offset/limit in a MySQL query would look like this:

SELECT * FROM Transactions LIMIT %%OFFSET%%, %%LIMIT%%

Note that the LIMIT syntax is part of MySQL. Other database variants such as Microsoft SQL Server and Oracle have different implementations for getting certain rows. Lastly, do note that the MySQL LIMIT syntax, as indicated above, is simplistic: it can have performance issues in larger result sets because it has to scan all of the rows prior to the offset row. There are advanced ways of writing the query (that would still utilize the offset/limit tokens) that can perform better.