How can I paginate a ColdFusion recordset in cfscript

Question

I want tot do the equivalent of:

<cfoutput query="queryName" startow="startrow" maxrows="maxrows">
     ......
</cfoutput>

using cfscript.

My code so far (in a function) is:

_sqlStatement = 'SELECT first_name, surname, mail_address 
         FROM   usersTable
         WHERE  1=1
         AND    first_name LIKE :searchText
         OR     surname  LIKE :searchText
         OR     mail_address LIKE :searchText
         ORDER BY #arguments.sortname# #arguments.sortorder#'; 

_qryGetUsers.setSQL(_sqlStatement);
_qryGetUsers.addParam(name="searchText",value="%" & #arguments.searchText# & "%",cfsqltype="CF_SQL_varchar");
_qryGetUsers.setDatasource(myDataSourceName);
qUsers = _qryGetUsers.execute().getResult();

 //cfoutput equivalent
 for(x=_startRow; x LTE qUsers.recordcount; x++)
 {
       ......
 }

Thanks.

Answer 1

In SQL Server 2012 you can use OFFSET and FETCH to specify the start row and number of rows respectively

_sqlStatement = '
  DECLARE @OffsetRows tinyint = :offset
  DECLARE @FetchRows tinyint = :fetch

  SELECT first_name, surname, mail_address 
  FROM   usersTable
  WHERE  1=1
     AND    first_name LIKE :searchText
     OR     surname  LIKE :searchText
     OR     mail_address LIKE :searchText
  OFFSET @OffsetRows ROWS
  FETCH NEXT @FetchRows ROWS ONLY
  ORDER BY #arguments.sortname# #arguments.sortorder#';

see http://dbadiaries.com/new-t-sql-features-in-sql-server-2012-offset-and-fetch

Answer 2

If you just want the CFScript equivalent:

qUsers = _qryGetUsers.execute().getResult();
_startrow=1;
_maxrows=10;

for(i=_startRow; i <= qUsers.recordcount && i - _startRow < _maxrows; i++)
{
    writeOutput(qUsers.['name'][i] & "<br>");
}

If you're generating HTML/XML, <cfloop query= in CFML would be easier and more readable.

Answer 3

It is not different in cfscript as simple coldfusion. I am providing you any idea of logic of pagination in coldfusion.

see the code of pagination below

<cfset limt = 50>
 <cfquery name="getCount" datasource="#application.datasource#">
     select COUNT(*) as cont from 
      ZIPCodes
 </cfquery>

   <cfset PagAddress =  "#CGI.PATH_INFO#">
<cfset totalRowsCount = getCount.cont>
<cfparam name="pre" default="0">    
<!--- End category st atistics --->
<cfparam name="url.grp" default="1">
<!--- records to display per page --->
<cfset displayrow = 10>
<!--- gets the number of pages --->
<cfset lop = (totalRowsCount\displayrow)>
<cfif (totalRowsCount MOD displayrow) GTE 1>
  <cfset lop = lop + 1>
</cfif>

<cfif isdefined("url.grp")>
  <cfset startrows = ((url.grp - 1)*displayrow)+1>
  <cfelse>
  <cfset startrows = 1>
</cfif>
<cfset endrows = (startrows + displayrow)-1>
<cfif endrows GTE totalRowsCount>
  <cfset endrows = totalRowsCount>
</cfif>
<cfsavecontent variable="pagination_HTML">
<cfoutput>

<div class="pagination_main_cls">
<div class="pagenation" style="width:800px;">
  <div class="jobs_days2">
      <cfif totalRowsCount GT 0><b>#startrows#&##45;#endrows#</b> of  <b>#totalRowsCount#</b> Jobs     </cfif>

 </div>
 <div class="pagenation_numb" >

    <cfif url.grp gt 1>
        <cfset pre = url.grp -1>
            <a href="#PagAddress#?grp=#pre#" title="Previous">&lt;Prev</a>
    </cfif>

    <cfset DisplayPageNumCount = 5>

    <cfif #lop# LTE DisplayPageNumCount and #url.grp# LTE DisplayPageNumCount>
        <cfset loop_from_val = 1>
        <cfset loop_to_val = #lop#>
    <cfelseif #lop# GT DisplayPageNumCount and #url.grp# LT DisplayPageNumCount>    
        <cfset loop_from_val = 1>
        <cfset loop_to_val = DisplayPageNumCount>   
    <cfelse>
        <cfset loop_from_val = #url.grp# - (DisplayPageNumCount-1)>
        <cfset loop_to_val = #url.grp#>
    </cfif>

    <cfloop index="group" from="#loop_from_val#" to="#loop_to_val#">
        <cfif lop GT 1>
        <!---<cfif url.grp is group>class="active"</cfif>--->
            <cfif url.grp is group>
                <a href="#PagAddress#?grp=#group#">#group#</a>
            <cfelse>                    
                [<a href="#PagAddress#?grp=#group#">#group#</a>]
            </cfif>
        </cfif>
    </cfloop>
    <cfif url.grp lt lop>    
        <cfif lop GT 1 AND lop gt DisplayPageNumCount>&hellip;</cfif> 
        <cfset nex = url.grp +1>        
            <a href="#PagAddress#?grp=#nex#" title="Next">Next &gt;</a>       
    </cfif>
    </div>                              
    </div>
    </div>

</cfoutput>
</cfsavecontent>


<cfquery name="getMember" datasource="#application.datasource#">
    SELECT *
    FROM ( 
        SELECT   ZIPCode, ZIPType, myrow = ROW_NUMBER() OVER (ORDER BY ZIPCode)
        FROM         ZIPCodes
        )p
        WHERE myrow BETWEEN #startrows# AND #endrows#
</cfquery>


<table border="1" cellpadding="0" cellspacing="0" >
    <tr>
        <th>Count</th>
        <th>ZIPCode</th>
        <th>ZIPType</th>
    </tr>

    <cfoutput query="getMember" >
        <tr>    
            <td>#myrow#</td>
            <td style="width:80px; text-align:left">
                #ZIPCode#                   
            </td>
            <td style="width:600px; text-align:left">
                #ZIPType#                       
            </td>
        </tr>
    </cfoutput>

</table>
<cfoutput>
    #pagination_HTML#
</cfoutput>