My First ExtJS DataGrid Pt 3: A Paging Query
OK, we're cooking with crisco now. You've probably taken a little time to look through the examples a little bit by now, and you've seen a little of what the paging grid looks like and can do, along with the many other examples. Our last tutorial covered setting things up, but before we dive into the JavaScript we'll need some data.
Now, the paging example that's included with the Ext download calls an external PHP page to retrieve the necessary JSON dataset. JSON is great, being small and lightweight, but I'm working with MS SQL at work, which can return XML data. Since the library has built in proxies for dealing with either, I change it up to take in the XML.
Each database has different ways of writing a 'paging' query. MySQL makes it really easy by providing multiple arguments for the LIMIT statement. MS SQL makes it a little harder. See, the trick is to only pull in the records, on each db call, that you actually need. Some people pull the entire recordset and then use a query-of-query to poll their required data, but if you're dealing with very large datasets then it makes more sense to only pull what your need when the time comes. I found a great article on MSDN (which I can no longer find) that gives a good suggestion on how to approach this, by using multiple sub select statements. But, the first thing we'll do is define some default parameters for those that will eventually be passed in on the AJAX calls.
<cfparam name="FORM.start" default="#URL.start#" />
<cfparam name="URL.limit" default="25" />
<cfparam name="FORM.limit" default="#URL.limit#" />
<cfparam name="URL.dir" default="DESC" />
<cfparam name="FORM.dir" default="#URL.dir#">
<cfparam name="URL.sort" default="vcLastName" />
<cfparam name="FORM.sort" default="#URL.sort#" />
First thing you probably noticed is that I have a FORM scoped variable that matches every URL scoped variable, defaulting the URL var first then defaulting the FORM var to the URL var's value. What this allows me to do is testing. I can call the page without any additional info and it will properly run, since I have defaulted all values, and I can tag on query string variables for initial output testing, or tap it directly from a form post. These variables are pretty basic: 'start' is the starting record row, 'limit' is the number of records to be returned, 'dir' is the sort order, and 'sort' is the column to sort on. After this we move to the query itself.
ID,
vcFirstName,
vcLastName,
bIsAdministrator,
bIsActive,
tsDateLastLogin
FROM ( SELECT TOP #FORM.limit# ID,
vcFirstName,
vcLastName,
bIsAdministrator,
bIsActive,
tsDateLastLogin
FROM (SELECT TOP #FORM.start + FORM.limit# ID,
vcFirstName,
vcLastName,
bIsAdministrator,
bIsActive,
tsDateLastLogin
FROM (SELECT TOP #FORM.start + FORM.limit# ID,
vcFirstName,
vcLastName,
bIsAdministrator,
bIsActive,
tsDateLastLogin
FROM tblUsers AS T1
WHERE tsDateLastLogin IS NOT NULL
ORDER BY #FORM.sort# ) AS T2
WHERE tsDateLastLogin IS NOT NULL
ORDER BY #FORM.sort# DESC ) AS T3
WHERE tsDateLastLogin IS NOT NULL) AS T4
WHERE tsDateLastLogin IS NOT NULL
ORDER BY #FORM.sort# #FORM.dir#
FOR XML AUTO, ELEMENTS
Notice a few things here. I only call the columns that I need. The two inner most sub selects use the TOP functionality to retrieve the 'start' row number plus the 'limit', so if you 'limit' yourself to 25 records and you are now calling page 3 (which would start with row 50) then you would say in these statements 'retrieve the TOP 50+25 rows', with the first sub-select then only asking for the 'limit' of the TOP 25. This gives you the TOP 25 rows of 50+25. You also see that a COUNT was added to the first select. Although this number appears in each record as 'recCount', it also gives you the total number of records that could be returned, thereby giving us the ability to say 'these are rows 50 thru 75 out of 38,543 records.'
If you cfdump the query return you will see multiple query rows returned, but nothing like you might expect. We now have to convert the returned query into a properly formated XML string. For this I use a function that Andrew Powell showed us in a Spry presentation that he did for the Nashville ColdFusion User Group. This was something that one of his compadres at Universal Mind wrote, and that I've adjusted slightly here.
<cfargument name="doc" type="string" required="false" default="xml" />
<cfargument name="qry" type="query" required="true" />
<cfscript>
var x = "";
var y = "";
var retXML = "";
x = listFirst(ARGUMENTS.qry.columnList);
for (y=1;y lte ARGUMENTS.qry.recordCount;y=y+1){
retXML = retXML & ARGUMENTS.qry[x][y];
}
retXML = "<" & ARGUMENTS.doc & ">" & retXML & "</" & ARGUMENTS.doc & ">";
</cfscript>
<cfreturn retXML />
</cffunction>
Basically this will take your MS SQL query output and format it into a proper XML document, with the ability for you to also define the 'root' element (doc). I keep this function in a utility library so that I can call it at anytime. I then take the return of this and output it between some cfcontent tags with a type of 'text/xml' to get a dynamic xml doc to be consumed by these AJAX calls.
The Ext library makes the call to the pages via a form post, then inspects the XML return to map fields to their assigned grid columns.
But, that's another lesson. This wraps it up for today. Tune in next time (same Bat-time, same Bat-channel) for our next installment: Defining the DataStore.
P.S. Sample files will be added to this post sometime tomorrow.
The sample files are now included in the download area below. Let me know if you have any questions, comments, or war stories.
Someone posted another paging method to one of the other tutorials. I just haven't had an opportunity to check it out yet. Personally, I'd rather us MySQL. Paging is far easier there, though it's more overhead to get a row count.
No, never had a problem. The Join type you use is important, dependent upon connected data on both sides. This was also written on SQL 2000, so it may be different on 2005 (and drastically easier on MySQL). At some point I hope to post on an updated paging query, that is much more simplified.