Rip's Domain

QoQs: share your tricks!

Posted in ColdFusion by rip747 on July 12, 2006

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.

7 Responses

Subscribe to comments with RSS.

  1. barry.b said, on July 13, 2006 at 1:42 am

    the heaviest use of Q’o’Q I ever did was to use it as an in memory database – the data “guts” of a server-scoped CFC

    the benefits were that simple SQL could be used and it could be re-queried as many times as you liked.

    some tricks:

    to remove particular records/rows: do a query where it excludes (eg: “not in…”) the records you want to get rid of. the resulting query is your new Q’o’Q to play with.

    another is that you can use another query to join to the Q’o’Q in the SQL. Use Q’o’Q to query across recordsets (I’m getting tired of writing “query”

    what are some improvements to Q’o’Q people think are needed? I’d like to see a few more (ANSI) SQL constructs available…

    eh, my 2c

  2. bob said, on July 13, 2006 at 5:02 am

    If you want a sum why not just do:

  3. bob said, on July 13, 2006 at 5:03 am

    If you want a sum why not just do:

  4. bob said, on July 13, 2006 at 5:04 am

    can’t seem to add code in a comment, look at using ArraySum()

  5. Pete said, on July 13, 2006 at 7:36 am

    I’ve frequently used QofQ’s and query caching to make dynamic column sorts (clickable column headers that sort ascending or descending) run faster. Cache the main query, then use a QofQ with a dynamic ORDER BY statement.

    Of course, there’s always joining data from two different datasources, too. I’ve used that only rarely, but it’s been a lifesaver when linking databases isn’t an option.

  6. […] There have been a couple of posts on QofQ tricks recently and this is what I have done for a client site recently. Instead of using QofQ’s for a few reports I created a couple of Sql Server VIEWS. The VIEWS had all the table joins and filtering I needed. After that I just used normal cfquery against the VIEW. This method gave me tremendous flexibility and power to generate dynamic reports. What are your thoughts on this method? […]

  7. Shawn C said, on August 16, 2006 at 6:09 pm

    I have used session-cached queries a lot with my Ajax-enabled pages. I have a couple of pages where I have dynamic sorting / paging / filtering. It’s much easier to grab a user’s main query results, store it at a session level, and then sort it or filter it dynamically in memory.

    Of course, you need to be disciplined about this…so do it smartly.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: