The amazing adventures of Doug Hughes

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.

queryCOnvert Image 1

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,

queryConvert Image 2

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:

queryConvert Image 3

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.

Comments on: "QueryConvertForGrid()…Its Not Just For cfgrid Anymore" (9)

  1. Dan G. Switzer, II said:

    Scott,

    This is pretty easy to use in SQL Server if you’re using 2005. (You do this with CTEs.) There are also several stored procedures for SQL 2000 (and below) which will do the pagination for you in SQL server.

    Like

  2. Aaron Longnion said:

    To follow on Dan’s comment, here’s some examples of how to paginate in SQL Server 2005 with CTEs in CF – http://cfzen.instantspot.com/blog/2008/6/9/Paginating-Records-in-CF-with-One-SQL-Server-2005-call-update-1

    Like

  3. Dmitriy Goltseker said:

    Can you explain the purpose of this function. If I have thousands of rows that come back from this query, that means for every request you will get all of them and then you just filter for 1 to 5. Is this the idea? if Yes, I don’t see a benefit for using this function; and I’m talking about it in general, not just about cfgrid.

    Like

  4. Scott Stroz said:

    @Dan nad @Aaron – I guess ‘easy’ is relative there. I love the LIMIT clause of MySql. Had I thoght about it more, I probably would have done this app in MySQL.

    @Dmitriy – The sole purpose of this function, as I understand it, is to give you a structure formatted properly for use in the grid.

    Like

  5. Dmitriy Goltseker said:

    Thanks, Scott. I just read CF documentation on this at http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=functions_m-r_18.html and I don’t agree with the approach. This approach will work for databases or resultsets that are small; however if the result set is huge, it doesn’t make sense to return the whole recordset and then just filter out the results (that’s what example in the documentation explains).

    Like

  6. Scott Stroz said:

    @Dmitriy – I agree. However, I would also say that for very large recordsets, I would implement some kind of filtering before even considering using queryConvertForGrid(). And if it can’t be avoided, the structure is simple enough to replicate and pass back to .

    Like

  7. Dmitriy Goltseker said:

    In my applications I filter to get 50 records at the time. So are you saying if I want to filter those 50 even further, then I would use aforementioned function? If this is the case, how’s that function compares with the query of a query, time-wise?

    Like

  8. How is this different from using

    ??

    Like

  9. Marcuski said:

    http://concealer.mybrute.com
    Check out this cool mini fighting game

    Like

Comments are closed.

Tag Cloud

%d bloggers like this: