A few weeks ago, I was working on a small application for my wife that will allow her to keep track of the books that my kids have read. It put it together quickly using Transfer and Model-Glue utilizing Model-Glue’s scaffolding for most of the lists and forms. Everything was running smoothly and my wife was happy
However, last week, she came to me and asked if there was a way to make it so the book list could be paginated (OK, she did not use those words, but that is what she was essentially asking for). So, I started tweaking the application to stop using generic database messages in Model-Glue and adding a true service layer that interacted with Transfer to return a query object of books. I started to code the tedious (in my mind) task of handling pagination when I realized there is an easier way, queryConvertForGrid(). I figured if it works for doing pagination with cfgrid, why not use it elsewhere? I already had the page number and page size, so instead of doing the normal query of queries, and using startRow and maxRow in cfoutput, I simply used queryConvertForGrid().
For those who may not know what queryConvertForGrid() is, it is a function in ColdFusion 8 that takes 3 arguments (a query object, a page number and the page size) and returns a structure that is in a format that is used to populate a cfgrid. The structure contains 2 keys: ‘TotalRowCount’ which is the number of items in the original query and ‘Query’ which is a query object that contains ‘page’ of the query that matches the page number and page size arguments
Here is some sample code:
<cfset names = queryNew("") />
<cfset firstNames = ["Al", "Rudi", "Summer", "Dirk", "James", "Loren", "Hiram"] />
<cfset lastNames = ["Giordino", "Gunn", "Moran", "Pitt", "Sandecker", "Smith", "Yeager"] />
<cfset queryAddColumn(names,"firstName","varchar",firstNames) />
<cfset queryAddColumn(names,"lastName","varchar",lastNames) />
<cfset pagedQuery = queryConvertForGrid(names,1,5) />
<cfdump var="#pagedQuery#">
In this example, we are asking queryConvertForGrid() for page 1 with 5 items per page.
Running this code will give you the following output.
You can now use this query to display ‘paged’ items on your page.
I noticed a rather odd issue when viewing the last page set and there were less than a full page of data. I had expected that the items under the list would appear closer to he bottom of the list than they actually did. The cause of this issue is that queryConvertForGrid()will always return a query with the same number of rows as specified in the pageSize argument. This means that if your page only has 2 items, but the page size is set at 5, then you will still get a query with 5 rows, only 3 of them will have empty strings for every column.
This code, for example, asks for page 2 with 5 items per page from a recordset that has only 7 items,
<cfset names = queryNew("") />
<cfset firstNames = ["Al", "Rudi", "Summer", "Dirk", "James", "Loren", "Hiram"] />
<cfset lastNames = ["Giordino", "Gunn", "Moran", "Pitt", "Sandecker", "Smith", "Yeager"] />
<cfset queryAddColumn(names,"firstName","varchar",firstNames) />
<cfset queryAddColumn(names,"lastName","varchar",lastNames) />
<cfset pagedQuery = queryConvertForGrid(names,1,5) />
<cfdump var="#pagedQuery#">
Runnign this code will give you the following result,
Not really what I expected, but I guess I can live with that. I added a quick cfif to my output to check for an empty string and my display was exactly how I wanted it.
Then I started wondering, what would happen if there was only enough data for 2 pages but I asked for page 4? Here is code that does just that:
<cfset names = queryNew("") />
<cfset firstNames = ["Al", "Rudi", "Summer", "Dirk", "James", "Loren", "Hiram"] />
<cfset lastNames = ["Giordino", "Gunn", "Moran", "Pitt", "Sandecker", "Smith", "Yeager"] />
<cfset queryAddColumn(names,"firstName","varchar",firstNames) />
<cfset queryAddColumn(names,"lastName","varchar",lastNames) />
<cfset pagedQuery = queryConvertForGrid(names,1,5) />
<cfdump var="#pagedQuery#">
And here is the result of runnign this code:
You will see that a query with 5 rows is still returned, but every column in every row is an empty string.
This is nothing that cannot be worked around, but if you want to use queryConvertForGrid() without using cfgrid its something you need to keep in mind.
Also keep in mind that some databases, like MySQL, make it easy to paginate data right inside your query by using the LIMIT clause. However, it is a bit more difficult in SQL Server, which is where I was storing the data for my book application.
DougHughes.net 