{"__v":1,"_id":"55097ab2ad1f0523008ecbd6","category":{"__v":16,"_id":"550974cc368a56170041475c","project":"55070e814bb83b2500ec9404","version":"550974cb368a561700414757","pages":["55097a674c7c3f2300aabf07","55097a8a4c7c3f2300aabf09","55097a92ad1f0523008ecbd4","55097a9faa9bd525001a065c","55097aa92dd6a11900e6e7b4","55097ab2ad1f0523008ecbd6","55097ac74c7c3f2300aabf0b","55097ace2dd6a11900e6e7b7","55097ad5dd77250d007369fa","55097adead1f0523008ecbd9","55097ae72dd6a11900e6e7b9","55097aefdd77250d007369fc","55097af8dd77250d00736a05","55097aff4c7c3f2300aabf0d","55097b07aa9bd525001a0660","55097b11dd77250d00736a07"],"reference":false,"createdAt":"2015-03-18T11:08:27.090Z","from_sync":false,"order":4,"slug":"database-interaction-through-models","title":"Database Interaction Through Models"},"project":"55070e814bb83b2500ec9404","user":"55070d24d30b3f190011b941","version":{"__v":1,"_id":"550974cb368a561700414757","forked_from":"55070e814bb83b2500ec9407","project":"55070e814bb83b2500ec9404","createdAt":"2015-03-18T12:51:23.709Z","releaseDate":"2015-03-18T12:51:23.709Z","categories":["550974cc368a561700414758","550974cc368a561700414759","550974cc368a56170041475a","550974cc368a56170041475b","550974cc368a56170041475c","550974cc368a56170041475d","550974cc368a56170041475e"],"is_hidden":false,"is_beta":false,"is_stable":true,"codename":"","version_clean":"1.4.0","version":"1.4"},"updates":[],"createdAt":"2015-03-18T13:16:34.240Z","link_external":false,"link_url":"","githubsync":"","sync_unique":"","hidden":false,"api":{"results":{"codes":[]},"auth":"required","params":[],"url":""},"order":7,"body":"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 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.\n\nRightly so, Google uses pagination to spread out the results on several pages.\n\nAnd in Wheels, it's really simple to do this type of pagination. Here's how:\n\n* 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.\n* Display the links to all the other pages that the user should be able to go to. This is done using the `paginationLinks()` function or using a lower-level function such as `paginationHasNext()`. (See the \"Related\" section below for more info.)\n\nThis chapter will deal with the first part: getting the paginated data. Please proceed to the chapter called [Displaying Links for Pagination](doc:displaying-links-for-pagination) if you wish to learn how to output the page links in your view.\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Learning by Example\"\n}\n[/block]\nLet's jump straight to an example:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset authors = model(\\\"Author\\\").findAll(page=2, perPage=25, order=\\\"lastName\\\")>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nThat simple code will return authors 26-50 from the database, ordered by their last name.\n\nWhat 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.\n\nOne 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:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset\\n    authorsAndBooks = model(\\\"Author\\\").findAll(\\n        include=\\\"Books\\\", page=2, perPage=25, order=\\\"lastName\\\"\\n    )\\n>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nHere, 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.\n\nIf you do want to paginate based on the books instead, all that you need to do is flip the `findAll()` statement around a little:\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"<cfset\\n    booksAndAuthors = model(\\\"Book\\\").findAll(\\n        include=\\\"Author\\\", page=2, perPage=25, order=\\\"lastName\\\"\\n    )\\n>\",\n      \"language\": \"text\"\n    }\n  ]\n}\n[/block]\nHere, 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.\n\nThat's all there is to it, really. The best way to learn pagination is to play around with it with debugging turned on.\n\nDon't forget to check the chapter [Displaying Links for Pagination](doc:displaying-links-for-pagination).","excerpt":"Improve database performance and simplify your user interface by using pagination.","slug":"getting-paginated-data","type":"basic","title":"Getting Paginated Data"}

Getting Paginated Data

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

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 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 `paginationLinks()` function or using a lower-level function such as `paginationHasNext()`. (See the "Related" section below for more info.) This chapter will deal with the first part: getting the paginated data. Please proceed to the chapter called [Displaying Links for Pagination](doc:displaying-links-for-pagination) if you wish to learn how to output the page links in your view. [block:api-header] { "type": "basic", "title": "Learning by Example" } [/block] Let's jump straight to an example: [block:code] { "codes": [ { "code": "<cfset authors = model(\"Author\").findAll(page=2, perPage=25, order=\"lastName\")>", "language": "text" } ] } [/block] 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: [block:code] { "codes": [ { "code": "<cfset\n authorsAndBooks = model(\"Author\").findAll(\n include=\"Books\", page=2, perPage=25, order=\"lastName\"\n )\n>", "language": "text" } ] } [/block] 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: [block:code] { "codes": [ { "code": "<cfset\n booksAndAuthors = model(\"Book\").findAll(\n include=\"Author\", page=2, perPage=25, order=\"lastName\"\n )\n>", "language": "text" } ] } [/block] 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. 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 [Displaying Links for Pagination](doc:displaying-links-for-pagination).