Query Paging in Flex
I'm working on a top secret Flex project that will have a grid with the potential to display a large amount of records from a remoting call. Rather than risk the potential of poor performance down the road I decided that it would be best to cache the query in my component and then return incremental results via paging. I did some Googling for a pre-built solution but didn't find anything so I created my own function to handle the paging of the cached query. The code is below.
It's pretty straightforward, just pass in the query object, a start row and an end row. The function returns a struct containing a query starting at the start row argument and ending at the end row argument, a hasPreviousRows value (boolean) and hasMoreRows value (also boolean). Now I can easily page through the cached query in Flex by calling this function and dynamically enabling the 'next/previous' buttons.
<cfargument name="theQuery" type="query" required="true">
<cfargument name="sRow" type="numeric" required="true" hint="the start row of the query to return">
<cfargument name="eRow" type="numeric" required="true" hint="the end row of the query to return">
<cfset var returnStruct = structNew()>
<cfset var increment = arguments.eRow - arguments.sRow +1>
<cfset var returnQ = QueryNew(theQuery.columnList)>
<cfset var row = 0>
<cfset var col = "">
<cfset returnStruct.hasPreviousRows = false>
<cfset returnStruct.hasMoreRows = false>
<cfif arguments.sRow gt arguments.eRow>
<cfthrow type="queryPage Error" message="Start row must be less than or equal to end row.">
</cfif>
<cfif arguments.eRow gt theQuery.recordCount>
<cfset arguments.eRow = theQuery.recordCount>
<cfset increment = arguments.eRow - arguments.sRow +1>
</cfif>
<cfif sRow gt 1>
<cfset returnStruct.hasPreviousRows = true>
</cfif>
<cfif eRow lt theQuery.recordCount>
<cfset returnStruct.hasMoreRows = true>
</cfif>
<cfset queryAddRow(returnQ, increment)>
<cfloop query="theQuery" startrow="#arguments.sRow#" endrow="#arguments.eRow#">
<cfset row = row + 1>
<cfloop from="1" to="#listLen(theQuery.columnList)#" index="col">
<cfset querySetCell(returnQ, listGetAt(theQuery.columnList, col, ","), theQuery[listGetAt(theQuery.columnList, col, ",")][currentRow], row)>
</cfloop>
</cfloop>
<cfset returnStruct.theQuery = returnQ>
<cfreturn returnStruct />
</cffunction>
RSS
I'm sure you're busy on the "top secret" project, but it would be helpful to see a Flex demo with the next/previous buttons and how you pass the query object from flex to the CFC function.
I would think there would be some internal Flex solution to paging using a datagrid that one could develop. It seems like extra work to have to keep going back to the CFC to get just the records you want to display in the datagrid. Especially since those records are already in the ArrayCollection (query) that Flex now knows about.
Once you have the complete query in flex, there should be some way using ActionScript to tell the datagrid what records (objects) out of the query (ArrayCollection) you want to display in the datagrid.
Perhaps you could design an ActionScript function that creates an ArrayCollection that only has a subset of the Objects (records) that you want to display in the datagrid and then make the dataGrid's dataprovider equal that ArrayCollection. When the user click on the Next button in Flex, you call a function that creates an ArrayCollection with the next 20 records and make that ArrayCollection the DataProvider for the datagrid.
Hmmm... this could be my next Flex Demo Application - Paging and the DataGrid Component.
I'm going to play with this further and see if I can figure out a Flex/ActionScript solution.
You know that Flex Data Services can work with the Flex datagrid to do this automatically for recordsets of any size? The performance is just awsome, I saw it demoed at MAX where you could scroll to any place in a 20.000 recordset with instant results. Hardly any lag at all.
J
Jensa - if only I had $20k to drop on FDS!
See: http://www.flashmagazine.com/1335.htm, where the author mentions using FLex Data Services to page data in a datagrid and also has some benchmark times for getting large amounts of data into a Flex app.
If you are building a large-scale app, then I think you should investigage further FDS. Does it really cost $20,000? It is possible that FDS will give you the speed and relability you need wihtout a bunch of custom-designed CF/ActionScript code.
http://livedocs.macromedia.com/coldfusion/6.1/html...
Pim
http://www.brucephillips.name/blog/index.cfm/2006/...
It will not solve the challenge Todd is faced with, but may help others who need a simpler solution.
Bruce