using cfquery, maxrows, and pagination

[Wickersty]

Hi. Let's say I have a CFQUERY:

<CFQUERY NAME="qMyQuery">
SELECT * FROM myTable
</CFQUERY>

and the results of that query are in the thousands and I don't want to output them all at the same time for performance issues (page load, etc).

My thought was to do:

<CFQUERY NAME="qMyQuery" MAXROWS="100">
SELECT * FROM myTable
</CFQUERY>

And then at the bottom of the output, use pagination:

<a href="thispage.cfm?startAt=101">Next 100</a>

Which would load the same page, running the same query, but have the query this time query maxrows of 100 but starting at the 101st row. Problem is my unique ID column in the table is not necessarily incremental (ie I might have 1... 2...4...6...7...8) so row 100 won't be ID 100.

Does anyone know how to do this? How to make a query like this one:

<CFQUERY NAME="qMyQuery" MAXROWS="100">
SELECT * FROM myTable
</CFQUERY>

start at the 101st row of the table?

Many thanks folks!

Jeff

 

[MsChelle]

Instead of using maxrows in your query, you could use it in your cfoutput or cfloop (how are you outputting the data?)

 

[Wickersty]

Well, was going to have the output as the standard:

<CFOUTPUT QUERY="qMyQuery">
<p>#someField#</p>
</CFOUTPUT>

What do you mean --- using cfloop?

<CFLOOP QUERY="qMyQuery">
<p>#someField#</p>
</CFLOOP>

I suppose i could cfbreak the loop at 100, but how would I 'start' the loop 101 rows into the query results?

 

[MsChelle]

<cfloop query="qMyQuery" startrow=#start# endrow=#end#>
#column#<br> (whatever)
</cfloop>

or

<cfoutput query="qMyQuery" startrow=#start# maxrows="100">
#column#<br> (whatever)
</cfoutput>

No need to break.

 

[r937]

using STARTROW and MAXROWS in CFOUTPUT means that the entire query has been retrieved, and CF is showing only parts of it

this works splendidly, provided that the query has been cached

if the query hasn't been cached, then you are basically retrieving the entire query over and over, as many times as you hit the "next" button

make sense?

r937.com | rudy.ca

 

[Wickersty]

Yea, but that's not necessarily a good thing - it's wasted processing to query 5,000 results and then only loop through and output 100 of them, no? There's no better way to do this at the query level?

 

[r937]

of course you can do this at the query level

but then you won't be using CF's STARTROW and MAXROWS

you are off on the right foot by using the concept of "start at" with a value of your column --

<a href="thispage.cfm?startAt=101">Next 100</a>

all you have to do is write your query accordingly

select columns
from yourtable
where foo >= #url.startat#
order
by foo limit 100

note that you must use ORDER BY and whatever feature your dbms provides to do LIMIT (which works only in mysql and postgresql -- you did not mention which dbms you're using)

r937.com | rudy.ca

 

[MsChelle]

I think part of the problem is the IDs aren't consecutive, so adding it into the where clause like that might not work.

If you really want to do this in the query, you could do something like this

select top 100 fields
from table
where columns not in (select top #startat# SomeUniqueField from table order by SomeUniqueField)
order by SomeUniqueField