Paginating with Coldfusion

Question

Is it possible to paginate and display number of pages in Coldfusion using only one query?

My understanding is you can obviously paginate with one query, but you would need an additional query to create the pages. This is in order to calculate the total number of results.

(currentPage - 1) * resultsPerPage = Offset in MySQL query. This logic is sufficient to create next/prev buttons. But in order to know the number of pages, would we not need to know the total number of results using a separate query, then a query of queries for the data?

Answer 1

You can simply use CFGrid to display the entire result set. Pagination is baked-in. Here's a link to the docs: http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7baf.html

Answer 2

What I have done is to do the "big" query and store the results in the SESSION scope, then do query-of-query on each page. Fewer calls to the database, which is usually where the bottlenecks are anyway.

Answer 3

In MySQL use SQL_CALC_FOUND_ROWS. For example:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl WHERE x = y LIMIT s,m

Right after your query query again for:

SELECT FOUND_ROWS() AS TotalRows

Using the directive SQL_CALC_FOUND_ROWS causes MySQL to store the total number of rows found for the last query before applying the limit and range. This is the same as doing a Count() on the same query without the overhead of actually having to count, then query, thne limit the query.

Wrap the two db calls in a transaction to ensure they execute consecutively. You might also think about abstracting the queries into a function that will do both and return a single struct with a results key and total rows keys. Use the results as cited above to generate pagination links as you see fit.