CFML converting evaluate() query string to structure syntax

Question

I have the code below inside of a loop so as the loop iterates I'm setting a dynamic variable temp to the value of the getAdvisor_Advisors.advisor_ID for the current loop iteration.

<cfset temp = "getAdvisor_Advisors#LoopCount#.advisor_ID">

This cfinvoke below calls a query that I pass in the dynamic "temp" variable but have to use the slow evaluate(temp) around it to get the proper value.

<cfinvoke component="com.appointments" method="get_All_Appointments" returnvariable="getAppointments">
      <cfinvokeargument name="Advisor_ID" value="#evaluate(temp)#">
      <cfinvokeargument name="StartDay" value="#dateFormat(form.cal,'dd')#">
      <cfinvokeargument name="StartMonth" value="#dateFormat(form.cal,'mm')#">
      <cfinvokeargument name="StartYear" value="#dateformat(form.cal,'yyyy')#">             
</cfinvoke>

I'm wanting to rewrite the temp variable and evaluate() to not use evaluate. I'm told I can use the structure syntax to reference it sort of like the following:

Without evaluate:

<cfset foo = qBar["text#lang#"][CurrentRow]>
getAdvisor_Advisor["advisor_ID"][CurrentRow]

How do I rewrite

<cfset temp = "getAdvisor_Advisors#LoopCount#.advisor_ID">

using structure syntax?

Adding more of the code so you can see why this is complicated these are compound loops.

            <cfquery name="getAdvisor_Advisors1" dbtype="query" cachedWithin="#CreateTimeSpan(0,0,15,0)#">
                Select *
                From getAdvisors
                Where Express = 'FR/SO' 
                Order by Specialization, Advisor
            </cfquery>

            <cfquery name="getAdvisor_Advisors2" dbtype="query" cachedWithin="#CreateTimeSpan(0,0,15,0)#">
                Select *
                From getAdvisors
                Where Express = 'JR/SR' 
                Order by Specialization, Advisor
            </cfquery>

            <cfquery name="getAdvisor_Advisors3" dbtype="query" cachedwithin="#CreateTimeSpan(0,0,15,0)#">
                Select *
                From getAdvisors
                Where Specialization IS NULL AND Appointments = 1 AND Campus_ID > 0
                Order by Campus_ID, Advisor
            </cfquery>

            <cfquery name="getAdvisor_Advisors4" dbtype="query" cachedwithin="#CreateTimeSpan(0,0,15,0)#">
                Select *
                From getAdvisors
                Where Specialization IS NOT NULL
                AND Title != 'BCC-GA' 
                Order by Specialization, Advisor
            </cfquery>

            <div id="calendarGrid">
                <!--- looping over the filter queries above that split advisors into groups --->   
                <cfloop index="LoopCount" from = "1" to = "4"> 
                    <!--- FR/SO Advisors --->
                    <cfif LoopCount LTE 3>
                        <cfset currGroup = "campus_id">
                    <cfelse>
                        <cfset currGroup = "specialization">
                   </cfif>
                    <cfoutput query="getAdvisor_Advisors#LoopCount#" group="#currGroup#">
                    <div class="advisorGrouping">
                        <div id="calcontainer">
                            <table class="pickme" border="0" cellspacing="1" cellpadding="1">
                                <tr class="hdr">
                                    <td width="6.9%">
                                        <cfif (Specialization IS "BCC") OR (Specialization IS "HONORS")>
                                            #uCase(Specialization)#
                                        <cfelse>                           
                                            #uCase(Campus_Text)# 
                                        <cfif Len(Express) NEQ 0>
                                            - #uCase(Express)#
                                        </cfif>
                                    </cfif>
                                    </td>
                                    <td width="4.9%" class="border">8:00</td>
                                    <td width="4.9%" class="border">8:30</td>
                                    <td width="4.9%" class="border">9:00</td>
                                    <td width="4.9%" class="border">9:30</td>
                                    <td width="4.9%" class="border">10:00</td>
                                    <td width="4.9%" class="border">10:30</td>
                                    <td width="4.9%" class="border">11:00</td>
                                    <td width="4.9%" class="border">11:30</td>
                                    <td width="4.9%" class="border">12:00</td>
                                    <td width="4.9%" class="border">12:30</td>
                                    <td width="4.9%" class="border">1:00</td>
                                    <td width="4.9%" class="border">1:30</td>
                                    <td width="4.9%" class="border">2:00</td>
                                    <td width="4.9%" class="border">2:30</td>
                                    <td width="4.9%" class="border">3:00</td>
                                    <td width="4.9%" class="border">3:30</td>
                                    <td width="4.9%" class="border">4:00</td>
                                    <td width="4.9%" class="border">4:30</td>
                                    <td width="4.9%" class="border">5:00</td>
                                </tr>
                                <cfoutput group="advisor_id">


                                    <cfset temp = "getAdvisor_Advisors#LoopCount#.advisor_ID">


                                    <!--- get Appts for cal date --->   
                                    <cfinvoke component="com.appointments" method="get_All_Appointments" returnvariable="getAppointments">
                                        <cfinvokeargument name="Advisor_ID" value="#evaluate(temp)#">
                                        <cfinvokeargument name="StartDay" value="#dateFormat(form.cal,'dd')#">
                                        <cfinvokeargument name="StartMonth" value="#dateFormat(form.cal,'mm')#">
                                        <cfinvokeargument name="StartYear" value="#dateformat(form.cal,'yyyy')#">             
                                    </cfinvoke>  

Belonging

What is the scope of the variable? Use the same bracket notation syntax, just add the scope, ie #scopeName["get_Advisors"& loopIndex]["advisor_ID"][someRowNumber]#. Might also want to investigate whether the dynamic variable name is even necessary. – Everard

Currently "temp" is a variable scope, but if I can use direct notation I don't need a temporary variable. I just knew I could get there by using an evaluate() on a temp dynamic... which works but is slow and not best practice. But the value I need is a cfquery "getAdvisor_Advisor.advisor_ID" is a cfquery with the advisor_ID column. – Belonging

Try it with the bracket notation above. Though again, depending on what the code is doing, could be the dynamic name is not even needed. – Everard

I tried with the bracket notation it did not work. Not sure if it was my syntax or what. – Belonging

Worked fine in a quick test, so it is probably your syntax. Though, again ... I would recommend exploring whether you even need dynamic query names. – Everard

Does get_All_Appointments get appointments from another db table? Which dbms? – Everard

MS SQL, they are all in the same table. – Belonging

Any reason you must use separate queries? Querying within a loop is very inefficient. This sounds like a perfect job for a stored procedure. – Everard

Answer 1

To answer the question, you can use QueryToStruct. Set an array with each index being a structure of the query.

<!--- CREATE THE ARRAY --->
<cfset temp = arrayNew(1)>

<cfquery name="getAdvisor_Advisors1" dbtype="query" cachedWithin="#CreateTimeSpan(0,0,15,0)#">
    Select *
    rom getAdvisors
    Where Express = 'FR/SO' 
    Order by Specialization, Advisor
</cfquery>

<!--- CREATE AND FILL THE STRUCT --->
<cfset temp[1] = QueryToStruct(getAdvisor_Advisors1)>

Then later you can loop over that array temp and output the values.

<cfloop from="1" to="#arrayLen(temp)#" index="t">
    ...
    <cfinvokeargument name="Advisor_ID" value="#temp[t].advisor_ID#">
    ...
</cfloop>

Info for QueryToStruct: https://gist.github.com/erikvold/764276

Here is a screenshot of it working:

Original answer: Why not use the query for the cfloop, or cfoutput? Then it's trivial:

<cfloop query = "getAdvisor_Advisor">
<cfinvoke component="com.appointments" method="get_All_Appointments" returnvariable="getAppointments">
      <cfinvokeargument name="Advisor_ID" value="#getAdvisor_Advisors.advisor_ID#">
      <cfinvokeargument name="StartDay" value="#dateFormat(form.cal,'dd')#">
      <cfinvokeargument name="StartMonth" value="#dateFormat(form.cal,'mm')#">
      <cfinvokeargument name="StartYear" value="#dateformat(form.cal,'yyyy')#">             
</cfinvoke>
</cfloop>

Unfruitful

This will not work because I"m not looking for the current row of the getAdvisor_Advisor query I'm looking for the current row of getAdvisor_Advisor#X# query. Which is one of 4 different query of queries, depending upon where we are in the index loop. – Belonging

Oh I see. I'll rewrite my answer when I get back to my desktop. – Unfruitful

This Looks right, your revised answer however when I try that the page dies as soon as it hits <cfset temp[1] = QueryToStruct(getAdvisor_Advisors1)> Like it cannot run that part... also there is no error message. – Belonging

You first have to create the array with arrayNew(1). If you use a regular 2 dimensional array, you can put a struct in it. I amended my answer. – Unfruitful

Did you download and insert the function from the link I posted? – Unfruitful

Where do I insert the function? – Belonging

Anywhere in the template. – Unfruitful

Answer 2

Your current approach is more complicated than necessary and you have too much going on inside your loop. Start with the things that don't change with each loop iteration.

myObject = CreateObject("component","com.appointments");
argumentStructure = StructNew();
argumentStructure.StartDay= dateFormat(form.cal,'dd');
argumentStructure.StartMonth= dateFormat(form.cal,'mm');
argumentStructure.StartYear = dateFormat(form.cal,'yyyy');
</cfscript>

Then do your loop.

<cfloop query = "getAdvisor_Advisor">
<cfscript>
argumentStructure.advisor_ID = advisor_ID;
variableFromObject = myObject.get_All_Appointments(argumentCollection = argumentStructure);
//  code to process that variable
</cfscript>
</cfloop>

Note that the question has no information about what the method is returning. That particular detail could affect the last part of the answer.

Silvereye

This returns all of the appointments for a given date on the calendar and a given advisor. It is then inside of a loop that is looping over groups of advisors. – Belonging

Answer 3

variables["getAdvisor_Advisors#LoopCount#"].advisor_ID[variables["getAdvisor_Advisors#LoopCount#"].currentRow]`

also remember that variables usually reference objects so you can also clean up code by referencing it by another variable name...

currentQuery = variables["getAdvisor_Advisors#LoopCount#"];
currentQuery.advisor_ID[currentQuery.currentRow]`

---

Also an additional suggestion. When I want to have a list of queries or actions to be taken I will usually either put the queries into a struct and then iterate over them or have a list of names to loop over.

So for example if the order doesn't matter...

<cfset queries = {} />
<cfquery name="queries.AdvisoryQuery1Name" ...>
    ...
</query>
<cfloop collection="#queries#" ...

or if the order does matter and there is a known set of names you can loop over a list or array. The list could also be dynamically generated.

<cfloop list="FR_SO_ExpressAdvisors,JR_SR_ExpressAdvisors,etc" index="queryname">
    <cfset query = variables[queryname] />
    .... etc

Both of these allow you to name the queries in a more descriptive way.

Groundage