Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
--
Adam
Copy link to clipboard
Copied
Yeah the only issue is I need to set a startrow to page the results. Was hoping there might be an easy solution. Thanks
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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
</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#">