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

Yes, typical mysql-based method needs two queries: one with select count(*) from table, second with select a, b, c from table limit x,y where x and y are build depending on current page and needed offset.

With single query you'll need to select all records, which is less efficient approach in most cases.

Answer 2

I don't have as much familiarity with MySQL, but I know that in MS SQL a stored procedure can return multiple result sets.

If you cannot do this, there are the two ways you suggested, but the second can be optimized.

First, run two queries. One to get the data for the currently displayed page, one to get the total size of the resultset. This is what I would do if the data changes frequently, or if multiple pages are not usually displayed. This requires two db calls, but reduces the total size of the return. If you don't change data freqauently, but rarely use more than a few pages per query, you could also cache the total result count.

The second is to return all results in one big query, and only display the appropriate page. You don't need a query of queries here, as the query loops using cfloop and cfoutput can specify beginning and ending query rows. If you do this, I would cache the query. This uses more memory, and the first page load will be slower, but you'll have fast page loads on subsequent pages.

Answer 3

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 4

In MS SQL, use CTE:

receive the arguments @startRow and @endRow plus your needed arguments

WITH qTemp AS (
   select *
   ,   count(*) as totCnt
   ,   rowNum = ROW_NUMBER() OVER(ORDER BY myIndex ASC)
   from myTable
   where (my where clause)
)
SELECT * FROM qTemp
WHERE rowNum BETWEEN @startRow AND @endRow

Answer 5

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 6

I believe you can do:

SET @myCount = (SELECT COUNT(*) FROM tblName);

SELECT col1, col2, @myCount as 'total'
FROM tblName

With your appropriate pagination stuff in place on the second query. You'll get the count on every record in your returned set, but that's not really a big deal. I didn't test this though, I don't have MySQL.

Answer 7

while this isn't the BEST way to do this, it is the old school way.

you can accomplish this by using cfoutput.

<cfquery name="q" dsn="#application.dsn">
SELECT
  firstname,
  lastname
FROM
  mytable
WHERE
  <some conditions>
</cfquery>

<cfparam name="url.maxrows" default="50">
<cfset url.maxrows = fix(val(url.maxrows))>
<cfparam name="url.startrow" default="1">
<cfset url.startrow = fix(val(url.startrow))>
<cfif url.startrow lte 0>
  <cfset url.startrow = 1>
</cfif>
<cfif url.startrow gte q.recordcount>
  <cfset url.startrow = q.recordcount>
</cfif>

<cfoutput>Total Records: #q.recordcount#/<cfoutput>

<cfoutput query="q" maxrows="#url.maxrows#" startrow="#url.startrow#">
  #firstname#<br/>#lastname#
</cfoutput>

<p>Page: 
<cfloop from="1" to="#q.recordcount#" step="#url.maxrows#" index="i">
  <a href="#cgi.script_name#?startrow=#i#&maxrows=#url.maxrows">#(i / url.maxrows)#</a>
</cfloop>
</p>

now to be honest... this method sucks and is REALLY slow for big queries. personally i use CFWheels that has all of this stuff built into it's ORM.