ColdFusion QueryAppend( qOne, qTwo )
Someone was asking me about a QueryAppend() method in ColdFusion. So this morning, I finally got around to writing a snippet demo about it. The way I figured it out, there are two different ways to append one query to another in ColdFusion. In the first method, QueryAppend( qOne, qTwo ), we are using the power of query of queries to union both queries together and return the resultant query. In the second method, QueryAppend2( qOne, qTwo ), we are looping through the second query and manually adding rows to the first query. As you can see from the results below, the second method (manually adding rows to the query) consistently performs much faster than the UNION ALL method.
Method One: Query of Queries
<cffunction name="QueryAppend" access="public" returntype="query" output="false"
hint="This takes two queries and appends the second one to the first one. Returns the resultant third query.">
<!--- Define arguments. --->
<cfargument name="QueryOne" type="query" required="true" />
<cfargument name="QueryTwo" type="query" required="true" />
<cfargument name="UnionAll" type="boolean" required="false" default="true" />
<!--- Define the local scope. --->
<cfset var LOCAL = StructNew() />
<!--- Append the second to the first. Do this by unioning the two queries. --->
<cfquery name="LOCAL.NewQuery" dbtype="query">
<!--- Select all from the first query. --->
(
SELECT
*
FROM
ARGUMENTS.QueryOne
)
<!--- Union the two queries together. --->
UNION
<!---
Check to see if we are going to care about duplicates. If we don't
expect duplicates then just union all.
--->
<cfif ARGUMENTS.UnionAll>
ALL
</cfif>
<!--- Select all from the second query. --->
(
SELECT
*
FROM
ARGUMENTS.QueryTwo
)
</cfquery>
<!--- Return the new query. --->
<cfreturn LOCAL.NewQuery />
</cffunction>
Method Two: Manually Adding Rows
<cffunction name="QueryAppend2" access="public" returntype="void" output="false"
hint="This takes two queries and appends the second one to the first one. This actually updates the first query and does not return anything.">
<!--- Define arguments. --->
<cfargument name="QueryOne" type="query" required="true" />
<cfargument name="QueryTwo" type="query" required="true" />
<!--- Define the local scope. --->
<cfset var LOCAL = StructNew() />
<!--- Get the column list (as an array for faster access. --->
<cfset LOCAL.Columns = ListToArray( ARGUMENTS.QueryTwo.ColumnList ) />
<!--- Loop over the second query. --->
<cfloop query="ARGUMENTS.QueryTwo">
<!--- Add a row to the first query. --->
<cfset QueryAddRow( ARGUMENTS.QueryOne ) />
<!--- Loop over the columns. --->
<cfloop index="LOCAL.Column" from="1" to="#ArrayLen( LOCAL.Columns )#" step="1">
<!--- Get the column name for easy access. --->
<cfset LOCAL.ColumnName = LOCAL.Columns[ LOCAL.Column ] />
<!--- Set the column value in the newly created row. --->
<cfset ARGUMENTS.QueryOne[ LOCAL.ColumnName ][ ARGUMENTS.QueryOne.RecordCount ] = ARGUMENTS.QueryTwo[ LOCAL.ColumnName ][ ARGUMENTS.QueryTwo.CurrentRow ] />
</cfloop>
</cfloop>
<!--- Return out. --->
<cfreturn />
</cffunction>
Let's explore the pros and cons of the first method.
Cons: This method performs slower for several reasons. For one, it has to evaluate SQL statements. Even though this is on the ColdFusion side, it's still a good amount of overhead. Also, due to the fact that we are unioning we have to read every row from both the first query and the second query even though we are only appending the second query.
Pros: The only real pro to this method is that we can determine wether or not we get duplicate rows back via the ALL directive in the UNION clause. This is something that cannot be done efficiently in the second method (see below).
Let's explore the pros and cons of the second method.
Cons: The main con for this method is that we are not checking for any duplicate rows. We simply append the rows from the second query to the first.
Pros: The main pro here is that its wicked fast! Much faster in fact than the first method. This will always be the case. Additionally, this behaves more like the other Append methods in ColdFusion. In StructAppend() and ArrayAppend(), you don't get a returned result object. The first object passed in is altered by reference, and therefore, no result needs to be passed back. This method of the QueryAppend() works that way, by altering the first query directly, not needing to pass anything back.
Let's explore the speed difference, as the second method will ALWAYS be faster no matter how many rows we are dealing with. Think about a situation in which you have one query that has X number of rows and you want to union it with another query which has Y number of rows.
Method One: Reads in X + Y rows in the SELECT statements of the query. Then, it writes X + Y rows back into a resultant query. That's (2X) + (2Y) read/write operations (give or take).
Method Two: Reads in just from the second query Y rows. It then appends those Y rows to the first query with Y writes. That's (2Y) read/write operations(give or take). Therefore, the second method will always be a factor of (2X) faster than the first method since it does not care about the first query in any significant way.
Want to use code from this post? Check out the license.
Reader Comments
I'm trying your method number 2 but CF doesn't like it. Gives me this error on the line where you "set the column value on the newly created row":
An error occurred while trying to modify the query named class coldfusion.sql.QueryTable.
Query objects cannot be modified, they can only be displayed.
@William,
That ColdFusion error usually comes about when you misspell one of the column names. If that happens, then ColdFusion thinks you are trying to add a new column rather than updating an existing old one.
In this case, since you are adding one query to the other, this would probably come about if the two queries do not have the exactly same column names.
Thank you Ben Nadel!
Great article. Method Two does not work when you have duplicate fields (eg disabled, datemodified, etc) when you do joins in the original queries. In SQL you distinguish the fields with the table they belong to. Therefore Method One only works.
@Mike,
Right, but when dealing with an existing query, there is no concept of original table and therefore you should never have duplicate fields (unless your original SQL statement went wrong somewhere).
Query of queries also has this problem. Since query of queries deals with existing queries, you have no concept of originating table in the original queries.
Hey, thanks Ben! Exactly what I needed. You rock!
Excellent method Ben. Just been looking on how to do this and found your methods. Thanks
As ever, exactly what I need just at this moment (you have a great presence on Google!), and a good full explanation to boot. Many thanks
Thanks Ben for this post. It really came in handy today when I was working on a calendar application (using your calendar widget) and I had to append events from one calendar on to a second calendar in a differnet database. I did have to figure out how to invoke the function since I rarely use cfc's
<cfinvoke component="_Query_Append" method="QueryAppend2" QueryOne="#QueryOne#" QueryTwo="#QueryTwo#" returnVariable="result" >
Thank You very much BEN
You rock! Thank you, thank you, thank you!!!
@Heather,
Glad people are still getting value out of this!
I love this, but I can't seem to get it to work within a loop. It will append the query of the first loop, but none of the following iterations.
Thanks for all you do.
@JD,
Never mind.. I just noticed that it IS working most excellently - and FAST, too - but my output somehow is breaking (no error messages.. odd.)
Thanks, again!
Approach #2 is now transplanted into one of our applications. Thanks for experimenting and sharing!
#2 was exactly what I was looking for. Great example and super fast!
Thanks Ben. Option 2 was just what I needed. My situation was to accumulate records by running a series of queries. So it was Q1+Q2+Qn ...
Cheers,
Murray
if u want to return a new query rather than just update the first query:
<cffunction name="QueryAppend" access="public" returntype="Query" output="false">
<cfargument name="QueryOne" type="query" required="true" />
<cfargument name="QueryTwo" type="query" required="true" />
<cfset LOCAL.QueryNew = Duplicate(ARGUMENTS.QueryOne)>
<cfloop query="ARGUMENTS.QueryTwo">
<cfset QueryAddRow( LOCAL.QueryNew ) />
<cfloop index="LOCAL.Column" list="#ARGUMENTS.QueryTwo.ColumnList#">
<cfset QuerySetCell(LOCAL.QueryNew, LOCAL.Column, evaluate("ARGUMENTS.QueryTwo.#LOCAL.Column#"))>
</cfloop>
</cfloop>
<cfreturn LOCAL.QueryNew />
</cffunction>
How to do these same in JS as I want to fetch the html of a site search in that html a specific thing.
Hi,
I am looking to combine two queries by appending the second query to the first (I want to keep the order of the rows with query one coming before query two). Your method two looks like it is exactly what I want. I am just failing to understanding how I can loop through the new query to display the results. I am using cfloop to display my results and don't want to change that. I just want to loop through the two queries combined.
Also, do I have to save the code in a separate file and call it as a function or can I use the code right on the same page after my two queries? If the first, how is that done?
Can someone please help me?
Thanks,
Steve
Hi,
I finally figured it out. I am not familiar with cf functions and didn't realize that you have to wrap them in the cfcomponent tag and invoke it as Bo pointed out previously. Unfortunately after extensive searching of how to use and call cffunctions I found very little that actually talked about this. If there is another way to do this I would be interested in learning about it, but the simple examples all over the internet do not get into this.
Thanks Ben! This function is EXACTLY what I needed.
Steve