LOCAL Variables Scope Conflicts With ColdFusion Query of Queries

Posted June 22, 2006 at 2:29 PM by Ben Nadel

Tags: ColdFusion, SQL

Reader Comments

You did NOT just save me hours of grief. :-) found your blog post on google and wanted to say thanks. I always ended up var'ing another variable to use in my QofQ. Thanks for the insight!

Tim

Tim,

No worries dude. Please let me know if there IS anything I can do to save you hours of grief?

Using structure in Query of queries is buggy to say the least:
Using your example as a reference.. the following fails. I can't find anyway in which to get this to work, so must be a bug

<cfscript>
var LOCAL = structnew();
LOCAL.Data = QueryNew( "id,name" );
LOCAL.Data2 = QueryNew( "id,name" );
LOCAL.Test = StructNew();
</cfscript>

<cfquery name="qTest" dbtype="query">
SELECT
id,
name
FROM
[LOCAL].Data ,[LOCAL].Data2
where
[LOCAL].Data.id =1
</cfquery>

Phil,

The problem is in the WHERE clause. For some reason, it cannot handle the [LOCAL].Data.id line. However, if you take that away, and just have id = 1, it will parse correctly. However, it then throws an ambiguous column name (since neither ID nor NAME are prefixed and are available in both join tables).

And since you can't alias table names, you might be out of luck with this example. Sorry.

would you guys please reduce using query of queries so much, and perhaps put a bit more time into sharpenning your sql join skills.

@Anonymous,

Can you point out a place where you feel that a JOIN would be more appropriate than a query of queries?

I ran into this issue recently and decided the quick fix at the time was to simply rename my LOCAL variable to LocalScope. Renaming LOCAL to fix a QoQ seemed odd to me at the time and it has been bugging me because I have not had the time to work on it.

I was about to ask you this very question, but I tried a quick search and there was the answer on the first result. I should have tried that earlier...

It's nice to be able to keep using LOCAL without worrying about query of queries inside functions. =)

I love the idea of scoping all variables in a function inside a single "var" struct. However, if there's an evolving standard, then using a variable name without a naming conflict makes far more sense. If the name "local" creates a conflict in QoQ, why not change the variable name, not use a workaround.

The first two that sprung to mind were "func" and "my". They're both descriptive (enough) and they're short, which is nice considering the scope is prepended to all the variables local to the function.

Any thoughts on this? I guess it just bothers me that the "accepted" practice is one that includes an unnecessary bump in the road.

@Adam,

At CFUNITED 2008, Adam Lehman actually said that in ColdFusion 9, functions would have an explicit LOCAL scope. So, it looks like they are endorsing this idea of "local" even though it causes issues in Query of Queries.

What about the highly descriptive term localVar? It's only eight characters, 1 more than REQUEST, 1 less than VARIABLES and 3 less than APPLICATION

<cfset var localVar = structNew()>

Since query of queries is so limited with functionallity and it takes a performance hit on your page, I opted to exclude it from my coding.
Instead sharpenning up my SQL has really taken away the need for QoQ. For example, get the rows and columns you'd like them on the first call to the SQL server. I know that is a long term fix but it's better in the long run.

@Webexpertise,

Query of queries definitely do NOT run as fast as native SQL calls to the database. However, they can work quite synthetically. For example, let's say you have to return a report of Students and last 10 test scores. To do this you could start out:

- Return all students
- Return all recent test scores

Then, loop over the students and for each students, do a query of queries on the recent test scores to get the test scores for that given student.

This allows us to leverage the goodness of SQL when necessary, but we can then use query of queries to leverage the existing query in the page's memory space.

Point taken, but in that case I would do one single SQL statment:

select top 50 student, score
from studentscores
order by id desc

that will give me a list of 50 students ordered from newest to lowest.

so then I can show them like this:
<cfoutput>

<cfif currentrecord lte 10>
<li><br>#student# #Score#</br></li>
<cfelse>
<li>#student# #Score#</li>
</cfoutput>

This will bold the top 10 last scores as well as give you the full list. And i didn't need the QoQ. and only one call to sql.

----

On the other hand if I wanted to add sorting to that SQL result by the provided columns in the query, it does make sense to use QoQ without a trip to the sql server.

Sorry but looping over a cfquery with a cfloop and on each iteration calling a query of queries to calculate the students test score looks like more processing on my server. And it looks like a workaround for something. Not to mention that if your original query was a large amount of rows it really takes a hit on performance.

One could then say: if performance is not at question then use QoQ.

I used to use QoQ a lot, then I was asked to do really complex reports requiring calculations based on many tables is when I realized that spending my time learning to make QoQ work for me was not as efficient time spent than learning sql to get the same results. (and faster)
But that's just my opinion. :)

@Webexpertise,

The problem with that way is that in many reports that I have to run, I am getting a lot of additional data such as school contact information and district information and principal information. With too many joins, there gets to be a lot of data pulled down for each record that is not necessarily worth the transfer time.

I am not saying one way is better - I think its more of an art than a science to figuring out which way will be faster.

Most of where I use this is in reporting where time of execution is not necessarily mission critical.

Ben,

Art vs. science, interesting comparison it reminds me of MAC vs PC.

I know you are a master at coldfusion, I do really appreciate all the articles you have ever provided, in many cases it has saved my week from falling short and I love your site for that. But let me quote you with all due respect on this: "With too many joins, there gets to be a lot of data pulled down for each record" I don't exactly see it that way, what I would do in that case is:

select top 50 students.firstname,students.lastname, school.name,school.location,ss.score
from students
join school on student.schoolid=school.id
join studentscores ss
order by ss.id desc

That outputs only 5 columns and 50 rows. The important note is not to use "select * from" when using joins for multiple tables therefore avoiding too much data out.

Usually what I do is create a function in a component i.e. students.cfc that returns this data in a query ready to use for any template or component. Having that in a component makes it even faster since it is already compiled code.

One good scenario where you'd be better off using joins and more specific SQL vs. QoQ is lets say you land on a job that used to have a full department of IT employees, such as a DBA and a couple of CF developers but now for some strange reason, perhaps the company was in financial trouble, now they only have you to do minor maintenance and some IT support. You will find out that a lot of the tables in SQL are normalized with 1NF or 2NF or so that is the job of the DBA, it will be very intuitive for someone who has been using joins to get what they need in their queries, vs. the programmer who relies in QoQ for everything, and will have a nightmare trying to match id's of the perhaps 4 tables needed to be called for a normalized database in any given report.

@Webexpertise,

I am not sure where you are getting this "top 50" from? This is a report - it might have thousands of students that need to have scores returned. Sure it might only return 5 columns and 50 rows, but your SQL does not address the actual gesture of the sample report.

top 50 was just an example, usually I limit the amount of rows returned by my sql, with a variable that works similar to "maxorws" Its a scary thing not to do so, I hate to see cfml templates spinning the cursor, going back to the QoQ vs. SQL I ran into this post in the IRC channel.
http://www.cfcode.net/blog/post.cfm/sql-fun-let-the-db-do-the-work

@Webexpertise,

Using a limit or top on the SQL is nice; however, when you are reporting, this is generally not possible unless the report concerns a TOP X percentage of some sort. Sometimes, a client just wants to see all 8 thousands records :(

True, but you got to limit it somewhere, what if its 700 billion rows in that report. You could cover the world on paper a few times, we will freeze to death from lack of heat from the sun catastrophic loss of species while making the world a lonelier and less interesting place. I would think twice before coding that report.

@Webexpertise,

Trust me, I have already campaigned heavily *against* the reports that they want now.... I think we're on the same page :)

Hoaah :) you saved me once again, really thanks

@Daniel,

Awwww yeah :)

I had a similar problem back in 07, not quite the same result, but the same cause. Here is my scenario in case it helps anyone.

http://russ.michaels.me.uk/index.cfm/2007/7/3/Query-of-Queries-quirks

@Russ,

It looks like you are using "locals" in your example, rather than "local", to get around the reserved word issue, correct?

I think some of the issues you were experiencing with the comparison operators been since been resolved, thankfully!

Hey Ben,

I recently started "following" you on Twitter. This blog finally helped me answer my question of why I always saw your "local" structs in other blogs (although I guess I could have easily figured the reason behind it). That struct makes sense, and you are less likely to need one of those "var" validator tools because there is less var variables to maintain.

I was wondering why my comment was deleted? I posted a very elegant solution to the problem allowing local variables to be declared anywhere in a CF8 function using syntax similar to the var declaration. Was it because I expounded on it's usefulness, giving multiple examples?

I think this is very important as it is not documented anywhere on the web. It took me hours to work out the solution myself.

Please don't delete it, so others can benefit. I've removed the extraneous examples and include only the function and sample usage.

The function:

Usage:

@Jdyer,

Sorry if it was deleted. Some comments get flagged as spam based on a lot of different criteria (name, link, comment content, relevancy, etc.) - there are definitely false positives.

This is actually a very interesting approach. I never thought about toying with the active local scopes. I can actually see this being useful in other situations (such as when dealing with threads).

One thing I've noticed when playing around with active function local scopes and popping them is that it can mess with the stack trace.

If an error occurs or if you purposely throw an error and you have popped function local scopes (in other words, it happens before you have restored the function local scope) then the stack trace gets screwed up indicating the error occurred on the line of the top level function call. To illustrate:

To fix this, you should set a flag (or a counting semaphore if you plan recursion) in a shared scope such as Request so in the event of an error you can push some new FLSs to prevent this problem.

Here is an update of the function with a counting semaphore in the Request scope and an error thrown if somevar is not a string.

Here is the throw function so you can see how to correct the problem.

If anyone needs the getCallerStack function to make this all work, just say so and I'll add it to the conversation.

If you are trying to debug some code and are not throwing your own error, then wrap this around code you suspect of creating the error.

You could also put the if and while loop in a function called restoreAllFunctionLocalScopes for convenience.

I never cease to be amazed that almost EVERY random CF issue I come across lands me on your site.
Thank you for documenting your findings for the world.

@Curt Self - Yup... you and the rest of the CF community. Adobe should be paying Ben, I reckon there'd be a lot less CF websites out there without him.

@Ben - Thanks for solving yet another issue. Previously I've been copying a Local variable to an unscoped variable in order to use in QOQ, which just seems ugly and inelegant.

@Curt, @Gary,

My pleasure, fellas!

Post A Comment

Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Supported HTML tags for formatting: <strong>bold</strong>   <em>italic</em>   <code>code</code>
	Remember my information
	Subscribe to comments
	Send me a copy of this comment