CF: breaking queries into pages of data

Another day another problem to solve. With many more Ajax type screens, managing the data flow to the front-end needs to be baked into the core system. Otherwise, it seems, the users really do not have a care in the world about how much data they consume....
In my specific case, I am receiving a query (structured data in records), any type of query from any type of DB. I do not know the number of columns, indexes, pks or anything. The job is to be able to return a page of records from this query without going back to the database (would be hard since we don't know the database either). This could even be a QofQ, i.e. code generated, set of records.
I scratched my head several times over this, but still do not think I have an elegant solution. Nonetheless, I have a working one.
I wanted to avoid looping over the query itself and creating a new one record by record. I decided to use a Query of Query approach though only when needed. To do that I dynamically attach another column to the original query that I am using as primary key. Then, I am calculating the start and end records for the page and using a QoQ to just return the ones requested for the Page.

<cffunction name="getQueryPage" access="public" returntype="query" hint="return a query with subset of records based on page argument.">

 <cfargument name="selRecords" required="Yes" type="query" hint="the base query with records">

 <cfargument name="Page" required="no" type="numeric" default="1" hint="which page (chunk) to return">

 <cfargument name="PageSize" type="numeric" default="50" hint="size of record chunk. this is also the max number of records to be returned.">



 <cfscript>

  //init stuff

  var dataQuery = arguments.selRecords; //realias

  var selReturn = dataQuery;

  var iPageCount = ceiling(dataQuery.recordCount/arguments.PageSize);

  var lstCols = dataQuery.ColumnList; // capture col list before change 

  var blnDoSubQuery = false;

  var intStartRec = 0;

  var intEndRec = arguments.PageSize;

  var zzArray = ArrayNew(1); 

   

  

  arguments.Page = int(Abs(arguments.Page)); //ensure that we have no negatives

  //if we request a page that is too big return last page

  if( arguments.Page GT iPageCount) arguments.Page = iPageCount;

  

  //attach control column to base data if calculations require it and or it is not present. 

  if (ListFindNoCase(lstCols,"zzzPageControlCol") IS 0 AND arguments.Page GT 0) {

   intStartRec = 1 + ((arguments.Page -1) * arguments.PageSize);

   intEndRec = intStartRec + (arguments.PageSize -1);   

   //only if the overall query has more records than requested do we need to do anything.

   if ( NOT (intStartRec IS 1 AND intEndRec GTE dataQuery.recordCount)) {

    blnDoSubQuery = true;

    //attach control col

    zzArray = ArrayNew(1);   

    //if we do not think that the passed in query is cached, we can probably use a scheme

    //were we only mark the records that we need to return.

    //however, marking all records one time is faster, if the base query is cached and repeatedly pages

    //need to be returned

    for (i=1; i LTE dataQuery.Recordcount; i++) {

     zzArray[i] = i;

    } 

    QueryAddColumn(dataQuery,"zzzPageControlCol","Integer",zzArray);      

   }; 

  }

 </cfscript>

 <!--- only do subquery if needed, ideally the base query is cached somehow but that is beyond this scope (bsoylu) --->

 <cfif blnDoSubQuery>

  <cfquery name="selReturn" dbtype="query" >

   SELECT #lstCols#

   FROM dataQuery

   WHERE zzzPageControlCol >= #intStartRec# AND zzzPageControlCol <= #intEndRec#

  </cfquery>

 </cfif>

 

 <cfreturn selReturn>

</cffunction>

Cheers,
B.