LOCAL Variables Scope Conflicts With ColdFusion Query of Queries
Posted June 22, 2006 at 2:29 PM
I just helped someone on the House of Fusion CF-Talk list who was having trouble with his ColdFusion query of queries. Like some of us do, he was declaring a local variable, LOCAL, in a function and then declaring variables within it. This is done so that only one variable (LOCAL) has to be "var'ed" to enable many variables to be considered local to the function's memory space:
Launch code in new window » Download code as text file »
- // Declare local scope.
- var LOCAL = StructNew();
-
- // These are now all local.
- LOCAL.Foo = 1;
- LOCAL.Bar = QueryNew( "id" );
- LOCAL.Test = StructNew();
His problem was that he was doing a query of queries using a query stored in the LOCAL pseudo scope:
Launch code in new window » Download code as text file »
- <cfquery name="qTest" dbtype="query">
- SELECT
- id,
- name
- FROM
- LOCAL.Data
- </cfquery>
This throws the error:
Query Of Queries syntax error.
Encountered "LOCAL.
This can be frustrating because if you change the name LOCAL to something like LOCAL2, it works just fine. The problem, while frustrating, has a very simple answer: LOCAL is a reserved word in SQL and ColdFusion Query of Query SQL. The solution: use the [ ] notation:
Launch code in new window » Download code as text file »
- <cfquery name="qTest" dbtype="query">
- SELECT
- id,
- name
- FROM
- [LOCAL].Data
- </cfquery>
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Newer Post
ColdFusion & AJAX: Converting ColdFusion Objects to Javascript Objects
Older Post
ColdFusion Error: The Column Name "3a" Is Invalid
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 :)
