Count group increments when using <cfoutput> with group specification

[stilllwaiting]

Hello everyone.

I have a query which joins several tables.

Orders & OrderItems

I use <Cfoutput Group="OrderID"> which is the primary key for the Orders Table. Then I use the nested Cfoutput Tag to display the Items for each order in a neat list.

ORDER 1
Product 1
Product 2
ORDER 2
Product 3
Product 4
Product 5

and so on. This part is working fine. However, we have a lot of orders, so I want to use page separators where the user can skip through pages of orders. Usually I'd just use STARTROW and MAXROWS in the <CFOUTPUT> tag and that'd be that. However if want to see 50 orders and I say MAXROW="50" if there are multiple items per order, I won't see my entire list. Is there a way to get the row numer in increments of the group field? Meaning if I'm at order 57 and I want to start my next page at order 107 can I get the row number of the first row with order 107 WITHOUT RUNNING ANOTHER QUERY.

I can think of several ways of doing this by running more queries, but that is counterproductive to my cause.

Thanks in advance.

 

[r937]

use query of query

query your result set like this --

<cfquery name="getorderdetailcounts" dbtype="query">
select OrderID
, count(*) as orderdetails
from getorderdetails
group
by OrderID
</cfquery>

this gives you an array of orders with the number of detail rows they have, which you can then use to generate your page numbers -- you would calculate X orderdetails per page, then sum up the orderdetails counts in the array to find out where the page breaks go



r937.com | rudy.ca