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
viagra online shopping india Org das-uterine-fibroids. They can provide an alternative to a hysterectomy. They can provide an alternative to a hysterectomy. viagra for daily use testimonials Login. annaneri.com/louiseinnylonheaven/dailing.php?guw=269989 http://globaphone.net/veelers/chev.php?mpl=269763 035). review efficacy of pre-operative gonadotrophin hormone releasing analogues for women with uterine fibroids undergoing hysterectomy or myomectomy: a systematic review. get generic viagra prescription online In a few women, remnants of fibroids are passed through the vagina. http://jaf.org/acupuncturist/rav.php?grb=268445 Acircnbsp2005:(14):226482. Cochrane database syst rev. estudi-juridic.es/tve-268698/ http://jerryleescajunfoods.com/gnn-267259/ review efficacy of pre-operative gonadotrophin hormone releasing analogues for women with uterine fibroids undergoing hysterectomy or myomectomy: a systematic review. angiovis.org/iyr-266955/ Am j obstet gynecol. It may take several months to several years for the fibroids to completely shrink. http://apropdetu.com/uap-266498/ Pelvic pain and pressure pain in the back and legs pain during sexual intercourse bladder pressure leading to a frequent urge to urinate pressure on the bowel, leading to constipation and bloating abnormally enlarged abdomen imaging expertise enables interventional radiologists to provide gynecologists and their patients better diagnosis and nonsurgical treatment options women typically undergo an ultrasound at their gynecologists office as part of the evaluation process to determine the presence of uterine fibroids.