I want to merge an array of queries into one query
I have tried this
<cfquery name="MergedData" dbtype="query">
<cfloop from="1" to="#arrayLen(arData)#" index="k">
SELECT *
FROM arData[k]
<cfif k LT ArrayLen(arData)>UNION</cfif>
</cfloop>
ORDER BY EID
</cfquery>
I get an error that looks like
<br><b>Query Of Queries syntax error.</b><br> Encountered "[k].
Try an alternative way of looping over your array. You need to create your own counter though to figure out the logic of if you need to keep appending 'UNION' to your SQL statement.
<cfset i = 1>
<cfquery name="MergedData" dbtype="query">
<cfloop index="k" array="#arData#">
SELECT *
FROM k
<cfif i LT ArrayLen(arData)>UNION</cfif>
<cfset i++>
</cfloop>
ORDER BY EID
</cfquery>
NB: you wouldn't need to calculate a counter yourself if you're using Railo instead of Adobe CF, as you can then do both index and item like so, as Peter mentioned in the comments above:
<cfloop index="i" item="k" array="#arData#">
i with a boolean check at the start of the query - i.e. <cfset FirstRow = true /> and <cfif FirstRow ><cfset FirstRow=false /><cfelse>UNION</cfif>
<cfquery> is built first, and then later the query is ran
did not work. Same syntax error as before, unexpected results from the query, some other type of error, ...?
SELECT * FROM qry1 ... SELECT * FROM qry2 .... Otherwise, the merged query only contains data from the last query in the array.kIf you'd like to accomplish this with functional programming, you could use the Underscore.cfc library (requires CF 10+ or Railo 4+):
// instantiate Underscore library
_ = new Underscore();
// convert the array of queries to a single array of structs
mergedArray = _.reduce(arrayOfQueries, function (memo, query) {
// convert current query to an array of structs
// and concatenate it to the rest of the result
return _.concat(memo, _.toArray(query));
}, []);
// convert the array of structs back to a query
mergedQuery = _.toQuery(mergedArray);
This solution utilizes reduce() to combine the array of queries into a single array of structs. The anonymous function passed to reduce() converts each query in the array of queries to an array of structs using toArray(), then concatenates that array with the rest of the array of structs (the memo value).
Once the array of queries has been converted to a single array of structs, it is a simple matter to convert it back to a query using toQuery() (assuming that is necessary).
Note: I wrote the Underscore library
Try like this:
<cfsavecontent variable="testing">
<cfset j = 1>
<cfloop array="#test#" index="i">
SELECT id FROM #i# LIMIT 10
<cfif j LT ArrayLen(test)> UNION </cfif>
<cfset j++>
</cfloop>
</cfsavecontent>
<cfquery name="qTest" datasource="#application.dsn#">
#testing#
</cfquery>
This is what end up working
<cfquery name="local.qryMergedData" dbtype="query">
<cfloop array="#arguments.Data#" index="k">
<cfset local.currentIndex++>
<cfset setvariable("Data_#local.currentIndex#", k)>
SELECT *
FROM Data_#local.currentIndex#
<cfif local.currentIndex LT local.MaxIndex>UNION</cfif>
</cfloop>
ORDER BY EID
</cfquery>
I really don't like that I am setting a variable inside of a <cfquery>, but at least it is only one query
fromclause expects a valid variable name containing a query, which would rule out array notation. Try either an array cfloop instead, or copy the query into an intermediary variable ie<cfset theQuery = arData[k]> ... SELECT * FROM theQuery ....[arData[k]]instead, but I'd probably do what Duncan suggests below (except because I use Railo I'd be able to specify both index and item attributes in the loop to avoid the manual counter).