Nested Loop not looping...

[nohandlesleft254]

Hi,

For some reason the 'GROUP1.DoorCodes' loop is repeating its first value for every output row, yet it is outputting the correct number of rows.

Dont know what im missing!! Can post more info if anyone has an idea...


<!--- PRICE GROUP1 -------------------------------------------------------------------------------------------------------------------------------->
<!--- Reset the GROUP1.MissingItems query for this item --->
<cfif isDefined ('GROUP1.MissingItems')>
<cfset StructDelete(GROUP1, "MissingItems")>
</cfif>

<!--- See if the Item has a recipe --->
<cfquery datasource="#Application.HansaData#" name="FindRecipe">
SELECT
Code,
Item,
Comment,
InQty
FROM RECIPESROW
WHERE Code = '#FindItems.Recepy#'
AND (Item LIKE '%ZZDR%'
OR Item LIKE '%ZZDW%')
</cfquery>

<!--- If the item has a recipe; loop through and add the prices up --->
<cfif FindRecipe.Recordcount gt 0>
<cfset RecipePrice = '0'>
<cfset GROUP1.MissingItems = #QueryNew("Code, Comment, Door")#>

<!--- BEGIN LOOPING THROUGH THE DIFFERENT DOOR STYLES FOR THIS GROUP --->
<cfloop query="GROUP1.DoorCodes">

<!--- If the unit is Glazed; find the default glazing type --->
<cfif ListFindNoCase(FindItems.DispGroups,'GLAZ') GT 0>
<cfquery datasource="#Application.HansaData#" name="FindDefaultGlazing">
SELECT
Code,
Mother,
DefGlazeRange,
DefGlazeRangeDesc
FROM DOORRANGES
WHERE CODE = '#GROUP1.DoorCodes.Code#'
</cfquery>
</cfif>

<!--- Loop through the recipe to find the products --->
<cfloop query="FindRecipe">
<cfquery datasource="#Application.HansaData#" name="FindExtraPrice">
SELECT
Code,
UPrice1,
SubDoor,
DRang,
DrawTyp,
SubDraw
FROM ITEMS
WHERE (SubDoor = '#FindRecipe.Item#'
OR SubDraw = '#FindRecipe.Item#')

<!--- If the unit is glazed; use the FindDefaultGlazing.DefGlazeRange --->
<cfif ListFindNoCase(FindItems.DispGroups,'GLAZ') GT 0>
AND (Drang = '#FindDefaultGlazing.DefGlazeRange#'
OR Drang = '#GROUP1.DoorCodes.Code#'
OR DrawTyp = '#GROUP1.DrawerCode#')

<!--- If the unit is standard; use the GROUP1.DoorCode --->
<cfelse>
AND (Drang = '#GROUP1.DoorCodes.Code#'
OR DrawTyp = '#GROUP1.DrawerCode#')
</cfif>
</cfquery>

<!--- If the Recipe Item exists in the Inventory - Add the price to the RecipePrice Var --->
<cfif FindExtraPrice.Recordcount gt 0>
<cfset RecipePrice = RecipePrice + (FindRecipe.InQty * FindExtraPrice.UPrice1)>

<!--- If the item doesn't exist; find and add the missing item to the GROUP1.MissingItems query --->
<cfelse>
<cfif GROUP1.MissingItems.RecordCount gt 0>
<cfset Row = (GROUP1.MissingItems.RecordCount +1)>
<cfelse>
<cfset Row = 1>
</cfif>
<cfset NewRows = QueryAddRow(GROUP1.MissingItems, 1)>
<cfset QuerySetCell(GROUP1.MissingItems, "Code", FindRecipe.Item, Row)>
<cfset QuerySetCell(GROUP1.MissingItems, "Comment", FindRecipe.Comment, Row)>
<cfset QuerySetCell(GROUP1.MissingItems, "Door", GROUP1.DoorCodes.Code, Row)>
</cfif>
</cfloop>

<!--- If there are no missing items; break the loop --->
<cfif GROUP1.MissingItems.RecordCount eq 0>
<cfbreak>
<cfelse>

<!--- If there are missing items; reset the recipe price ready for another round --->
<cfset RecipePrice = 0>
</cfif>
</cfloop>

<!--- Add the RecipePrice to the Base Price --->
<cfset GROUP1.TotalPrice = RecipePrice + FindItems.UPrice1>
<cfset RecipeExists = 'yes'>
<cfelse>

<!--- If there is no recipe set the total price as the Base Price --->
<cfset GROUP1.TotalPrice = FindItems.UPrice1>
<cfset RecipeExists = 'no'>
</cfif>

<!--- END PRICE GROUP1 ------------------------------------------------------------------------------------------------------------------------------>

 

[r937]

that's a humungous block of code that appears to be doing queries inside loops, which is always a terrible performance drag

you should be using JOIN queries instead

r937.com | rudy.ca

 

[nohandlesleft254]

I would love to, in fact i squirm when i look at it, but i couldnt see any other way as the amount of outcomes for each product is approx 500, and there are 900 of these products. If i were to try and gather all the information before adding and outputting the relevant pieces, it would be 'extra humungous'. The client has a dedicated internal server so the performance is a patience issue more than anything.

Ive been working on several of these types of output for this client so if ive become blind to the obvious (which i probably have) could you maybe point out what you think you'd move around?...

 

[r937]

i would first of all concentrate on what data i want to retrieve from the database, before writing code

it sure looks like you can combine some of them

this isn't so much "moving around" as rewriting

r937.com | rudy.ca

 

[nohandlesleft254]

point taken - back to the drawing board then

thanks

 

[Aikien]

Generally using a <cfloop> with the query parameter isn't a good idea. The inner loop will often corrupt the CurrentRow count for the outer loop. Instead, use the index syntax to do your nested loops. In your case, something like:

<cfloop index="i" from="1" to="GROUP1.DoorCodes.recordCount">
<!--- bunch of code here --->
<!--- reference to query variable now looks like this --->
<cfset tempVar=GROUP1.DoorCodes.Code>

<!--- second nested loop --->
<cfloop index="j" from="1" to="FindRecipe.recordcount">
<!--- Reference to variable looks like this --->
<cfset tempVar=FindRecipe.Item[j]>
</cfloop>
</cfloop>

This uses a defined loop counting variable and so will not have the same problems as the nested <cfloop> query & conditional constructs.

 

[imstillatwork]

just to add, I notices your code comment mentioned looping the query to get the sum, this should also be in the query, with the SQL SUM() function, saving quite a bit of CF code.

So... what is the goal of this code? what results do you want?

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[nohandlesleft254]

Hi Guys,

Thanks for looking at this. I have ended up doing it differently from the code i submitted here, it has actually taken more code, but has ended up being far more reliable.

Thanks to Aikien, i wasnt aware of that and i will def keep it in mind.

As for the final comment on sum - that comment refers to the whole block of code following that comment, i could not sum the query as the various different products that make up the price are found through several diferent queries. The problem with this whole pricing routine is the fact that each item can have dynamic parts that depend various options chosen by the user - nightmare!