Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

CFML converting evaluate() query string to structure syntax

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>  
like image 405
user2676844 Avatar asked May 17 '17 13:05

user2676844


2 Answers

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: enter image description here

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>
like image 69
Jules Avatar answered Sep 30 '22 05:09

Jules


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.

like image 29
Dan Bracuk Avatar answered Sep 30 '22 06:09

Dan Bracuk