ColdFusion CFScript Query of Queries Example

This post is more for me than anything else...but I couldn't find a decent example of how to write a query of queries in ColdFusion's CFScript syntax. Below you'll see two queries; the first one is a simple query using ColdFusion's auto-generated dsn, the second one narrows the result set just a bit.

<cfscript>
    // Basic Query Syntax
    q1 = new Query();
    q1.setDatasource('cfartgallery');
    q1.setSQL('select * from artists');
    rs1 = q1.execute().getResult();
    WriteDump(var=rs1,label='RS1');
    
    // Query of Queries
    q2 = new Query();
    q2.setDBType('query');
    q2.setAttributes(rs=rs1); // needed for QoQ
    q2.addParam(name='state', value='CO', cfsqltype='cf_sql_varchar');
    q2.setSQL('SELECT * FROM rs where state = :state');
    q2.setMaxRows(2); // limit max rows, if desired
    rs2 = q2.execute().getResult();
    WriteDump(var=rs2,label='RS2');
</cfscript>

This should produce something similar to the following output:

Hope this helps...cheers!

Comments
I always forget how to do this. Thanks for the reminder!
# Posted By existdissolve | 11/9/12 9:27 AM

© 2014, Stephen J. Withington, Jr.  |  BlogCFC was created by Raymond Camden – Version 5.9.004

Creative Commons License  |  This work is licensed under a Creative Commons Attribution 3.0 Unported License.  |  Hosted by Hostek.com