LOCAL Variables Scope Conflicts With ColdFusion Query of Queries

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

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>

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

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.

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.

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. :)

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.

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

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.

Hoaah :) you saved me once again, really thanks

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

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:

function var(somevar)
{
	var oldlocal="";
	getPageContext().popFunctionLocalScope();//Get rid of current local scope.
	oldlocal=getPageContext().getActiveFunctionLocalScope();//Get old local scope.
	if (ArrayLen(Arguments) GT 1)
	{//Inject with value passed in.
		oldlocal["#somevar#"]=Arguments[2];
	}
	else
	{//Inject with default of empty string.
		oldlocal["#somevar#"]="";	}
	getPageContext().pushNewFunctionLocalScope();//Put a local scope for CF to pop when this function exits.
}

Usage:

function somefunc()
{
	var("test");//Defaults to empty string.
	var("test1",1);//Assign simple value.
	for (var("i",1); i LTE maxval; i=i+1)
	{
		dosomething();
	}
}

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:

function somefunc()
{
	someotherfunc();
}
function someotherfunc()
{
	getPageContext().popFunctionLocalScope();
	//error happens here
	getPageContext().pushNewFunctionLocalScope();
}
somefunc();//Error will indicate this line.

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.

//Allows creation of a local variable anywhere in a function.
function var(somevar)
{
	var oldlocal="";
	getPageContext().popFunctionLocalScope();//Get rid of new local scope.
	if (IsDefined("Request.poppedFLSCount"))
	{	//We have to keep track of how many are popped in case of errors.
		++Request.poppedFLSCount;
	}
	else
	{
		Request.poppedFLSCount=1;
	}
	oldlocal=getPageContext().getActiveFunctionLocalScope();//Get old (now current) local scope.
	if (IsValid("string",somevar))
	{
		if (ArrayLen(Arguments) GT 1)
		{
			oldlocal["#somevar#"]=Arguments[2];//Create new local variable with passed in value.
		}
		else
		{
			oldlocal["#somevar#"]="";//Create new local variable with default of an empty string.
		}
	}
	else
	{
		throw(type="invalidParameter",message="Invalid Parameter",detail="The parameter somevar must be a string containing the name of the variable to be locally scoped.");
	}
	getPageContext().pushNewFunctionLocalScope();//Push another local scope for CF to pop.
	--Request.poppedFLSCount;//Reduce count as we have created a new one.
}

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

<cffunction name="throw" output="false" returnType="void" hint="CFML Throw wrapper">
	<cfargument name="file" type="string" required="false" default="Unknown" hint="File throw is located in.">
	<cfargument name="line" type="string" required="false" default="Unknown" hint="Line throw is located on.">
	<cfargument name="func" type="string" required="false" default="Unknown" hint="Function throw is located in.">
	<cfargument name="type" type="string" required="false" default="Application" hint="Type for Exception">
	<cfargument name="message" type="string" required="false" default="" hint="Message for Exception">
	<cfargument name="detail" type="string" required="false" default="" hint="Detail for Exception">
	<cfargument name="errorCode" type="string" required="false" default="" hint="Error Code for Exception">
	<cfargument name="extendedInfo" type="string" required="false" default="" hint="Extended Info for Exception">
	<cfargument name="object" type="any" hint="Object for Exception">
		<cfif IsDefined("Request.poppedFLSCount")>
			<!--- We have to restore these for the stack trace. --->
			<cfloop condition="Request.poppedFLSCount GT 0">
				<cfset getPageContext().pushNewFunctionLocalScope()>
				<cfset Request.poppedFLSCount=Request.poppedFLSCount-1>
			</cfloop>
		</cfif>
		<cfset var("functxt")>
		<cfif file EQ "Unknown" OR line EQ "Unknown" OR func EQ "Unknown">
			<cfif type NEQ "getStackTrace">
				<cfset caller=GetCallerStack()>
				<cfif ArrayLen(caller) GTE 1>
					<cfset file=caller[1]["template"]>
					<cfset line=caller[1]["line"]>
					<cfset functxt='in function #caller[1]["function"]# '>
				</cfif>
				<cfif file EQ "Unknown"><!--- Was thrown from outside a function. --->
					<cfset caller=GetErrorLineAndFile()>
					<cfset file=caller["template"]>
					<cfset line=caller["line"]>
				</cfif>
			</cfif>
		</cfif>
		<cfif not isDefined("object")>
			<cfthrow type="#type#" message="Error: #type#<BR/><BR/>#message#" detail="#detail#<BR/>Error occured on line #line# #functxt#of #file#" errorCode="#errorCode#" extendedInfo="#extendedInfo#">
		<cfelse>
			<cfthrow object="#object#">
		</cfif>
</cffunction>

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.

try
{
	//your code
}
catch(Any excpt)
{
	if (Is(Defined(Request.poppedFLSCount))
	{
		while (Request.poppedFLSCount GT 0)
		{
			getPageContext().pushNewFunctionLocalScope();
			--Request.poppedFLSCount;
		}
	}
	rethrow();//wrapper for cfrethrow tag
}

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.