ColdFusion’s Query of Queries is a fantastic tool. It isn’t nearly as fast as running a query on a database directly, but it serves a very useful purpose. More often than I’d care to remember, there’s a need for combining a query set from multiple datasources that have no way to talk to each other. There’s ways to get around this, of course, but QoQ makes things just so much simpler.

Except, when it isn’t so simple.

(Sigh)

Anyways, I had a project in which I needed to use QoQ, and it happened to be within a CFC function. I try to always use locally scoped variables when possible, so, I ran into a silly problem: How to use QoQ with a local scoped query. Because, I can’t just use “local.queryName” in the query name – it throws an error.

Second, this QoQ needed to combine datasets that did not exactly match up. For example, 2 of the 3 queries that I was combining did not have a “description” field. QoQ acts funny sometimes when you try to hard-code a value – it seems to guess what the datatype should be, and oftentimes gets it wrong. You’ll then get an error about a datatype mismatch. So, I needed to be able to force casting of variable types.

For problem #1, Ben Nadel’s blog has a perfect solution. Use [local].queryName instead of local.queryName.

For the second issue, Ben Nadel to the rescue again!

---

Final code:

	<cffunction name="getCalendarData" access="public" returntype="any" hint="" output="false"> 
		<cfargument name="startDay" type="date" required="no" default="#CreateDate(Year(Now()),Month(Now()),1)#">
		<cfargument name="endDay" type="date" required="no" default="#CreateDate(Year(Now()),Month(Now()),DaysInMonth(Now()))#">
		
		<cfset var local={}>
		
		<!--- get CALENDAR dates --->
		<cfset local.calendarData    = getEventsForDateRange(arguments.startDay,arguments.endDay)>
		<!--- get ANNOUCEMENT dates --->
		<cfset local.annoucementData = getCalendarAnnoucementsForDateRange(arguments.startDay,arguments.endDay)>
		<!--- get Meetings dates --->
		<cfset local.meetingsData    = getMeetingsForDateRange(arguments.startDay,arguments.endDay)>
		
		<!--- and now, we merge! --->
		<cfquery dbtype="query" name="local.allCalendarData">
			select CAST(title as VARCHAR), 
			       start_date, end_date, 
			       CAST(link as VARCHAR), 
			       CAST(description as VARCHAR), 'C' as data_type
			from [local].calendarData
			UNION
			select CAST(page_title as VARCHAR) as title, 
			       page_announcement_start_date as start_date,
			       page_announcement_end_date as end_date, 
			       CAST(announcement_url as VARCHAR) as link, 
			       CAST('' as VARCHAR) as description, 'A' as data_type
			from [local].annoucementData
			UNION
			select CAST(event_title as VARCHAR) as title, 
			       event_start_date as start_date, event_end_date as end_date, 
			       CAST(event_url as VARCHAR) as link, 
			       CAST('' as VARCHAR) as description, 'M' as data_type
			from [local].meetingsData
			order by start_date, title
		</cfquery>

		<cfreturn local.allCalendarData>
	</cffunction>