Query of Queries is Inconsistent (Adobe CFML Runtime)

I've never been a big fan of a half baked solution, but I will acknowledge that coldfusion's query of queries has come a long way from it's beginning and does do a good job on simple queries to get data from an existing query.

However, there are times when it simply sucks at doing what you think it should do, especially when it comes to multiple tables and joins, as well as converting datatypes to be alike.

I'll show a few examples of it's inconsistencies and attempt to point out some problems with it's implementation.

Example 1: Q-of-Q (Query of Queries) and Proper variable scoping inside a cffunction.

When inside a cffunction you always want to fully qualify your variables as best practice. If it's an arguments, prepend the value with 'arguments.' etc. The same goes for Q-of-Q. But I will show you where q-of-q forces you to break it's own best practice advice by not allowing you to do this simple task.

I will be referencing the following 2 queries:

<!--- Test Query 1 --->
<cfset q = QueryNew('id,name','Integer,varchar')>
<cfset queryaddrow(q,1) >
<cfset querysetCell(q,'id',1,1) >
<cfset querysetcell(q,'name','test',1) >

<!--- Test Query 2 --->
<cfset q2 = QueryNew('id,age','Integer,Integer')>
<cfset queryAddRow(q2,1) >
<cfset querySetCell(q2,'id',1,1) >
<cfset querySetCell(q2,'age',33,1) >

This is a simple query that creates a single row of data




What I'm going to do is merge the results of these 2 queries (as one may have come from a cfdirectory, and the other from a cfsearch, or cfquery etc).

My method takes 2 query arguments and joins them - but for now I'll just return the value of the 1st argument 'qry'.

<cffunction name="merge" output="false" access="public" returntype="query" hint="">
<cfargument name="qry" type="query" required="true"/>
<cfargument name="qry2" type="query" required="true"/>
<cfset var Private=StructNew()>

<cfquery name="private.q" dbtype="query">
select *
from arguments.qry
</cfquery>

<cfreturn private.q />
</cffunction>

Resulting in a simple query returned - nothing special.



Now when i wish to merge the 2 queries - I would assume that I could do something like this then if 'arguments.qry' worked to reference the qry argument.

<cffunction name="merge" output="false" access="public" returntype="query" hint="">
<cfargument name="qry" type="query" required="true"/>
<cfargument name="qry2" type="query" required="true"/>
<cfset var Private=StructNew()>

<cfquery name="private.q" dbtype="query">
select *
from arguments.qry, arguments.qry2
where arguments.qry.id = arguments.qry2.id
</cfquery>

<cfreturn private.q />
</cffunction>

WRONG!
Query Of Queries syntax error.
Encountered "arguments . qry .. Incorrect conditional expression, Expected one of [like|null|between|in|comparison] condition,

Ok fine - I won't use proper best practices and just reference the query w/o the arguments proper fully scoped value.

<cffunction name="merge" output="false" access="public" returntype="query" hint="">
<cfargument name="qry" type="query" required="true"/>
<cfargument name="qry2" type="query" required="true"/>
<cfset var Private=StructNew()>

<cfquery name="private.q" dbtype="query">
select *
from qry, qry2
where qry.id= qry2.id
</cfquery>

<cfreturn private.q />
</cffunction>

Results: works fine



Example 2: Using QofQ (query of queries) to reference a column from the table using dot notation

The ColdFusion LiveDocs states the following:

Example

If a structure named A contains a field named B, which contains a table named Products, you can refer to the table with dot notation, as follows:

SELECT tape_ID, length
FROM A.B.Products;

The 'structure' in my case is 'arguments', the field name is 'id', and the table name is 'qry'. With that in mind:

A=arguments
B=id
Products=qry

Therefore you could deduce that the following is true:

SELECT id, name
FROM arguments.id.qry

Results:

Query Of Queries syntax error.
Encountered ". Incorrect Select List, Incorrect select column, arguments.id cannot be followed by '.'


However, you would normally reference this data as 'arguments.qry.id'.

Results:

Query Of Queries syntax error.
Encountered ". Incorrect Select List, Incorrect select column, arguments.qry cannot be followed by '.'

The kicker is - Neither of these work.