Need help with structs

[BreakawayPaul]

I'm building a highway noise barrier inventory tool that will allow people to search all the current highway noise barriers in the US by state, type, material, etc (I know, I know, exciting stuff).

I'm needing to slice and dice the data many different ways, so I'm using cffunctions and cfcs.  I've found out that it works better to have a function for each thing I want to call, rather than trying to stuff it all into a two or three huge queries.  This has worked well, except for one place, where I have a list of barriers by state and year, and within each record, I have to grab all the materials.  This is sort of a query in a nested loop, and as such, is SLOW.

I solved the speed problem on another page by just running one bit cached query at the top of the page, then doing this:

<cfset qgetMaterialsbyID = structNew()>
<cfloop query="qgetallbarriermat">
    <cfset qgetMaterialsbyID[qgetallbarriermat.barrierid[qgetallbarriermat.currentRow]] = qgetallbarriermat.material[qgetallbarriermat.currentRow]>
</cfloop>

This has worked great (and has been super fast) when I want to get one piece of information, but in this case, I can have multiple materials per barrier.

qgetMaterialsbyID[id].material  works great for getting one of the materials, and I can dump qgetMaterialsbyID[id] to get them all, but I can't figure out how to loop through qgetMaterialsbyID[id] to get all the materials for that ID.  Or maybe that struct is only good for one result?  I've been staring at it for so long, I'm not sure which way is up anymore.  Thanks for any help!

[WolfShade]

Are you absolutely, positively certain that you have to put a query within a loop, and that it cannot be done with UNION and/or JOIN?

Putting a loop in a query is processor intensive, memory hogging, and could bottleneck your network.  The _only_ situation where I would use that kind of process is if I'm pulling data from different schemas and the security is so tight that it prevents cross-schema querying.  Where I work, that's common.

If it cannot be done with UNION / JOIN statements, I think using a CURSOR would be preferential to a looped query.

Just my two cents.

V/r,

^_^

[BreakawayPaul]

Well I don't want to have a query inside a loop, which is why I want to do it with a struct or an array.  I just don't know what syntax I need to loop through the results.

On the page that has the struct with a single result, the page rendering time is pretty much instant, since it's pulling the info from the query that's in memory.  I just can't figure out how to get more than the one row.

I've had some luck doing this too:

<cfset qgetMaterialsbyID = ArrayNew(2)>
<cfloop query="qgetallbarriermat">
    <cfset qgetMaterialsbyID[currentrow][1] = barrierid>
    <cfset qgetMaterialsbyID[currentrow][2] = material>
</cfloop>

But again, I'm not sure how to get all the rows.

The problem with changing the master query is that I then I have to go to the other 22 pages where it's used and redo those.  I mean I'd really rather figure out how to do it this way than redoing the entire app.