coldfusion 11 loop in rightful way

Question

i see this code in one of the pages, seems annoying, it can be shorted, if i am on Coldfusion 11 https://trycf.com/gist/701515844d5e41549a7b6e61dfafeaa0/lucee5?theme=monokai

Here is the code how its looks like

<cfloop list="#mqry.columnList#" index="col">
<cfset ColRow ="#mqry.columnList#," >                     
</cfloop>  


<cfset  x = replace(ColRow,"CONDITION1","CONDITION") & chr(13) & chr(10)>
<cfloop query="mqry">               
<cfloop list="#mqry.columnList#" index="colN"> 
    <cfset f = Replace("#mqry[colN][currentRow]#" ,"""","","All")>
    <cfset f = REReplaceNoCase("#mqry[colN][currentRow]#" , "<[^>]*(?:>|$)", "", "ALL")>
    <cfset x = x & """#_f#"",">  
</cfloop> 
<cfset  x = x & chr(13) & chr(10)>                          
</cfloop>

the first value i think i can do as:

mquery.columnlist - no need to loop over

Looks like you are trying to generate a CSV from the query? Is the underscore in front of each cell value intentional? And why does every line end with a delimiter `,`? — Alex, Aug 07 '21 at 13:02
yes, trying to generate csv, it does its work but it is written in a very weird way, i am trying to steamline it and remove unnecessary loops from here, i had updated my gist — muaa, Aug 07 '21 at 13:04
@DanBracuk Are you referring to "CRLF vs LF"? Because CSV needs newlines and there is one for the column header and one for every row. Purpose should be clear. — Alex, Aug 07 '21 at 16:28

Alex · Answer 1 · 2021-08-07T16:31:40.937

1

There's nothing wrong with the nested loop. You iterate over all rows and then iterate over each cell per row using the column name. I rewrote your code to be easier understandable:

<cfset NEWLINE = (chr(13) & chr(10))>
<cfset DELIM   = ",">
<cfset QUOTE   = '"'>

<!--- column headers --->
<cfset x = mqry.columnList>

<!--- replace column names as desired --->
<cfset x = replace(x, "CONDITION1", "CONDITION")>

<!--- next line --->
<cfset x &= NEWLINE>

<cfloop query="mqry">

    <cfset row = []>

    <cfloop list="#mqry.columnList#" index="colN">

        <cfset cellValue = mqry[colN][mqry.currentRow]>

        <!--- remove all quotes --->
        <cfset cellValue = replace(cellValue, QUOTE, "", "ALL")>

        <!--- remove tags --->
        <cfset cellValue = reReplaceNoCase(cellValue, "<[^>]*(?:>|$)", "", "ALL")>

        <!--- prefix with underscore --->
        <cfset cellValue = ("_" & cellValue)>

        <!--- wrap in quotes --->
        <cfset cellValue = (QUOTE & cellValue & QUOTE)>

        <cfset row.add(cellValue)>

    </cfloop>

    <!--- combine cell values into a row --->
    <cfset x &= arrayToList(row, DELIM)>

    <!--- next line --->
    <cfset x &= NEWLINE>

</cfloop>

<cfoutput>#x#</cfoutput>

edited Aug 07 '21 at 16:31

answered Aug 07 '21 at 13:23

Alex

7,743
1
18
38

Thanks @alex, i added my updated gist in which i am sharing there are three loops and in one main loop which run 10 times and then the file is created, i hope you might have seen my updated gist... – muaa Aug 07 '21 at 15:36
I assume you are executing 10 different SQL statements and generating a CSV file from each of the resultsets right? What's the issue then? – Alex Aug 07 '21 at 16:22
it seems to timeout and takes a lot of time, i want to make it better by using something like arrays of anything else which can make its processing faster, maybe a direct way of converting the query to csv or something, – muaa Aug 07 '21 at 18:03
Start by measuring the parts then. Make sure it's not the query that is taking too long. You can try optimizing the concatenation by using `java.lang.StringBuilder`. Converting the `columnList` to an array might be slightly faster too. But these optimizations only start to matter if you are processing really big lists. And at that point, memory starts mattering too as minor collections might start ramping up. How many records are in your resultsets? – Alex Aug 07 '21 at 21:25
the record is quite huge, so usually 8 out of 10 times it is timing out, i tried the query to run it as standalone, does not seems to be taking too much of time. – muaa Aug 08 '21 at 18:09

coldfusion 11 loop in rightful way

1 Answers1