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

}

 

4 Responses

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

Leave a Reply to Adam Cameron Cancel reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.

Related