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

This is typically how I do it. You can select the count of the table by doing a sub-select and aliasing it. You can see it as the third colum in this query. This could be used to even grab data from another table if you wanted to. it does cause MySQL to initiate another query in the middle of your query to assemble the data, but coldfusion only needs to ask the database once making it faster than 2 separate queries.

    <cfquery name="SomeQry" datasource="YourDatabase">
    SELECT 
    col1, 
    col2,
    (SELECT COUNT(*) FROM MyTable) as mycount
     FROM MyTable
     LIMIT #(CurrentPage * 20) - 20#, 20
</cfquery>

         <cfset start = CurrentPage - 5>
         <cfset Max = CurrentPage + 5>

        <cfoutput>
           <ul class="pagination">
               <cfloop from=#start# to=#Max# index="page">
                     <li><a href="/search?q=#querystring#&page=#page#">#page#</a></li>
            </cfloop>
          </ul>
        </cfoutput>

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

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 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

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 6

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.