{"id":16,"date":"2015-09-16T16:08:01","date_gmt":"2015-09-16T16:08:01","guid":{"rendered":"http:\/\/www.blueradial.com\/blog\/?p=16"},"modified":"2015-09-24T19:42:29","modified_gmt":"2015-09-24T19:42:29","slug":"query-of-queries","status":"publish","type":"post","link":"http:\/\/www.blueradial.com\/blog\/index.php\/2015\/09\/16\/query-of-queries\/","title":{"rendered":"Query of Queries Tricks"},"content":{"rendered":"

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.<\/p>\n

Except, when it isn’t so simple.<\/p>\n

(Sigh)<\/p>\n

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.<\/p>\n

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.<\/p>\n

For problem #1, Ben Nadel’s blog has a perfect solution<\/a>. Use [local].queryName instead of local.queryName.<\/p>\n

For the second issue, Ben Nadel to the rescue again<\/a>!<\/p>\n

(These are both very old posts, but, they are still relevant and useful…)<\/em><\/div>\n
\n

Final code:<\/h3>\n
\r\n\t<cffunction name="getCalendarData" access="public" returntype="any" hint="" output="false"> \r\n\t\t<cfargument name="startDay" type="date" required="no" default="#CreateDate(Year(Now()),Month(Now()),1)#">\r\n\t\t<cfargument name="endDay" type="date" required="no" default="#CreateDate(Year(Now()),Month(Now()),DaysInMonth(Now()))#">\r\n\t\t\r\n\t\t<cfset var local={}>\r\n\t\t\r\n\t\t<!--- get CALENDAR dates --->\r\n\t\t<cfset local.calendarData    = getEventsForDateRange(arguments.startDay,arguments.endDay)>\r\n\t\t<!--- get ANNOUCEMENT dates --->\r\n\t\t<cfset local.annoucementData = getCalendarAnnoucementsForDateRange(arguments.startDay,arguments.endDay)>\r\n\t\t<!--- get Meetings dates --->\r\n\t\t<cfset local.meetingsData    = getMeetingsForDateRange(arguments.startDay,arguments.endDay)>\r\n\t\t\r\n\t\t<!--- and now, we merge! --->\r\n\t\t<cfquery dbtype="query" name="local.allCalendarData">\r\n\t\t\tselect CAST(title as VARCHAR), \r\n\t\t\t       start_date, end_date, \r\n\t\t\t       CAST(link as VARCHAR), \r\n\t\t\t       CAST(description as VARCHAR), 'C' as data_type\r\n\t\t\tfrom [local].calendarData\r\n\t\t\tUNION\r\n\t\t\tselect CAST(page_title as VARCHAR) as title, \r\n\t\t\t       page_announcement_start_date as start_date,\r\n\t\t\t       page_announcement_end_date as end_date, \r\n\t\t\t       CAST(announcement_url as VARCHAR) as link, \r\n\t\t\t       CAST('' as VARCHAR) as description, 'A' as data_type\r\n\t\t\tfrom [local].annoucementData\r\n\t\t\tUNION\r\n\t\t\tselect CAST(event_title as VARCHAR) as title, \r\n\t\t\t       event_start_date as start_date, event_end_date as end_date, \r\n\t\t\t       CAST(event_url as VARCHAR) as link, \r\n\t\t\t       CAST('' as VARCHAR) as description, 'M' as data_type\r\n\t\t\tfrom [local].meetingsData\r\n\t\t\torder by start_date, title\r\n\t\t<\/cfquery>\r\n\r\n\t\t<cfreturn local.allCalendarData>\r\n\t<\/cffunction><\/pre>\n","protected":false},"excerpt":{"rendered":"
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. […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[9,8],"_links":{"self":[{"href":"http:\/\/www.blueradial.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/16"}],"collection":[{"href":"http:\/\/www.blueradial.com\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.blueradial.com\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.blueradial.com\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.blueradial.com\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=16"}],"version-history":[{"count":3,"href":"http:\/\/www.blueradial.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/16\/revisions"}],"predecessor-version":[{"id":43,"href":"http:\/\/www.blueradial.com\/blog\/index.php\/wp-json\/wp\/v2\/posts\/16\/revisions\/43"}],"wp:attachment":[{"href":"http:\/\/www.blueradial.com\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=16"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.blueradial.com\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=16"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.blueradial.com\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=16"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}