Run a cfquery inside of a query output

Question

I am getting weird unexpected results when I try to run a cfquery inside of the output of a previous query.

Example:

<cfoutput query="reportInfo">
  <tr>
    <td>#id#</td>
    <td>#name#</td>    
    <cfif Status EQ 'Complete'>
      <cfquery name="submitInfo" datasource="RC">
        SELECT  *
        FROM    Action_Log
        WHERE Form_ID = '#id#'
        AND Action = 'Complete' OR Action = 'Saved'
      </cfquery>    
      <cfset startStamp = submitInfo.Action_Time>
      <cfoutput>startStamp</cfoutput>    
      <td>#startStamp#</td>
    <cfelse>
      <td>No Completion Date</td>
    </cfif>
  </tr>
</cfoutput>

When "StartStamp" is output to the page it is shown once for each count in the loop and it does this for each ID so there is a lot of extra.

I thought it should only run the submitInfo query once every time the contain query (reportInfo) output loops through but that is not happening.

The query will only be ran one time per record in `reportInfo`. I'm not sure I understand what you are expecting. — Matt Busche, May 12 '15 at 18:56
as an aside your query is probably not written as you expect. If you want to query for the form_ID and the action being either complete or saved you need `()` around your two action clauses. In addition you should be able to do this all in one query without the nested query. — Matt Busche, May 12 '15 at 18:57
Not sure what you are trying to accomplish, but you should get rid of the inner cfoutput tags ie `startStamp`. They are only needed when using the cfoutput group feature. Having said that, querying within a loop should be avoided. Most times you can achieve the same result with a simple JOIN. Without knowing more about the first query, I see no reason you cannot do that here. — Leigh, May 12 '15 at 18:58
Thanks everyone for the notes and comments. I am going to explore building the whole thing in one query. After removing the cfoutput from startStamp I am already seeing an improvement. Before the numbers were duplicating (best way to describe it) every time through the main query. — Denoteone, May 12 '15 at 19:04
That was due to the extra set of `cfoutput` tags. When you nest cfoutput tags CF thinks you want to use the [`group` attribute](https://wikidocs.adobe.com/wiki/display/coldfusionen/cfoutput) (which "..eliminates adjacent duplicate rows when data is sorted"). Hence the repeats :-) Though removing the extra tags solves the issue, switching to a single JOIN is definitely the way to go. It is much more efficient than hitting the database on each iteration - for every request. — Leigh, May 12 '15 at 21:50

score 1 · Accepted Answer · edited May 23 '17 at 12:06

(Expanded from comments ...)

<cfoutput>startStamp</cfoutput>

Not sure what you are trying to accomplish, but you should get rid of the inner cfoutput tags. Those are only needed when using the (frequently misunderstood) <cfoutput group="..."> feature. It is normally used to eliminate duplicates from sorted data. However, if the data is not sorted properly - or is grouped incorrectly - it can produce the kind of results you described. (If you are not familiar with grouping, see this example of creating an A-Z type listing).

Having said that, querying within a loop should be avoided. Most times you can achieve the same result with a simple JOIN. Without knowing more about the first query, I see no reason you cannot do that here.

In psuedo-sql something along these lines. It uses an OUTER JOIN to retrieve all records from the first table, but only the values from Action_Log that have a matching ID and status of "Complete" or "Saved".

SELECT  t1.ID
        , t1.Name
        , al.Action_Time AS CompletionDate
FROM    SomeTable t1
      LEFT JOIN Action_Log al 
                ON al.Form_ID = t1.ID 
                AND t1.Status = 'Complete'
                AND al.Action IN ('Complete','Saved')

Run a cfquery inside of a query output

1 Answers1