GROUP and MAXROWS... friendly? 1

[strantheman]

I am unable to accurately display a grouped result set on multiple pages. I have an example, and I hope someone can assist me with a solution. Basically STARTROW and MAXROWS aren't helping me get grouped rows.

There are 100 lakes.
There are 5 fishnames per lake.

My join query returns 500 rows, and CFOUTPUT will group and show the lakename column only once per each record.

The nested CFOUTPUT will display a subset of lakes, which is the fish associated to that lake.

Therefore, there should only be 100 total lake rows, and no one lake should ever be displayed twice. If I use STARTROW and MAXROWS in my outermost CFOUTPUT, and MAXROWS is 50 there should be only 2 pages of lakes, however there are 10.

My #start# is incrementing fine, and each page says its showing records 1 - 50 or 51 - 100, but there is actually a third page that says 101 - 150.

My debug output says the query returns 500 rows, and I understand that because of my join, but can't CFOUTPUT and its next / prev style functionality handle grouped output?


<!--- one to many join --->
<CFQUERY name=&quot;getMatchingRows&quot; datasource=&quot;#ds#&quot;>
select distinct
L.lakeid as lid
, L.lakename as lakename
, F.fishid as fid
, F.fishname as fishname
from t_lake L, t_fish F
where L.lakeid = F.lakeid
and UPPER(Q.questiontext) like '%#ucase(keyword)#%'
order by L.lakename, F.fishname
</CFQUERY>
<!--- --->


<CFOUTPUT query=&quot;getMatchingRows&quot; group=&quot;lid&quot; startrow=&quot;#start#&quot; maxrows=&quot;50&quot;>
#lakename#
<br>
<CFOUTPUT group=&quot;fid&quot;>
<li>#fishname#
</CFOUTPUT>
<hr>
</CFOUTPUT>

Thanks to anyone who has a suggestion. Id prefer to avoid using arrays or session/ client variables to handle this.

nic

 

[strantheman]

Oops, looks like I let some real code slip in there:

and UPPER(Q.questiontext) like '%#ucase(keyword)#%'

should not be there.

: )

 

[tleish]

First of all, you cannot put put a GROUP attribute inside of a nested <CFOUTPUT> tag, therefore ColdFusion simply ignores <CFOUTPUT group=&quot;fid&quot;>

Secondly, understand that when you combine MAXROWS and GROUP, that MAXROWS uses the GROUP for it's count, not the acual number of records inside the group.

Keeping this in mind, try this

<!--- one to many join --->
<CFQUERY name=&quot;getMatchingRows&quot; datasource=&quot;#ds#&quot;>
select distinct
L.lakeid as lid,
L.lakename as lakename,
F.fishid as fid,
F.fishname as fishname
from t_lake L, t_fish F
where L.lakeid = F.lakeid
order by L.lakename, F.fishname
</CFQUERY>

<!--- Output 10 lakes, or 50 fish if there are 5 fish in each lake --->
<CFOUTPUT query=&quot;getMatchingRows&quot; group=&quot;lid&quot; startrow=&quot;#start#&quot; maxrows=&quot;10&quot;>
#lakename#
[COLOR=000080]<br>[/color]
<CFOUTPUT>
[COLOR=000080]<li>[/color]#fishname#
</CFOUTPUT>
[COLOR=000080]<hr>[/color]
</CFOUTPUT>

Hope this helps. - tleish

 

[strantheman]

hi tleish

Thanks for the quick reply, im really under the gun.

Removing the group attribute from my nested output tag did not change the result at all. I understand now, CF doesn't need me to specify. At any rate my problem still persists.

Im not sure what you mean by:

... MAXROWS uses the GROUP for it's count, not the actual number of records inside the group.

So is it using 500 as its count? Or 100? It seems to me that its using 500. There lies the rub.

Now, when I use STARTROW=&quot;1&quot; MAXROWS=&quot;100&quot; that page looks fine... but if I use STARTROW=&quot;51&quot; MAXROWS=&quot;50&quot; (for page 2) I do not see 50 distinct rows, I see some of the same I saw on page 1.

Ill try to help explain this better if i can. Thanks again.

 

[tleish]

Here's where it gets tricky. I'm not sure why Allaire did it this way, but I'm sure they have their reasons for doing this way in the <CFOUTPUT> tag.

STARTROW
Optional. Specifies the row from which to start output. This doesn't take into consideration the GROUP attribute. So if STARTROW=&quot;51&quot;, it will start at the 51st fish, not lake.

MAXROWS
Optional. Specifies the maximum number of rows you want displayed in the output section. This does take into consideration the GROUP attribute.

Display 100 lakes: If 5 fish p/lake then this will display 500 fish (records 1 - 500)
<CFOUTPUT query=&quot;getMatchingRows&quot; group=&quot;lid&quot; startrow=&quot;1&quot; maxrows=&quot;100&quot;>

Display 50 lakes starting with fish 51: If 5 fish p/lake then this will display 250 fish (records 51 - 301)
<CFOUTPUT query=&quot;getMatchingRows&quot; group=&quot;lid&quot; startrow=&quot;51&quot; maxrows=&quot;50&quot;>

Before I go on and give you some code that should work, I must know are there always 5 fish p/lake guaranteed in the query, or could the number vary? - tleish

 

[strantheman]

The number could absolutely vary. Some lakes have no fish. Some lakes have up to 24. 24 is the max.

thanks again.

 

[tleish]

Do you still want to display the lake if there are no fish for that query? - tleish

 

[strantheman]

Yes. Some lakes show no fish.

Now, when you said:

Display 50 lakes starting with fish 51: If 5 fish p/lake then this will display 250 fish (records 51 - 301)
<CFOUTPUT query=&quot;getMatchingRows&quot; group=&quot;lid&quot; startrow=&quot;51&quot; maxrows=&quot;50&quot;>

That makes sense.. or atleast explains the results im getting. But I thought the point of the MAXROWS attribute was to simplify this sort of task for the developer. Dealing with grouping requires me to estimate the number of records I really want? Thats insane.. hehe but I hope you can find a method to the madness.

thanks again tleish, big time pink stars coming your way

 

[tleish]

Also, why do you know want to use an Array or Structure? - tleish

 

[strantheman]

Why do I not want to? I love arrays and structures, but I was hoping to keep this as simple as possible.

The query will be used like a search result query where I can page through the result set. If I have to use a structure to accomplish this, I must store the entire structure in an application or session variable. Outputting session structures is not as fast as outputting cached queries. Also, changing this from a record set to a structure would simply complicate things and limit my ability to quickly make changes to the query.

Im all for doing it the right way the first time, dont get me wrong. Ill do whatever method will work, but I really had not planned on such a complicated solution for something the Certified CF dev. guide explained as &quot;... without this feature [GROUP] a developer might have to resort to more complicated conditional logic and looping to determine when and when not to display the output...&quot;

What sort of solution did you have in mind? Ive been very discouraged of late. thanks

 

[tleish]

Here's one way to do it, I hope you can follow it. The dificulty isn't in outputing the records, the complication comes in creating a navigation that can figure out the records for you.

<!--- Default start row num --->
<CFPARAM NAME=&quot;URL.start&quot; DEFAULT=&quot;1&quot;>

<!--- Number of MaxRows Variable --->
<CFSET vMaxRows = 10>

<!------------------------------------------------
START: Output Lakes and Fish
-------------------------------------------------->
<CFSET vRow = 0>
<CFOUTPUT query=&quot;getMatchingRows&quot; group=&quot;lid&quot; startrow=&quot;#VARIABLES.aRow

[

URL.start

]

#&quot; maxrows=&quot;#VARIABLES.vMaxRows#&quot;>
#lakename#
<br>
<CFOUTPUT>
<li>#fishname#
</CFOUTPUT>
<hr>
</CFOUTPUT>
<!------------------------------------------------
END: Output Lakes and Fish
-------------------------------------------------->

<!------------------------------------------------
START: Create an array that holds the record number
that starts with each lake
-------------------------------------------------->
<CFSET aRow = ArrayNew(1)>
<CFSET vRow = 1>
<CFOUTPUT query=&quot;getMatchingRows&quot; group=&quot;lid&quot;>
<CFSET ArrayAppend(aRow,vRow)>
<CFOUTPUT>
<CFSET vRow = vRow + 1>
</CFOUTPUT>
</CFOUTPUT>
<!------------------------------------------------
END: Create an array
-------------------------------------------------->

<!------------------------------------------------
START: Ouput record Navigation
-------------------------------------------------->
<CFOUTPUT>

<!--- DISPLAY: Previous | Next Link --->
<CFIF URL.start - VARIABLES.vMaxRows GTE 1>
<CFSET vPrev = URL.start - VARIABLES.vMaxRows>
<A HREF=&quot;index.cfm?start=#vPrev#&quot;>Previous</A>
<CFELSE>
Previous
</CFIF>
|
<CFIF URL.start + VARIABLES.vMaxRows LTE ArrayLen(aRow)>
<CFSET vNext = URL.start + VARIABLES.vMaxRows>
<A HREF=&quot;index.cfm?start=#vNext#&quot;>Next</A>
<CFELSE>
Next
</CFIF>


<!--- DISPLAY: Page Numbers based on what vMaxRows is set to --->
<BR><BR>
Page:
<CFSET vArrayCount = 1>
<CFLOOP FROM=&quot;1&quot; TO=&quot;#Evaluate(ArrayLen(aRow)/VARIABLES.vMaxRows)#&quot; INDEX=&quot;i&quot;>
<CFIF URL.start EQ aRow

[

VARIABLES.vArrayCount

]

>
#i#
<CFELSE>
<A HREF=&quot;index.cfm?start=#aRow

[

VARIABLES.vArrayCount

]

#&quot;>#i#</A>
</CFIF>
<CFSET vArrayCount = vArrayCount + (VARIABLES.vMaxRows - 1)>
</CFLOOP><BR>
<BR>


<!--- DISPLAY: Record number items (Records: 55 to 120) --->
<CFSET vArrayCount = 1>
<CFLOOP FROM=&quot;1&quot; TO=&quot;#Evaluate(ArrayLen(aRow)/VARIABLES.vMaxRows)#&quot; INDEX=&quot;i&quot;>
<CFIF URL.start EQ aRow

[

VARIABLES.vArrayCount

]

>
<CFIF URL.start + VARIABLES.vMaxRows LTE ArrayLen(aRow)>
<CFSET vEnd = URL.start + VARIABLES.vMaxRows>
<CFELSE>
<CFSET vEnd = ArrayLen(aRow)>
</CFIF>
<CFBREAK>
</CFIF>
<CFSET vArrayCount = vArrayCount + (VARIABLES.vMaxRows - 1)>
</CFLOOP>
Records: #URL.start# to #aRow

[

vEnd

]

#

</CFOUTPUT>
<!------------------------------------------------
END: Ouput record Navigation
-------------------------------------------------->

- tleish

 

[tleish]

Sorry... found some errors, let's try that one again.... - tleish

 

[tleish]

<!------------------------------------------------
START: Create an array that holds the record number
that starts with each lake
-------------------------------------------------->
<CFSET aRow = ArrayNew(1)>
<CFSET vRow = 1>
<CFOUTPUT QUERY=&quot;getMatchingRows&quot; GROUP=&quot;lid&quot;>
<CFSET ArrayAppend(aRow,vRow)>
<CFOUTPUT>
<CFSET vRow = vRow + 1>
</CFOUTPUT>
</CFOUTPUT>
<!------------------------------------------------
END: Create an array
-------------------------------------------------->

<!--- Default start row num --->
<CFPARAM NAME=&quot;URL.start&quot; DEFAULT=&quot;1&quot;>

<!--- Number of MaxRows Variable --->
<CFSET vMaxRows = 10>

<!------------------------------------------------
START: Output Lakes and Fish
-------------------------------------------------->
<CFOUTPUT query=&quot;getMatchingRows&quot; group=&quot;lid&quot; startrow=&quot;#VARIABLES.aRow

[

URL.start

]

#&quot; maxrows=&quot;#VARIABLES.vMaxRows#&quot;>
#lakename#
<br>
<CFOUTPUT>
<li>#fishname#
</CFOUTPUT>
<hr>
</CFOUTPUT>
<!------------------------------------------------
END: Output Lakes and Fish
-------------------------------------------------->

<!------------------------------------------------
START: Ouput record Navigation
-------------------------------------------------->
<CFOUTPUT>

<!--- DISPLAY: Previous | Next Link --->
<CFIF URL.start - VARIABLES.vMaxRows GTE 1>
<CFSET vPrev = URL.start - VARIABLES.vMaxRows>
<A HREF=&quot;test.cfm?start=#vPrev#&quot;>Previous</A>
<CFELSE>
Previous
</CFIF>
|
<CFIF URL.start + VARIABLES.vMaxRows LTE ArrayLen(aRow)>
<CFSET vNext = URL.start + VARIABLES.vMaxRows>
<A HREF=&quot;test.cfm?start=#vNext#&quot;>Next</A>
<CFELSE>
Next
</CFIF>

<BR><BR>

<!--- DISPLAY: Page Numbers based on what vMaxRows is set to --->

Page:
<CFSET vArrayCount = 1>
<CFLOOP FROM=&quot;1&quot; TO=&quot;#Ceiling(Evaluate(ArrayLen(aRow)/VARIABLES.vMaxRows))#&quot; INDEX=&quot;i&quot;>
<CFIF URL.start EQ VARIABLES.vArrayCount>
#i#
<CFELSE>
<A HREF=&quot;test.cfm?start=#VARIABLES.vArrayCount#&quot;>#i#</A>
</CFIF>
<CFSET vArrayCount = vArrayCount + VARIABLES.vMaxRows>
</CFLOOP>

<BR><BR>

<!--- DISPLAY: Record number items (Records: 55 to 120) --->
<CFIF URL.start + VARIABLES.vMaxRows LTE ArrayLen(aRow)>
<CFSET vEnd = aRow

[

URL.start + VARIABLES.vMaxRows

]

- 1>
<CFELSE>
<CFSET vEnd = aRow

[

ArrayLen(aRow)

]

>
</CFIF>
Records: #aRow

[

URL.start

]

# to #VARIABLES.vEnd#

</CFOUTPUT>
<!------------------------------------------------
END: Ouput record Navigation
-------------------------------------------------->
- tleish

 

[strantheman]

hey thanks for the update!

Hehe, im working on it right now.

thanks again, ill keep you posted.

 

[cfvogue]

By the way,
Just read this thead, but using a query, the groupfield has to be same as order by field in query.
Otherwise grouping in output won't work.

 

[tleish]

Not necessarily. Depending on the DB design, it could cause problems, but not the way they have set it up. - tleish

 

[strantheman]

Thanks again tleish!

Your solutions certainly wasn't the answer I had hoped for, but it did work. It just took me a few days to get around to it. I under estimated how long this part of the project would take, and simple assumed that the nextN style functionality would work with any query.

Although im not happy about the complex solution we've developed here, it does work, and with my implementation of session variables as opposed to passing the state through the URL ive made it transparent to the user.

My original goal was met, however it was much more time consuming than I had first assumed. This is the only time in my two years of CF that ive been less than totally satisfied with the language. I certainly hope that this little red headed step child is kept secret.

thanks again, here's your star!

nic