How to use query of query in cfscript
We can use queries of queries in cfscript with the Query.cfc. But this is possible in Coldfusion 9 because Query.cfc has introduced in Coldfusion 9.
Let's see the following code.
<cfquery name="qRead" datasource="#application.dsn#" username="#application.dbuid#" password="#application.dbpwd#"> SELECT employeeId,fistName,LastName FROM employees </cfquery> <cfscript> qoq = new Query(sql="select * FROM qRead WHERE employeeId=1", dbtype="query", qRead = qRead); qList = qoq.execute().getResult(); </cfscript>
I am just passing the my sql statement to sql argument and the dbtype value will be the 'query'. Here I'm pass qRead as the 3rd argument. If I am not pass this qRead then I got error as :
Error Executing Database Query. Query Of Queries runtime error. Table named qRead was not found in memory. The name is misspelled or the table is not defined.
This is because, I have the qRead in my cfml page's variable scope but I'm trying to execute the query of query on the Query.cfc and qRead was not on cfc's variable scope. Both the cfml page's variable scope and the cfc's variable scope are different and they do not have access to eachother.
But when I'm passing the qRead as argument, the init() function of the Query.cfc copy the named arguments into the local Variables scope and then while executing the query of query qRead available in the variable scope of the Query.cfc and we got the result.
For better understanding please see the blog from Ben Nadel: