Eliminating Evaluate from my application

[Guest]

Ive been doing a little bit of reading and a lot of people seem to feel that evaluate is unneccessary. I am however having trouble finding anything comprehensive on how to eliminate evaluate from my application. Iam only using it in a few places but would like to know the alternatives (any useful links would be appreciated).

Here is an example of where Iam using evaluate:

<cfoutput query="housing_type">
   <tr>
    <td class="tblcell">#HousingTypeName#</td>
    <td class="tblcell">#Evaluate(HousingIdentifier).Time_Spent#</td>
    <td class="tblcell">#Evaluate(HousingIdentifier).Records#</td>
   </tr>
  </cfoutput>

So Iam using the value returned from a query to access another query identified by the identifier grabbed from <cfoutput query="housing_type">
or basically housing_type.HousingIdentifier.

And then from the second query Iam grabbing the .Time_Spent value.

any pointers on how I can eliminate evaluate from this.

Cheers

[-__cfSearching__- · Accepted solution]

 #Evaluate(HousingIdentifier).Time_Spent#

To answer your question, in most cases, un-scoped variables are placed the VARIABLES scope. It is a structure (mostly) like the FORM and URL scopes. So variables within this scope can be accessed using array notation.

ie cfset value = variables["someVariableName"]

Query objects can also be accessed using array notation:

ie cfset value = queryName["columnName"][rowNumber]

So in your case, you could combine the two concepts to access the query objects dynamically.

ie

#variables["theQueryName"]["columnName"][rowNumber]# ... or ...

#variables[HousingIdentifier]["Time_Spent"][1]#

Though, I am curious about why so many queries ..

[Guest]

Thanks for the response.

The reason for so many queries is that Iam outputing a range of values from another range of values. As the originating range of values can change then it needs to dynamically update itself depending on the orginating range of values.

Hope that makes sense.

Cheers again

[Adam Cameron.]

The reason for so many queries is that Iam outputing a range of values from another range of values. As the originating range of values can change then it needs to dynamically update itself depending on the orginating range of values.

Can you post the SQL for the queries contributing to all this?  And I guess the CF logic that's linking them together.  It's pretty rare that the logic linking queries together on the CF end of things can't be pushed back into the DB (which, really, it belongs).  I mean... if you want to look at that side of things, I mean.  I realise it's not really why you posted in the first place.

--

Adam

[Guest]

<cfquery name="housing_type"  datasource="#application.datasource#" username="#application.username#" password="#application.password#">

  SELECT *
  FROM tblHousingType
  WHERE HousingTypeID > 1
</cfquery>

<!--- Loop through each of the relevant housing types --->
<cfoutput query="housing_type">
  <cfquery name="#HousingIdentifier#"  datasource="#application.datasource#" username="#application.username#" password="#application.password#">
  SELECT SUM(TimeSpent) AS Time_Spent, COUNT(*) AS Records
  FROM tblReflectionNote
  WHERE memberID = <cfqueryparam cfsqltype="CF_SQL_INTEGER" value="#attributes.memberID#">
  AND HousingType = #HousingTypeID#
  AND RecordDate BETWEEN <cfqueryparam cfsqltype="CF_SQL_DATE" value="#attributes.StartDate#"> AND <cfqueryparam cfsqltype="CF_SQL_DATE" value="#attributes.EndDate#">
  </cfquery>
</cfoutput>

Here you go

[Adam Cameron.]

Is the first query used for anything other than contributing to the filter on the second one?  It seems to me like you're doing a SELECT * when you're only using the HousingTypeID column (which is less than ideal in itself).

Why don't you simply join the two tables together in the second query, and group by that ID?

Also: don't forget to parameterise your reference to HousingTypeID, rather than hardcoding it into the SQL string.

--

Adam

[Dan_Bracuk]

Regarding:

Why don't you simply join the two tables together in the second query, and group by that ID?

If you don't know how to do that, I've heard good things about the book, Teach Yourself SQL in 10 Minutes by Ben Forta.