CODEWITHSUNDEEP
coldfusioncfquerycfloop
kmorris84
kmorris84

Reputation: 25

How to remove row from CFLoop query results

I'm using loop through a query and joining a table when records exist to join.

Is there a way to then keep the row from returning altogether when there is no record to join on that row?

Edit: I missed an if statement in there. I'm looping through records and checking for a setup option, if that setup options exists in a record and there is no corresponding record in the query "someRecord" those are the records I do not want to return.

<cfloop query="myquery">
    <cfif listfindnocase(myquery.setup_option,"required_information")>
        <cfquery name="someRecord" dbtype="query">
            select * from second_table
            where key_id = '#myquery.key_id#'
        </cfquery>
        <cfif someRecord.recordcount eq 0>

        <!--- Need something here to remove this row from returning in the query --->

        </cfif>
    </cfif>
</cfloop>

Upvotes: 1

Views: 2909

Answers (3)

Adam Cameron
Adam Cameron

Reputation: 29870

OK, the model answer here is: do not do this in CFML, do it on the DB. CF is for string generation, not data manipulation.

The question is slightly misleading as it initially asks how to remove rows from the query, which - as it turns out - is not the requirement (see comments on the question). I have answered that question further down.

To simply exit an iteration of a loop, use the <cfcontinue>. This ends the current iteration of the loop immediately, and returns to the top of the code block and starts the next iteration. Using your own code example:

<cfloop query="myquery">
    <cfif listfindnocase(myquery.setup_option,"required_information")>
        <cfquery name="someRecord" dbtype="query">
            select * from second_table
            where key_id = '#myquery.key_id#'
        </cfquery>
        <cfif someRecord.recordcount eq 0>
            <cfcontinue>
        </cfif>
        <!--- handle the rows you *do* want to process here --->
    </cfif>
</cfloop>

However to answer the question of how to remove rows from a query, there's no elegant way of doing it. You have two inelegant options:

// pseudocode, for brevity
newQuery = queryNew(oldQuery.columnList)
loop (oldQuery)
    if the row is not wanted
        continue
    /if
    add a row to newQuery
    add the row data to newQuery
/loop

Or:

listOfRowsToExclude = someMechanismToArriveAtSaidList()
<cfquery name="newQuery" type="query">
    SELECT   *
    FROM     oldQuery
    WHERE    id NOT IN (listOfRowsToExclude)
    ORDER BY [same clause as for oldQuery]
</cfquery>

However by far the best advice to be had here is to do your data processing in the DB where it belongs. You should not be putting this sort of logic in a) your CFML app; b) in your view code, which is where I suspect all this is going.

Separate your logic from your display. And separate your data-processing logic from your application logic.

Upvotes: 5

StaticVoid
StaticVoid

Reputation: 1537

Rather than loop through the query to make multiple SELECTs (as others have mentioned that it uses unnecessary resources), perhaps try an IN clause using ValueList to extract a list of IDs:

<cfset myqueryIDs = ValueList(myquery.key_id)>
<cfif listfindnocase(myquery.setup_option,"required_information")>
    <cfquery name="allRecords" dbtype="query">
        select * from second_table
        where key_id IN (#myqueryIDs#) <!--- Assuming numeric IDs --->
    </cfquery>
</cfif>

Upvotes: 1

Neil N
Neil N

Reputation: 25258

I assume there is something else done after your closing cfif, you should invert your if statement and do this:

<cfloop query="myquery">
    <cfquery name="someRecord" dbtype="query">
        select * from second_table
        where key_id = '#session.key_id#'
    </cfquery>

    <cfif someRecord.recordcount neq 0>

      <!--- do what you would do when record count NOT equal to zero --->
    </cfif>
</cfloop>

Upvotes: 0

Related Questions