QoQs: share your tricks!

UPDATE: I keep getting hits on the post, but noone has shared a trick since August. Do me and the rest of the CF community a favor and share something, will ya! BTW, at the time of this update, I’m drinking vodka and Gator Aid (go blue!)  so I’m a little tipsy. Which might be why I’m typing this 😛

I know this won’t be the most advanced topic for most CF developers out there, but we have to remember that there are some people who are just starting out using CF and by sharing some of the tricks we know will help those people learn faster. With that in mind I present some basic, yet helpful tricks on using QoQs.

A little intro:

One of the best things to happened to CF was the introduction of QoQs in CF5. Using QoQs can save yourself some pretty good amount of code and increase the performance of your application. Let’s look at some ways to do this.

First off: Grabbing the total.

We’ve all programmed reports in our careers. Usually this involves running a query to return a bunch of records and displaying the results in a table, nothing big. For an added bonus we display the total of all the records in the last row of the table, again nothing big and complex. So how would you get the total for the records in the query?

Some popular ways were to query your database again and do a SUM() on the columns that you wanted the totals for. Not bad, but why hit the database again for something like this. You could do the old adding the total to a variable while outputting the query. However this is tedious and prone to errors especially when you want to total a bunch of columns. An easy way is to just use QoQs for the job. Left’s take a look:

<cfscript>
// setting up the test query
query1 = QueryNew("customer,ordertotal");
QueryAddRow(query1, 10);
for(i=1; i LTE 10; i=i+1)
{
	name = "Tony";
	if(bitand(i, 1)){name = "Charles";}
	QuerySetCell(query1, "customer", name, i);
	QuerySetCell(query1, "ordertotal", RandRange(50, 100), i);

}
</cfscript>
<cfdump var="#query1#">
<!--- use QoQ to grab the total for each customer --->
<cfquery dbtype="query" name="querytotal">
SELECT
customer,
SUM(ordertotal) AS total
FROM query1
GROUP BY customer
</cfquery>
<cfdump var="#querytotal#">

This is an extremely basic example, but it does show the point. By using QoQ you can avoid hitting your database again and grab the totals in an easy to read and use manner.

Next Up: Query Caching.

Again this doesn’t have a huge WOW factor. I think everyone has use the “cachedwithin” attribute of the cfquery tag at some point. The only thing I can’t stand about using “cachedwithin” is that CF will cache a copy of the query for each statement. Another gotcha is that you can’t use cfqueryparam when using cachedwithin, which should be used in every query.

So how can QoQ help us out? By using the application or session scopes to store the query into memory, we can use QoQ to hit this query and pull the information that we want. The example below shows how to do this by loading the query into the application scope and then performing two separate QoQs again the query in memory. Notice that now we are able to use cfqueryparam. Now I will say that you wouldn’t want to do this on a huge table, but for a table that’s is 5000 rows or less and gets hit frequently, this could help take some load of you database server.

<cfif NOT StructKeyExists(application, "testquery")>
<p>Loading query into memory</p>
<cfscript>
application.testquery = QueryNew("customer,ordertotal");
QueryAddRow(application.testquery, 10);
for(i=1; i LTE 10; i=i+1)
{
	name = "Tony";
	if(bitand(i, 1)){name = "Charles";}
	QuerySetCell(application.testquery, "customer", name, i);
	QuerySetCell(application.testquery, "ordertotal", RandRange(50, 100), i);
}
</cfscript>
</cfif>
<cfdump var="#application.testquery#">
<cfquery dbtype="query" name="query1">
SELECT *
FROM application.testquery
WHERE customer = <cfqueryparam cfsqltype="cf_sql_varchar" value="Tony">
</cfquery>
<cfdump var="#query1#">
<cfquery dbtype="query" name="query2">
SELECT *
FROM application.testquery
WHERE customer = <cfqueryparam cfsqltype="cf_sql_varchar" value="Charles">
</cfquery>
<cfdump var="#query2#">
<!--- Uncomment the line below to remove the query from memory --->
<!---<cfset StructDelete(application, "testquery")>--->

Now that I’ve shared some of my stupid tricks with QoQs, is there anything else that people are using them for that they would like to share? Let me know and I’ll update this post with your tricks.