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>
// 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