Paginating with Coldfusion

问题:

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?

回答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.

回答2:

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

回答3:

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.

回答4:

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

回答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.

回答6:

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>

回答7:

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.

回答8:

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.

回答9:

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.