Unioning queries using query of query

Here is how you can union together two queries using query of query. This function would take two queries and use query of query to combine them into a single query. The code below assumes the columns from the two source queries are identical. You would need to modify if that isn’t the case.

private any function combineQuery(qA, qB) {
 var qry1Result = arguments.qA;
 var qry2Result = arguments.qB;
 var qoqResult = '';

// create new query object
 var qoq = new Query();

// set attribute of new query object to be a query result with arbortrary name

qoq.setAttributes(QoQsrcTableA = qry1Result);
 qoq.setAttributes(QoQsrcTableB = qry2Result);

// use previously set attribute as table name for QoQ andset dbtype = query
 qoqResult = qoq.execute(sql="select * from QoQsrcTableA union select * from QoQsrcTableB", dbtype="query");

// return result
 return qoqResult.getResult();

}

private any function combineQuery(qA, qB) {
 var qry1Result = arguments.qA;
 var qry2Result = arguments.qB;
 var qoqResult = '';

// create new query object
 var qoq = new Query();

// set attribute of new query object to be a query result with arbortrary name

qoq.setAttributes(QoQsrcTableA = qry1Result);
 qoq.setAttributes(QoQsrcTableB = qry2Result);

// use previously set attribute as table name for QoQ andset dbtype = query
 qoqResult = qoq.execute(sql="select * from QoQsrcTableA union select * from QoQsrcTableB", dbtype="query");

// return result
 return qoqResult.getResult();

}

cfml tag/function

cfquery

functions

(3)

Comments

(6)

Dave Ferguson Follow

This space intentionally left blank.

You must be logged in to post a comment.

All Comments

LrdJester

2019-04-11 15:11:59

QoQ needs to be used when you have to union results of a query with the data from an API.

There is no join for that.

MadAqua

2018-08-21 07:12:49

First of all: Do not use qoq. Every SQL statement can be solved by a sub selection or joins. The database is build for and could it better.

Anonymous (You)

2017-10-19 13:52:47

That’s a pretty chunky implementation, it only really needs a single expression:

query function combineQueries(required query q1, required query q2) {
return queryExecute(
“SELECT * FROM q1 UNION SELECT * FROM q2”,
[],
{dbtype = “query”, q1 = q1, q2 = q2}
);
}

Test: https://trycf.com/gist/4fb8ce152e74e978e2f788b375d8f34c/acf2016?theme=monokai

Works on CF11+, Lucee 4.5+

I’d also never recommend to use those CFML-based services Adobe ship in CF: they’re pretty clunky & are pretty much an obsolete way of doing things.