Anybody know to do page a QoQ like MySQL's LIMIT?

[shaunperry]

I have a query that has rows added to it from another source. I have a QoQ which then updates the order by (date order). I then need to set a startRow and endRow limit like MySQL's LIMIT to page the recordset back to an AJAX call? Does anything know how to do this?

Cheers

Shaun

[Adam Cameron.]

You just need to use maxrows on the <cfquery> tag. There's nothing in the QoQ SQL implementation to do row-limiting.

This is not documented on the QoQ page:

CF9: http://help.adobe.com/en_US/ColdFusion/9.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.html

CF10: http://help.adobe.com/en_US/ColdFusion/10.0/Developing/WSc3ff6d0ea77859461172e0811cbec0e4fd-7ff0.htm...

--

Adam

[shaunperry]

Yeah the only issue is I need to set a startrow to page the results. Was hoping there might be an easy solution. Thanks

[Adam Cameron.]

Ah yeah sorry, missed you mentioning that. No there's no way of doing this.  Perhaps raise an enhancement request with Adobe: https://bugbase.adobe.com/

It'd be a good addition to QoQ, so I'd def vote for it.

--

Adam

[BKBK]

You could easily create your own startRow/endRow functionality. Let us call your query myQuery. You could then proceed as follows:

<cfset rowCount = arrayNew(1)>

<!--- Store query row numbers in array --->

<cfloop from="1" to="#myQuery.recordCount#" index="n">

<cfset rowCount = n>

</cfloop>

<!--- Add column containing row numbers to query --->

<cfset queryAddColumn(myQuery, "rowNumber", "integer", rowCount)>

<!--- Example --->

<cfset startRow = 5>

<cfset endRow = 11>

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

select *

from myQuery

where rowNumber between #startRow# and #endRow#

</cfquery>

<cfdump var="#getSubset#">