Return to v2.1 docs

setPagination()


Controller Pagination Functions void controller model migrator migration tabledefinition


Allows you to set a pagination handle for a custom query so you can perform pagination on it in your view with paginationLinks.

Name Type Required Default Description
totalRecords numeric Yes Total count of records that should be represented by the paginated links.
currentPage numeric No 1 Page number that should be represented by the data being fetched and the paginated links.
perPage numeric No 25 Number of records that should be represented on each page of data.
handle string No query Name of handle to reference in paginationLinks.
/* Note that there are two ways to do pagination yourself using a custom query.
1) Do a query that grabs everything that matches and then use
the `cfouput` or `cfloop` tag to page through the results.
2) Use your database to make 2 queries. The first query
basically does a count of the total number of records that match
the criteria and the second query actually selects the page of
records for retrieval.
In the example below, we will show how to write a custom query
using both of these methods. Note that the syntax where your
database performs the pagination will differ depending on the
database engine you are using. Plese consult your database
engine's documentation for the correct syntax.
Also note that the view code will differ depending on the method
used.
*/
//=================== First method: Handle the pagination through your CFML engine
// Model code: In your model (ie. User.cfc), create a custom method for your custom query
function myCustomQuery(required numeric page, numeric perPage=25){
local.customQuery=QueryExecute("SELECT * FROM users", [], { datasource=get('dataSourceName') });
setPagination(
totalRecords=local.customQuery.RecordCount,
currentPage=arguments.page,
perPage=arguments.perPage,
handle="myCustomQueryHandle");
return local.customQuery;
}
// Controller code
function list(){
param name="params.page" default="1;
param name="params.perPage" default="25";
allUsers = model("user").myCustomQuery( page=params.page, perPage=params.perPage);
// Because we're going to let `cfoutput`/`cfloop` handle the pagination,
// we're going to need to get some addition information about the pagination.
paginationData = pagination("myCustomQueryHandle")
}
<!--- View code (using `cfloop`): Use the information from `paginationData` to page through the records --->
<ul>
<cfloop query="allUsers" startrow="#paginationData.startrow#" endrow="#paginationData.endrow#" >
<li> #allUsers.firstName# #allUsers.lastName# </li>
</cfloop>
</ul>
#paginationLinks(handle="myCustomQueryHandle")#
<!--- View code (using `cfoutput`) Use the information from `paginationData` to page through the records--->
<ul>
<cfoutput query="allUsers" startrow="#paginationData.startrow#" maxrows="#paginationData.maxrows#" >
<li> #allUsers.firstName# #allUsers.lastName# </li>
</cfoutput>
</ul>
#paginationLinks(handle="myCustomQueryHandle")#
//=================== Second method: Handle the pagination through the database
// Model code: In your model (ie. `User.cfc`), create a custom method for your custom query
function myCustomQuery(required numeric page, numeric perPage=25){
local.customQueryCount=QueryExecute("SELECT COUNT(*) AS theCount FROM users",
[], { datasource=get('dataSourceName') });
local.customQuery=QueryExecute("SELECT * FROM users LIMIT ? OFFSET ?",
[arguments.page, arguments.perPage],
{ datasource=get('dataSourceName') });
//Notice the we use the value from the first query for `totalRecords`
setPagination(
totalRecords=local.customQueryCount.theCount,
currentPage=arguments.page,
perPage=arguments.perPage,
handle="myCustomQueryHandle" );
// We return the second query
return local.customQuery;
}
// Controller code
function list(){
param name="params.page" default="1;
param name="params.perPage" default="25";
allUsers = model("user").myCustomQuery( page=params.page, perPage=params.perPage);
}
<!--- View code (using `cfloop`)--->
<ul>
<cfloop query="allUsers">
<li> #allUsers.firstName# #allUsers.lastName# </li>
</cfloop>
</ul>
#paginationLinks(handle="myCustomQueryHandle")#
<!--- View code (using `cfoutput`)--->
<ul>
<cfoutput query="allUsers">
<li> #allUsers.firstName# #allUsers.lastName# </li>
</cfoutput>
</ul>
#paginationLinks(handle="myCustomQueryHandle")#

Related Functions

Pagination Functions