Merging a array of queries into a single query

Question

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].

(Edit) I think the `from` clause 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 ` ... SELECT * FROM theQuery ....` — Leigh, Feb 02 '14 at 23:00
@Nesha8x8 - No, the QoQ expects the *name* of a variable containing a query, not a query object. — Leigh, Feb 03 '14 at 03:00
If you have an array of queries, you may have done something unwise. — Dan Bracuk, Feb 03 '14 at 03:12
Each on of the items in the array is the results if a normal cfquery — James A Mohler, Feb 03 '14 at 03:54
QoQ uses brackets to escape names, so it's not getting as far as CF looking at it as a variable name. May or not work if you write `[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). — Peter Boughton, Feb 03 '14 at 12:17

duncan · Answer 1 · 2014-02-03T12:32:32.767

2

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#">

edited Feb 03 '14 at 12:32

answered Feb 03 '14 at 09:25

duncan

31,401
13
78
99

Actually, a minor thing, but instead of using a counter I would replace `i` with a boolean check at the start of the query - i.e. `` and `UNION` – Peter Boughton Feb 03 '14 at 15:34
I tried something like this, but it did not work. It looks like the string inside of `` is built first, and then later the query is ran – James A Mohler Feb 03 '14 at 16:48
1

Define `did not work`. Same syntax error as before, unexpected results from the query, some other type of error, ...? – duncan Feb 03 '14 at 17:04
(Edit) @Duncan - I think he means you must use unique query names ie `SELECT * FROM qry1 ... SELECT * FROM qry2 ...`. Otherwise, the merged query only contains data from the *last* query in the array. – Leigh Feb 03 '14 at 17:37
What Leigh said is correct. It only contained the last `k` – James A Mohler Feb 04 '14 at 04:37

Russ · Answer 2 · 2014-02-04T06:02:12.227

If 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

score 0 · Answer 3 · answered Feb 03 '14 at 14:45

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>

score 0 · Accepted Answer · answered Feb 03 '14 at 16:51

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

Merging a array of queries into a single query

4 Answers4