One of the cool new UI elements we have in ColdFusion 8 is the AJAX based grid. It can be bound to external data and allows for sorting and paging. This is pretty cool and all - and I'll blog more about it later, but I wanted to talk about a small little function that is used to help drive the grid - queryConvertForGrid.
Before you can hook up a cfgrid to the result of a CFC, you have to format the data in a special way. Instead of just returning a query, you must return a structure with two keys: query and totalrowcount.
The query is not your complete recordset. Instead, it is the current "page" of data. All cfgrid AJAX requests will pass to your CFC the current page. So you might think that you need to do a bit of math to figure out the current "slice" of data to return. You certainly could too - but this is where queryConvertForGrid comes into play.
So lets take a concrete example. You may remember my Traffic demo from yesterday. This demo used a grid that spoke to a CFC that in turn spoke to my Yahoo engine (which in turn spoke to Yahoo's services). The grid "spoke" to my CFC and passed along:
- The page to fetch
- The size of my pages
- SortCol and SortDir (not discussed today)
- And the Zip Code
My CFC then handled getting the data (and potentially sorting). But to return the proper data, I simply used queryConvertForGrid. This function takes three arguments: A query, a page number, and the page size. Here is a simple example using a pre-built query:
<cfset foo = queryNew("id,name,age,gender")>
<cfscript>
for(i=1; i <= 200; i++) {
queryAddRow(foo);
querySetCell(foo, "id", i, i);
querySetCell(foo, "name", "Name #i#", i);
querySetCell(foo, "age", randRange(18,65), i);
querySetCell(foo, "gender", listGetAt("male,female", randRange(1,2)), i);
}
</cfscript>
<cfset slice1 = queryConvertForGrid(foo, 1, 20)>
<cfset slice2 = queryConvertForGrid(foo, 2, 20)>
<cfset slicex = queryConvertForGrid(foo, 99, 20)>
<cfdump var="#slice1#" label="First Page">
<cfdump var="#slice2#" label="Second Page">
<cfdump var="#slicex#" label="Page 99">
I have three calls to the queryConvertForGrid function. Each one passes in my query. The first call gets page 1. The second gets page 2. The last gets page 99. If you run this, you will see that each dump returns a slice of the initial 200 row query. You will also notice that it fails silently on page 99. (Is that good? Bad? I'm not sure.)
So as you can see, this function takes out some of the grunt work we have to do in order to hook up our CFCs to the new AJAX goodness on the front end.
Now I do have one point I'd like folks to consider. This function makes it super easy to get the page. But I'm still getting all 200 rows up front. You may want to consider doing your paging in SQL, especially MySQL since it makes it so easy. In fact, you can even write a query in MySQL that will get both a "page" and the total row count. (I discussed this here.) So if you can easily do the paging yourself, it may make sense to do so and improve the performance of your application.
In my next entry I'll give a nice complete demo of cfgrid and a bound CFC. And lastly - I have to admit that the cfscript block above just makes me plain giddy. (Yes, I'm a Scorpio Geek.)
Archived Comments
I haven't played with this yet, so I am probably missing something obvious.
Why can't you just pass in a query to cfgrid (seems like that would be easier and more consistent with the rest of the language)?
Steve: You can. This is just an alternative for built in paging. When you use the Ajax grid with a bind statement and return paged results from a CFC the grid will automatically build in controls for next/previous, etc for you - taking some of the grunt work out of it.
And Ray - I'm sure you'll touch on this in your next entry on the topic - but those of us who don't have the luxury of easy DB paging could also use a cached query to speed things up a bit. And since cfquery now supports caching with cfqueryparam we now have no excuse not to cache the heck out of queries wherever possible. Hey that sounds like another blog post (or has that been done already)...
Todd,
Glad to hear it! So why does paging require the extra structure?
Is it possible to use a variable for the bind cfc? I'm trying to do something like this in a Model-Glue app. I can access the cfc thru ColdSpring via the application scope, but if I try something like this:
<cfset myCFC = application.serviceFactory.getBean("ArticleGateway")>
I can do this: <cfset newsList = myCFC.getNewsForGrid(1,20)>
But I can't do this:
<cfgrid name="news"
format="html"
pagesize="10"
striperows="yes" bind="cfc:myCFC.getNewsForGrid({cfgridpage},
{cfgridpagesize},
{cfgridsortcolumn},
{cfgridsortdirection})"
selectmode="column"
appendKey="true">
Is it just not possible to get the CFC thru ColdSpring/Model-Glue and use the grid in this way?
No, you can't use a CF variable like that - remember - this all ends up as client side code. Just point to a CFM:
index.cfm?event=getcrapforgrid&etcetc
I have a weird problem in using QueryConvertForGrid in a new CF 8 server (the same code works in a different CF 8 server). The Problem is that the JSON returned by this function comes back with some leading white space and the following lines before the JSON:
<!--
1: HIDE ERROR CODE FROM USER
0: SHOW ERROR CODE FROM USER
1 = ON ; 0=OFF
-->
I have used <cfsetting showdebugoutput="no"> also. In comparing the response of both the servers (using Firebug), I suspect the problem lies with the _cf_rc value which is 0, while in the working version the value is 1. Is this the problem, If so, what is this variable and how do I control it? Any help will be greatly appreaciated.
Thanks!
It could be a few things. First off, the white space can come from a number of places, but if you add a <cfcontent reset="true"> before you output the json, the whitespace will be removed, just be sure output the json right after the cfcontent.
As for the text you shared, that is not from CF, it is from something else in your code. I'd do a multi-file search for it.
You are right. I did a global search and found the comments in the application.cfm! Changing the comments to CF comments fixed the issue - the white space was part of the comments, so it fixed itself.
I tested by inserting some HTML comments in the CFC just before the json output and the comments did not cause a problem. It is a problem only if it is in the appplication.cfm. I wonder why.
Thanks,
-Xavier
Big puzzler for me... How can I use this in Model Glue? I haven't had any success finding examples of data binding within Model Glue (Unity, v2), and the event handlers circumvent direct references to CFCs. If anyone knows how, it would have to be you.
@JF: Well, this is kind of a big subject and a bit hard to 'fit' into a comment here, but this is one way I've done in the past.
I simply point my Ajax to an event, like json.getFoo. The event does what it needs to do to get data, and then the view outputs the JSON.
I've even taken this a step further where I can broadcast "toJSON" in an event. I pass the name of the view State key to use, and then the generic toJSON event will get the value, convert to JSON, and use one generic view to output it.