setPagination()

Signature

setPagination() — returns void

Available in: controller, model, test, migrator, migration, tabledefinition Category: Pagination Functions

Description

Aallows you to define a pagination handle for a custom query so that you can easily generate paginated links and manage page offsets in your views. It’s useful when you want manual or database-driven pagination instead of relying on built-in model queries. This works in combination with the pagination() function to retrieve pagination metadata (like startRow, endRow, maxRows) and paginationLinks() to render links in your view.

Parameters

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.

Examples

/* 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")#