October 17, 2017
Unioning queries using query of query
Comments
(6)
October 17, 2017
Unioning queries using query of query
This space intentionally left blank.
Newbie 1 posts
Followers: 2 people
(6)

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();

}

 

6 Comments
2019-04-11 15:11:59
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.

Like
2018-08-21 07:12:49
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.

Like
2017-10-19 13:52:47
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.

Like
(3)
>
Anonymous
's comment
2017-10-31 05:04:58
2017-10-31 05:04:58
>
Anonymous
's comment

Hi Adam,

Could `{dbtype = “query”, q1 = q1, q2 = q2}` be shortened to `{dbtype = “query”}`?

Thanks!,
-Aaron

Like
>
hemsmarki
's comment
2017-10-31 05:08:50
2017-10-31 05:08:50
>
hemsmarki
's comment

Hi Adobe,

Is Markdown support planned?

Thanks!,
-Aaron

Like
>
hemsmarki
's comment
2017-10-31 06:16:33
2017-10-31 06:16:33
>
hemsmarki
's comment

Ah yes: indeed it could. Didn’t realise one could reference variables directly there. Even better!

Like
Add Comment