Coldfusion Query of Queries with looping

The follow snippet was a quick little test done at the office, when asked from a new team member about doing queries (this developer is from a .Net background and is still getting his feet wet in CF). The question/problem was that we were trying to solve was that of reducing the trips to the SQL server via processing and queries, immediately I thought of querying the original query, thus leading to the snippet below.

I will start posting some of these little snippets of code so hopefully they will be helpful to some new developers and to also get feedback and alternate solutions from more advanced developers, as this is an ever changing environment and I am always interested in various methods of performing tasks.

The follow snippet was a quick little test done at the office, when asked from a new team member about doing queries (this developer is from a .Net background and is still getting his feet wet in CF).  The question/problem was that we were trying to solve was that of reducing the trips to the SQL server via processing and queries, immediately I thought of querying the original query, thus leading to the snippet below.

The <cfscript> is just calling a coldfusion component that has two functions: the first function performs that first trip to the SQL server and returns the resultset, which is show with the getData() function.  The next task (scenario) was to pass in each letter of the alphabet to query each letter (an A-Z index),  so instead of plaguing our code with <cfif> statements or using a <cfswitch> we wanted to compress the code as much as possible thus the loop.

In the second function call getSomeData() we are passing in our resultset from our first query along with the lowercase alpha value to perform a query of queries.  The NumberOfRecords is just a variable returned from performing a count on the ID’s in the second function to output our recordcount.  Again this was done quickly at the end of the day and I’ll likely update this post as the tasks become more complete.

Your component will consist of the following, we called it qry.cfc


<cfcomponent>
  <cffunction name="getData" output="No" returntype="query">
    <cfset var qry1="">
    <cfquery datasource="#data#" name="qry1">
       SELECT * 
       FROM content
    </cfquery>
    <cfreturn qry1>
  </cffunction>

  <cffunction name="getSomeData" output="No" returntype="query">
    <cfargument name="qryObj" type="query" required="true" />
    <cfargument name="qryParam" type="string" required="true" />

    <cfquery  dbtype="query" name="qry2">
      SELECT COUNT(location) AS NumberOfRecords 
      FROM arguments.qryObj 
      WHERE location LIKE ('#arguments.qryParam#%')
    </cfquery>

    <cfreturn qry2 />
   </cffunction>

 </cfcomponent>

The code below will be your index.cfm file.

<cfscript>
  qryObj=createObject("component","qry");
  qry1=qryObj.getData();
</cfscript>

<cfloop index="intLetter" from="#Asc('A')#" to="#Asc('Z')#" step="1">
  <cfset strLetter = lcase(Chr( intLetter )) />
  <cfset qryResult = qryObj.getSomeData(qry1,"#strLetter#") />
  <cfoutput>
  <p>Queried leeter: #lcase(Chr( intLetter ))#
  Recordcount: #qryResult.NumberOfRecords#</p>
  </cfoutput>
</cfloop>

Related posts:

  1. Coldfusion 9 Resources, Tutorials and Examples
  2. Best JQuery Image Slider with Transition Effects

Leave a Reply

Submit
http://oldrichkyn.com/ory-4648/ Under x-ray guidance, the tip of the catheter is moved to the arteries that feed the fibroid, and then a clotting medicine is used to cut off blood supply to the fibroid so that it will shrink. Myomectomy: a retrospective study to examine reproductive performance before and after surgery. Myomectomy: a retrospective study to examine reproductive performance before and after surgery. lamingtonhotelmotel.com.au/ksl-3202/ Epub 2008 dec 1. bayer viagra coupons catchthelightfoundation.org/vfb-5668/ Girls, please donacirceurotradet suffer as per advice of your gp. viagra online cheapest Leiomyomata. 7 e2=30 amh=1. take viagra100 2011117:396. Do not consider webmd user-generated content as medical advice.