Let SQL Do The Data Work

Posted At : August 17, 2007 11:51 PM | Posted By : name
Related Categories: SQL,Basics

Many come to ColdFusion from HTML development and find out that CF is great for putting data into their beautiful web pages. The natural tendency is to select all the database and dump it into the page. As the data grows, the new developer will pick up some SQL skills to filter the list down and even do some grouping of data and then output it nicely on the page. ColdFusion does this well with its CFOUTPUT attribute GROUP. This is all just wonderful.

Then a requirement comes along. Our aspiring developer then figures out he can use CFLOOPS and CFIF statements to control the output with even more but he notices his code is getting messy and pages are loading more slowly. He is sensing that he might be asking ColdFusion to do things the database server is better suited for. He is right.

A good practice in web application development is to separate the data manipulation from the data presentation. Database servers are designed to crunch data efficiently and have functions built in to do most if not all of the data manipulation. A developer should strive to have the data all crunched and manipulated before it is presented back to ColdFusion for presentation.

I was recently asked by someone online how they could get maximum value from a group of items with the same id. This would be like finding the top sales rep where the database table (monthly_sales) has the region id (id) and the sales dollars (sales). A developer could query the database for all records for the month and sort by sales dollars and id and then loop through the results and use CFSET and CFIF to out put only the first sales rep in each region. This would be asking the database to send back more data then necessary and would be asking ColdFusion to a little too much crunching.

I thought about this problem for a bit and decided to use SELECT DISTINCT which brought back distinct items if I queried one column. But I was querying two columns so the database would return distinct combinations. I need to get each distinct ID and the maximum dollars so I used DISTINCT along with the MAX function. Here's my SQL that brought back each individual id along with the maximum dollars for each.

SELECT DISTINCT id, MAX(sales) AS sales_dollars FROM monthly_sales GROUP BY id

The moral of the story is that you should get the database to do all the data work and have ColdFusion present the data.

Comments (0) | Print | Send | del.icio.us | Digg It! | Linking Blogs | 617 Views

Grouping Results with CFQUERY and CFOUTPUT

Posted At : August 8, 2007 10:01 PM | Posted By : name
Related Categories: SQL,Basics,CFOUTPUT

Grouping Results

ColdFusion makes it easy to select data out of a database and display it on a page. As soon as you feel good about your success in crossing the divide from static to dynamic, someone will say "That's nice, can you group the list by author?". Whether you are asked to group things by author, make, brand, year or whatever, ColdFusion has you covered with its grouping capabilities.

To present things in groups, we first rely on SQL to group the data before it is presented to ColdFusion. This is done with a GROUP BY or ORDER BY clause.

CLAUSE QUERY
GROUP BYSELECT Author, Title, Publisher
GROUP BY Publisher, Author, Title
ORDER BY SELECT Author, Title, Publisher
ORDER BY Publisher, Author, Title

ColdFusion will then display grouped data by using nested CFOUTPUT tags. In both cases the outter most CFOUTPUT will indicate the QUERY and each CFOUTPUT that is used to group will use a GROUP parameter except the innermost CFOUTPUT. The code below will group authors by publisher and then group books by author.

CODE
<CFOUTPUT Query="myquery" GROUP="Publisher">
  <p>#Publisher#
  <CFOUTPUT GROUP="Author">
    <br>#Author#
    <CFOUTPUT>
   <br>~ #Title#
    </CFOUTPUT>
  </CFOUTPUT>
</p>
</CFOUTPUT>
OUTPUT

Macromedia Press
Forta
~CF WACK Third Edition

~Reality ColdFusion MX

Newbie Press
Barr
~Instant Messenger for Dummies
Gates
~Dominating the World with Software Patches

If you use a GROUP BY clause with a WHERE clause the GROUP BY clause most come after the WHERE clause. Think of this as the SQL engine finding all of the results that match the WHERE statement and then it groups the results.

If you use a GROUP BY clause with an ORDER BY, use the GROUP BY before the ORDER BY clause. The SQL engine will create the groups and then order the groups, this reduces the amount of ordering that is done by SQL since the number of groups is the same or less than the number of members of the groups.

Comments (0) | Print | Send | del.icio.us | Digg It! | Linking Blogs | 632 Views
BlogCFC was created by Raymond Camden. This blog is running version 5.8.001.