LogoLogo
HomeAPIBlog
2.5.0
2.5.0
  • INTRODUCTION
    • Getting Started
      • Running Local Development servers
      • Beginner Tutorial: Hello World
      • Beginner Tutorial: Hello Database
      • Tutorial: CFWheels, AJAX, and You
    • Frameworks and CFWheels
    • Requirements
    • Manual Installation
    • Upgrading
    • Screencasts
  • Command Line Tools
    • CLI Commands
    • wheels - commands
    • wheels generate - commands
    • wheels dbmigrate - commands
    • wheels plugins - commands
  • Working with CFWheels
    • Conventions
    • Configuration and Defaults
    • Directory Structure
    • Switching Environments
    • Testing Your Application
    • Contributing to CFWheels
    • Documenting your Code
  • Handling Requests with Controllers
    • Request Handling
    • Rendering Content
    • Redirecting Users
    • Sending Files
    • Sending Email
    • Responding with Multiple Formats
    • Using the Flash
    • Using Filters
    • Verification
    • Event Handlers
    • Routing
    • URL Rewriting
      • Apache
      • IIS
      • Tomcat
      • Nginx
    • Obfuscating URLs
    • Caching
    • Nesting Controllers
    • CORS Requests
  • Displaying Views to Users
    • Pages
    • Partials
    • Linking Pages
    • Layouts
    • Form Helpers and Showing Errors
    • Displaying Links for Pagination
    • Date, Media, and Text Helpers
    • Creating Custom View Helpers
    • Localization
  • Database Interaction Through Models
    • Object Relational Mapping
    • Creating Records
    • Reading Records
    • Updating Records
    • Deleting Records
    • Column Statistics
    • Dynamic Finders
    • Getting Paginated Data
    • Associations
    • Nested Properties
    • Object Validation
    • Object Callbacks
    • Calculated Properties
    • Transactions
    • Dirty Records
    • Soft Delete
    • Automatic Time Stamps
    • Database Migrations
      • Migrations In Production
    • Using Multiple Data Sources
  • Plugins
    • Installing and Using Plugins
    • Developing Plugins
    • Publishing Plugins
  • External Links
    • Source Code
    • Issue Tracker
    • Sponsor Us
    • Community
Powered by GitBook
LogoLogo
On this page

Was this helpful?

Edit on GitHub
Export as PDF
  1. Database Interaction Through Models

Getting Paginated Data

Improve database performance and simplify your user interface by using pagination.

PreviousDynamic FindersNextAssociations

Last updated 1 year ago

Was this helpful?

If you searched for "coldfusion" on Google, would you want all results to be returned on one page? Probably not because it would take a long time for Google to first get the records out of its index and then prepare the page for you. Your browser would slow to a halt as it tried to render the page. When the page would finally show up, it would be a pain to scroll through all those results.

Rightly so, Google uses pagination to spread out the results on several pages.

And in Wheels, it's really simple to do this type of pagination. Here's how:

  • Get records from the database based on a page number. Going back to the Google example, this would mean getting records 11-20 when the user is viewing the second results page. This is (mostly) done using the findAll() function and the page and perPage arguments.

  • Display the links to all the other pages that the user should be able to go to. This is done using the function or using a lower-level function .

This chapter will deal with the first part: getting the paginated data. Please proceed to the chapter called if you wish to learn how to output the page links in your view.

Learning by Example

Let's jump straight to an example:

authors = model("Author").findAll(page=2, perPage=25, order="lastName");

That simple code will return authors 26-50 from the database, ordered by their last name.

What SQL statements are actually being executed depends on which database engine you use. (The MySQL adapter will use LIMIT and OFFSET, and the Microsoft SQL Server adapter will use TOP and some tricky sub queries.) Turn on debugging in the ColdFusion Administrator if you want to see exactly what's going on under the hood.

One important thing that you should be aware of is that pagination is done based on objects and not records. To illustrate what that means, we can expand on the above example a little:

authorsAndBooks = model("Author").findAll(
  include="Books", page=2, perPage=25, order="lastName"
);

Here, we tell Wheels that we also want to include any books written by the authors in the result. Since it's possible that an author has written many books, we can't know in advance how many records we'll get back (as opposed to the first example, where we know we will get 25 records back). If each author has written 2 books, for example, we will get 50 records back.

If you do want to paginate based on the books instead, all that you need to do is flip the findAll() statement around a little:

booksAndAuthors = model("Book").findAll(
  include="Author", page=2, perPage=25, order="lastName"
);

Here, we call the findAll() function on the Book class instead, and thereby we ensure that the pagination is based on the books and not the authors. In this case, we will always get 25 records back.

If you need to know more about the returned query, you can use the pagination() function which returns a struct with keys pagination().currentPage, pagination().totalPages and pagination().totalRecords.

That's all there is to it, really. The best way to learn pagination is to play around with it with debugging turned on.

Don't forget to check the chapter .

paginationLinks()
pagination()
Displaying Links for Pagination
Displaying Links for Pagination