PDA

View Full Version : CFQueryReader + QueryConvertForGrid + Ext JS Grid Example



davidsanderson
31 Dec 2009, 6:21 AM
I'm trying to use the CFQueryReader in a store to populate a Grid with paging. I can't get it to work. I would like to do optimal server paging but since I'm using SQL Server 2005 there's not an "easy" way of writing the SQL so I instead opted to use QueryConvertForGrid in my cfquery.

So the query is simple, the getTerms method is returning
<cfreturn QueryConvertForGrid(locVars.retVal,1,20) />

I dumped on this and it's correct, providing the correct TOTALROWCOUNT of 320 with only the first 20 records returned.

This is my code for the store:

tlc.storeGetTerms = new Ext.data.Store({
url: "lib/extdirect/termLengthConfigurator.cfc",
id: 'storeGetTerms',
baseParams:{
method: 'getTerms',
returnFormat: 'JSON'
},
reader: new Ext.data.CFQueryReader({
id:'TermID'
},tlc.storeGetTermsFieldDef),
listeners: {
exception: {
fn: function() {
console.log(arguments);
console.log("Response Text?"+response.responseText);
}
}
}
});Then in a button handler I'm doing this:
tlc.storeGetTerms.load({params:{start:0, limit:20}});

The Grid has a paging toolbar coded like this:

// paging bar on the bottom
bbar: new Ext.PagingToolbar({
pageSize: 20,
store: tlc.storeGetTerms,
displayInfo: true,
displayMsg: 'Displaying records {0} - {1} of {2}',
emptyMsg: "No records to display"
})The problem is that the Grid only shows the 20 rows and it doesn't even recognize that there are a total of 320 records so the next page arrow is disabled and it just says "Displaying records 1 - 20 of 20".

Does anyone know what I'm doing wrong or that can provide a working example?

js_coder
31 Dec 2009, 8:04 AM
I'm not sure what Ext.data.CFQueryReader is, but JsonReader accepts a config option called 'totalProperty'. Setting this to the matching property name on the returned JSON will allow the store's getTotalCount method to return an accurate value.

davidsanderson
5 Jan 2010, 6:49 AM
The key was to realize that the ColdFusion function, "QueryConvertForGrid", wants the query, the *page number* and the limit. I didn't know it wanted a page number, I thought it would want the start row. :)

So my code is as follows.

<cffunction name="getTerms" output="false" access="remote" returntype="any" ExtDirect="true">
<!--- don't change the name of these parameters. Ext JS by default passes these arg names in. --->
<cfargument name="start" default="1" />
<cfargument name="limit" default="20" />
<cfargument name="sort" default="TermCode" />
<cfargument name="dir" default="ASC" />
<cfargument name="DateInTerm" />

<cfset var locVars = {} />

<!--- strip off the timesamp at the end of the date --->
<cfif arguments.DateInTerm neq "">
<cfset arguments.DateInTerm = Left(arguments.DateInTerm,10) />
</cfif>

<cfquery name="locVars.query" datasource="#VARIABLES.dsn#">
SELECT TermID,
TermCode,
OriginalStartDate,
OriginalEndDate,
OverrideStartDate,
OverrideEndDate,
LastModifiedBy,
LastModifiedDate,
'Last Modified: ' + convert(varchar,LastModifiedDate,107) + ' by ' + LastModifiedBy as LastModified
FROM tblTerm
<cfif arguments.DateInTerm neq "">
WHERE '#arguments.DateInTerm#' Between OriginalStartDate and OriginalEndDate
Or '#arguments.DateInTerm#' Between OverrideStartDate and OverrideEndDate
</cfif>
ORDER BY #Arguments.sort# #Arguments.dir#

</cfquery>

<!--- set a default for pageNum in case there's no records returned --->
<cfset localVars.pageNum = 1 />

<cfif locVars.query.RecordCount neq 0>

<!--- if the last page contains less records than the limit, then change limit size to number remaining records --->
<cfif locVars.query.RecordCount - arguments.start lt arguments.limit>
<cfset arguments.limit = locVars.query.RecordCount - arguments.start />
</cfif>

<!--- Calculate the page number since QueryConvertForGrid needs a *page number* instead of the row number --->
<cfset localVars.pageNum = arguments.start / arguments.limit + 1 />

</cfif>

<!--- pass in the query, page number, and the limit (page size) to the QueryConvertToGrid function --->
<cfreturn QueryConvertForGrid(locVars.query, #localVars.pageNum#, #arguments.limit#) />

</cffunction>My JS code has this:

tlc.storeGetTerms = new Ext.data.Store({
url : "lib/extdirect/termLengthConfigurator.cfc",
remoteSort : true,
id : 'storeGetTerms',
// this gets passed on every http request
baseParams:{
method : 'getTerms',
returnFormat: 'JSON',
DateInTerm : tlc.formFindTerm.form.findField('dateInTerm').getValue()
},
reader: new Ext.data.CFQueryReader({
totalProperty :'TOTALROWCOUNT',
id :'TermID'
},tlc.storeGetTermsFieldDef),
listeners: {
exception: {
fn: function() {
console.log(arguments);
}
}
}
});
I don't need to show all of my grid code layout, but here is the paging bar in the GridPanel.

// paging bar on the bottom
bbar: new Ext.PagingToolbar({
pageSize : 50,
store : tlc.storeGetTerms,
displayInfo : true,
displayMsg : 'Displaying records {0} - {1} of {2}',
emptyMsg : 'No records to display'
})

Mike Robinson
5 Jan 2010, 7:43 AM
Maybe CFQueryReader works for you, but I wound up abandoning that approach altogether.

Have a munch of the attached zip:

(1) "InstantCrud.cfm" implements the Create Read Update Destroy functionality needed on the host side, so that a sample data-source looks like this:

<cffunction name="GridSource_ActiveRegions"
access="remote"
returnFormat="JSON"
returntype="struct">

<!--- Once again, the "dict" for a simple read-only query requires no field definitions. --->
<cfset dict = InitDataDict('Regions', 'RegionId', ds_TLCReporting) >

<cfset options = StructNew() >
<cfset options['select_query'] = "
SELECT
RegionId,
CASE
WHEN State IS NULL THEN ISNULL(Region, '(None)')
ELSE ISNULL(Region, '(None)') + ', ' + State
END AS Region_Name,
LTRIM(RTRIM( ISNULL(RMOFname, '') + ' ' + ISNULL(RMOLname, '') ))
AS RMO_Name
FROM
Regions
WHERE Active = 1
">
<cfset options['order_by'] = "Region_Name" >

<cfreturn InstantReadOnlyCRUD(dict, options) >
</cffunction>or:
<cffunction name="GridSource_ActiveRequests"
access="remote"
returnFormat="JSON"
returntype="struct">

<cfargument name="region_id" type="string" required="false" default="all">
<cfargument name="status" type="string" required="false" default="all">

<!--- Once again, the "dict" for a simple read-only query requires no field definitions. --->
<cfscript>
dict = InitDataDict('ClinicDev_MaintenanceRequests', 'request_id', ds_TLCApps);

// AJAX NAME DATABASE NAME
// ALL-LOWER-CASE FIELD NAMES ARE USED FOR THIS UPDATABLE RECORD-SOURCE UNTIL WE FIND THE BUG ASSOCIATED WITH MIXED-CASE.

addROnly(dict, 'request_id', 'request_id' );
addField(dict, 'status', 'status', dtype.String, '' );
[...]
// Note that some fields described in the query are not mentioned in the dictionary. These fields don't actually get updated
// even though the client attempts to do so. What he does not know will not hurt him.
</cfscript>

<cfset options = StructNew() >

<cfset options['softdelete_field'] = 'delete_flag'>
<cfset options['softdelete_value'] = 1 >

<cfscript>
options['select_query'] = "
SELECT
RQ.request_id,
RQ.status,
RQ.report_date,
[...]
FROM
ClinicDev_MaintenanceRequests RQ
[...]
WHERE
RQ.delete_flag = 0
";

if ((ARGUMENTS.region_id NEQ 'all') AND (ARGUMENTS.region_id NEQ '')){
options['select_query'] &= " AND (REG.RegionID = " & ARGUMENTS.region_id & ")";
};
if (ARGUMENTS.status NEQ 'all' AND (ARGUMENTS.status NEQ '')) {
options['select_query'] &= " AND (RQ.Status = '" & ARGUMENTS.status & "')";
};
</cfscript>
<cfset options['order_by'] = "report_date" >

<cfreturn InstantCRUD(dict, options) >
</cffunction>(2) The other file is a descendent of "JsonStore" which glosses-over many of the issues of getting ColdFusion-8/9 to work correctly with ExtJS. Simply descend from this class.

My decision was not an easy one, and the amount of effort in the attached code is considerable, but it definitely worked better for me to let CF8's built-in implementation be "for Adobe's corresponding JavaScript code" and to implement a parallel system that is "pure ExtJS 3."

davidsanderson
5 Jan 2010, 8:12 AM
Mike, thanks for the reply. I might have to abandon CFQueryReader myself. Have you tried grid paging while keeping parameters? I can't seem to get that.

For example, I have a simple "search" form that loads the store and passes in the values just fine but when you go to the next page, it loses the search parameter. It keeps the start, limit, sort and dir just fine.