ColdFusion: Get RecordCount with a variable in the name

Question

I want to get the record count of a query that has a variable in it's name.

<cfloop query="Getteam">
    <cfquery name="GetJobs#teamstaffid#" datasource="#dataSource#" >
        SELECT  *
        FROM    Request, Hist_Req_Assign, Hist_Req_status
        WHERE   hist_req_assign.teamstaffid = '#teamstaffid#' AND
                hist_req_assign.requestid = request.requestid AND
                hist_req_status.requestid = request.requestid AND
                hist_req_status.statusid = '3'
    </cfquery>
</cfloop>

GetTeam spits out the ID of each staff member in my team.
And GetJob#teamstaffid# gets all their jobs.

MY first instinct is to do: <cfoutput>#GetJobs#teamstaffid#.RecordCount#</cfoutput>

This obviously wont work though. How can I get the record count of each team member? Thanks

Do a search for coldfusion dynamic variable (or query). There are a *ton* of examples. Having said that, this type of aggregation is better done with a single database query. Querying within a loop is very inefficient. Side note - always use cfqueryparam on variable query parameters. It helps improve query performance and protect against sql injection. — Leigh, Aug 04 '16 at 14:56

Alex · Answer 1 · 2016-08-04T23:26:49.047

The name attribute in your cfquery tag is the output variable of your query. If not specified, the default scope of a variable such as GetJobs#teamstaffid# is VARIABLES. Variable scopes in ColdFusion are structs.

To access dynamic variables, use the bracket notation for structs:

<cfoutput>#VARIABLES["GetJobs#teamstaffid#"].RecordCount#</cfoutput>

Note: If your code is part of a function, use the LOCAL scope instead of the VARIABLES scope.

score 1 · Answer 2 · answered Aug 04 '16 at 16:05

1

I would probably do something along these lines:

<cfscript>
try {
    sql = "select * from Request, Hist_Req_Assign, Hist_Req_status where hist_req_assign.requestid = request.requestid and hist_req_status.requestid = request.requestid and hist_req_status.statusid = '3'";
    principalQuery = new query();
    principalQuery.setDatasource(dataSource);
    result = principalQuery.execute(sql=preserveSinglequotes(sql));
    getJobs = result.getResult();

    for(i=1;i<=listLen(teamstaffid);i++){ 
        sql = "select request, Hist_Req_Assign, Hist_Req_status from sourceQuery where hist_req_assign=#teamstaffid[i]#";
        local.queryService = new query();
        local.queryService.setName("employee");
        local.queryService.setDBType("query");
        local.queryService.setAttributes(sourceQuery=getJobs);
        local.objQueryResult = local.queryService.execute(sql=sql);
        local.queryResult = local.objQueryResult.getResult();
        writeOutput("Employee " & teamstaffid[i] & " has " & local.queryResult.recordcount & " records.");
    }
    } catch (any e){
        //whatever
    }
</cfscript>

answered Aug 04 '16 at 16:05

Keith Fosberg

89
2
9

3

Sorry, but this is not a good approach for a few reasons. A) Querying within a loop is very inefficient. Usually it can be avoided by constructing the proper query. That means a *single* db call - instead of one for *every record* in the outer query (50,100, etcetera...). B) Do not use `preserveSinglequotes()`. It reverses the minimal protection CF provides automatically and exposes the database to SQL-I. Instead, use `` or `addParam`. Using bind variables not only protects against SQL-I, but also improves performance for queries executed multiple times. – Leigh Aug 04 '16 at 19:37
That is only one DB call. The second query is not a DB call. It is a query of the structure created in the results of the first query. I would tend to agree on the param point, but would consider the risk pretty minimal in this case since no user variables are used. The list used to specify parts of the structure is part of the code, it is not an input. – Keith Fosberg Aug 08 '16 at 13:58
That does not really make much difference. There are still costs associated with parsing and building the query results, [which enterprise databases tend to optimize better than QoQ's](http://stackoverflow.com/questions/13632246/cf-qoq-vs-query). With a single db query the cost is incurred *only once*. With looping, those costs are multiplied many times: once per record - per request. Usually resulting in a lot more time and resources than are actually necessary. Hence why looping is often the least efficient and slowest method, and tends to scale poorly. – Leigh Aug 08 '16 at 16:57
*since no user variables are used.* Do not assume any data is safe. Even if you use `cfqueryparam`/`addParam` religiously, it does absolutely nothing to prevent malicious sql from being *saved* in the database. If any malicious values were entered, using `preserveSinglequotes` means they would be executed. – Leigh Aug 08 '16 at 17:16

score 0 · Answer 3 · answered Aug 06 '16 at 16:41

The cfquery tag returns some result variables in a structure. So, we use result attribute in the cfquery tag we can able to get some details of the query.

For example: 1. resultname.sql 2. resultname.recordcount

<cfloop query="Getteam">
    <cfquery name="GetJobs#teamstaffid#" datasource="#dataSource#" result="resultname">
        SELECT  *
        FROM    Request, Hist_Req_Assign, Hist_Req_status
        WHERE   hist_req_assign.teamstaffid = '#teamstaffid#' AND
                hist_req_assign.requestid = request.requestid AND
                hist_req_status.requestid = request.requestid AND
                hist_req_status.statusid = '3'
    </cfquery>
</cfloop>

<cfoutput>#resultname.recordcount#</cfoutput>

score 0 · Answer 4 · answered Aug 08 '16 at 17:48

Just use Coldfusion function Evaluate

<cfoutput>#Evaluate("GetJobs#teamstaffid#").RecordCount#</cfoutput>
<cfoutput>#Evaluate("GetJobs#teamstaffid#").column1#</cfoutput>
<cfoutput>#Evaluate("GetJobs#teamstaffid#").column2#</cfoutput>
.....

While you can access any column or field from the record set using Evaluate, it's better to store the returned query object first in a variable (within the loop) as a short hand access so you could easily reach other columns/fields from the query record set.

<cfloop query="Getteam">
    <cfquery name="GetJobs#Getteam.teamstaffid#" datasource="#dataSource#" >
        SELECT  *
        FROM    Request, Hist_Req_Assign, Hist_Req_status
        WHERE   hist_req_assign.teamstaffid = '#Getteam.teamstaffid#' AND
            hist_req_assign.requestid = request.requestid AND
            hist_req_status.requestid = request.requestid AND
            hist_req_status.statusid = '3'
    </cfquery>

    <cfset QGetJob = #Evaluate("GetJobs#Getteam.teamstaffid#")# />

    <cfoutput>#QGetJob.RecordCount#</cfoutput>
    <cfoutput>#QGetJob.column1#</cfoutput>
    <cfoutput>#QGetJob.column2#</cfoutput>
</cfloop>

score 0 · Answer 5 · answered Aug 09 '16 at 15:20

In a direct answer to the question; you can extract the data name in context (GetJobs#teamstaffid#) within each iteration and store it to a global list/array/ structure to use later but, as has been discussed, it would be better to structure the query to get everything at once.

It would be hard to provide a specific on the SQL without knowing your schema.

ColdFusion: Get RecordCount with a variable in the name

5 Answers5