Two ColdFusion Things I Love

Lately, while working on new interfaces for UDI - Digital Crews Database Interface custom tag (written by Peter www.cftopper.com) I was in the need for a simple method of querying a database using recursion with little overhead on the database.

2007-01-24 20:48:00.0

Two ColdFusion Things I Love

  1. REQUEST Scope
  2. Query Of Queries

Lately, while working on new interfaces for UDI - Digital Crews Database Interface custom tag (written by Peter www.cftopper.com) I was in the need for a simple method of querying a database using recursion with little overhead on the database.

I needed this to build up the path of a node in a { PARENTID, ID, NAME } type table. For instance, if it was a breadcrumb navigation and I wanted a page 4 levels deep, I wanted to know that the path was : Home » About Us » History » Overview when all I had to work with was the ID of the Overview page.

Before the REQUEST scope (CF 4.5 / 5 and earlier) I would possibly have used a SESSION or passed the data all around the shop. Now, I just pop my data in the REQUEST scope and can access it in the recursive custom tag.

Another issue I had was solved by Query Of Queries. I only wanted to hit the MySQL database once and in my example above, before Query Of Queries, I would have hit the database 4 times in a loop. Not good. Now, I do one query against the database and pass the resultant query into the custom tag (or function) recursively. Far more efficient!

One snag was if I passed the query as follows : <cf_myCustomTag query="#myDBSQuery#"> and tried to use it in the myCustomTag.cfm page as follows <cfquery name="QOQQuery">SELECT * FROM #ATTRIBUTES.query#</cfquery> I got an error saying the query wasnt in memory.

To solve it, I created a temp variable called cQuery and set it to ATTRIBUTES.query using <cfset cQuery=ATTRIBUTES.query> and called <cfquery name="QOQQuery">SELECT * FROM #cQuery#</cfquery>

Tags: ColdFusion | Tips
Add to your del.icio.us    DIGG This!    Technorati Cosmos Link    Post to Reddit    Add to your Furl    Add to Blinklist
Comments [0] - Leave a comment

Comments

No comments found.