Scott Stroz specializes in developing
and deploying enterprise-level web applications, as well as integrating
new programming features into older applications to help organizations
achieve growth by migrating data management to advanced web technology.
His experience with web application development technology spans nearly
a decade, during which he was named an Adobe Community Expert. Scott
excels at completing unusual, complex and challenging tasks, and he
embraces new technology and its possible inclusion in current and future
projects, earning him the title “Chief Problem Solver.” Scott is
recognized as an industry expert through his speaking engagements at
conferences and user groups and contributions to a number of technical
publications.
- 85 entries Alagad
- 12 entries Ant
- 167 entries ColdFusion
- 11 entries ColdSpring
- 20 entries Conferences and Events
- 1 entries Development Tools
- 5 entries Dynamic Programming
- 1 entries Flexmaniacs
- 52 entries General
- 4 entries Groovy
- 5 entries JRun
- 17 entries Jobs
- 17 entries Model-Glue
- 27 entries OO Development
- 53 entries Programming
- 31 entries Reactor For ColdFusion
- 13 entries SQL
- 14 entries The Server Side
- 7 entries Validat
- 8 entries Farcry CMS
- 32 entries Flex
- 10 entries Flex
- 3 entries Hibernate
- 3 entries jQuery
- 1 entries Presentations
- 1 entries Press
Subscribe to our The Alagad Technical Team Blog
QueryConvertForGrid()...Its Not Just For <cfgrid> Anymore
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 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 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 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.
9 responses to “QueryConvertForGrid()...Its Not Just For <cfgrid> Anymore”
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
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.
@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.
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).
@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 <cfgrid>.
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?
How is this different from using
<cfset startrow = (page - 1) * rowPerPage + 1>
<CFOUTPUT query="names" startrow="#startrow#" maxRows="#rowPerPage#">
??
http://concealer.mybrute.com
Check out this cool mini fighting game
Leave a Reply
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.
Posted By: Dan G. Switzer, II on Aug 14, 2008 at 12:00 AM