I'm My Own SQL: Saved by the Join!

 
Jan 04, 2006

by C. Hatton Humphrey

Welcome to the first installment of a new series on using SQL (Structured Query Language, pronounced "sequel") with ColdFusion. It is my goal to impart some insight into providing proper separation of duties between the database engine and the application engine. When you look at the example I give in this article, you'll see what I'm talking about.

However, before we delve into our first little foray, I wanted to address the issue of your role in this series. You see, it's not enough that I sit here and spout out SELECT statements at you or wax poetically about proper normalization of your database and why it is imperative to good application design. There has to be a reason for the article... more of a reason than "I wanted to write an article about something." Your job is to provide the something, the material and the questions that will drive the series. I can come up with some pretty boring questions and I'm sure none of us want to be bored!

What kind of questions do I want you to ask? They can be specific or they can be generic, though I would caution against asking a question for something you want to have answered within a time limit. Questions can be of almost any level. If you're not familiar with joins or aggregate functions or text manipulations then feel free to ask about those. If you're looking for the best way to write a stored procedure or a trigger, ask that as well. I'm running with no assumptions here so if an answer doesn't make sense to you then that's a good time to ask a question! One final bit of warning: be prepared to do a little legwork on your own; while sometimes I will post a direct answer to your question there will also be times when you'll get an example but not the final answer. After all, you have to have something to practice with, don't you?

Okay, enough administrative stuff. Let's dive right into a fairly simple challenge that I have seen on a number of different occasions: outputting groups of data and the sub data within them. For example, let's say you've been asked to create a page that displays all of the categories and the items within the categories in a tree format.

Those who have used the CFOUTPUT tag know that there is an attribute called group that will handle the output part of the problem. But in a well-normalized database, we need a bit of help building the actual output.

Here's the database structure we have:

Items
Items_id
Categories_id
Name
Price

Categories
Categories_id
Name

Now let me show you a solution I've seen presented that was, well, wrong:

<!--- Start off by getting the list of items --->
<cfquery name="Items" datasource="#datasource#">
SELECT Categories_id, Name, Price
FROM Items
ORDER BY Categories_id, Name
</cfquery>


<!--- Output the query --->
<ul>
<cfoutput query="Items" group="Categories_id">
<!--- For each category ID, grab it's name from the database --->
<cfquery name="Category" datasource="#datasource#">
SELECT Name
FROM Categories
WHERE Categories_id = #items.categories_id#
</cfquery>


<li>#Category.Name#</li>
<ul>
<cfoutput query="Items">
<li>#Items.Name# - #DollarFormat(Items.Price)#
</cfoutput>
</ul>
</cfoutput>
</ul>

The obvious problem with this solution is that we end up with an unknown number of queries. Before you roll your eyes at me, let me say that I have seen this done too many times to count. What we're doing with this solution is making the application server do all of the work and making the database server just spit out data. It can do so much more than that!

The way to solve this situation is by using a simple JOIN. Databases are designed using different tables for data to make logical sense in the scheme of the data structure. The engines have been built to allow for the combining of data from tables using joins. They create a recordset of unified data even thought there are splits in the actual storage locations of the data itself.

Here is a much better solution to the issue:

<!--- Start off by getting the list of items --->
<cfquery name="Items" datasource="#datasource#">
SELECT Categories.Name as CategoryName, Items.Name, Items.Price
FROM Items JOIN Categories ON Items.Categories_id = Categories.Categories_id
ORDER BY Categories.Name, Items.Name
</cfquery>


<!--- Output the query --->
<ul>
<cfoutput query="Items" group="Categories_id">
<!--- Output the CategoryName --->
<li>#Items.CategoryName#</li>
<ul>
<cfoutput query="Items">
<li>#Items.Name# - #DollarFormat(Items.Price)#
</cfoutput>
</ul>
</cfoutput>
</ul>

Now instead of having an unknown number of queries, we have exactly one. By making the database server do a little more legwork, we save the application server the unknown number of queries.

So now that I've put an easy one out there, come on and hit me with a few questions! Oh, and if you come across code like the first example above, please resist the urge to scream and recommend that someone let you fix it!

C. Hatton Humphrey and his SQL, CJ Hatton is an instructor at ITT Technical Institute and has been working with ColdFusion and SQL for the last 6 years. You can see him at left with his own SQL, CJ. To submit a question for this series, send it via email to immyownsql@fusionauthority.com. All questions are subject to review and editing by the author and/or publishing authority. Similar questions may be grouped together and questions that appear to contain sensitive data will be reworded or edited. Questions will be answered on a first-come, first-serve basis, and answers will not be immediate. For immediate help, your best resources are still the community mailing lists. You may be notified by email if you question has been accepted for the series.

Add a Comment
(If you subscribe, any new posts to this thread will be sent to your email address.)
  
Privacy | FAQ | Site Map | About | Guidelines | Contact | Advertising | What is ColdFusion?
House of Fusion | ColdFusion Jobs | Blog of Fusion | AHP Hosting