Ok this trick is intended for the newer ColdFusion programmers as most experience programmers will either know this one, or feel silly admitting they didn’t.
Let’s assume you have a table of users and each user is assigned to a particular department.
For the sake of simplicity we’ll assume the department ID is stored in the user’s table as only one user can be in one department.
So you might have a query which looks like this
SELECT u.firstName, u.lastName, u.email, d.departmentName, d.departmentID
FROM users u
JOIN department d on d.departmentid = u.departmentid
ORDER BY u.lastname
This SQL code will return a listing of all the users in your table and they will be ordered by their last names.
Now let’s assume that when you started out there were only a handful of employees and two departments. So you might just do something like this for the output:
| Employee Name | Employee Email | Employee Department |
|---|---|---|
| #lastName#, #firstName# | #email# | #departmentName# |
Now you could fancy it up and make it so users could click on a column heading and sort by that column if you want, but we’ll skip that for now.
Now fast forward say 2 years, your company has been growing by leaps and bounds. You now have 50 employees and 5 different departments. Your little employee listing app just isn’t cutting it anymore.
You boss says he likes having all the employees on one page but he’d rather see them grouped together by department. You take that guidance and head back to your office.
Now your first inclination might be to query the DB for departments then loop over each department and grab those respective employees. This is perfectly acceptable but let me show you a way to do it faster, and with less code.
Take your previous query and change it to look like this
SELECT u.firstName, u.lastName, u.email, d.departmentName, d.departmentID
FROM users u
JOIN department d on d.departmentid = u.departmentid
ORDER BY d.departmentName
Now you’ll notice all we did is change the order by clause. This becomes important later on.
Now for the fun part! Let’s get this displayed the way the boss wants it. Take a look at this code below.
| #departmentName# | |
|---|---|
| #lastName#, #firstName# | #email# |
So how does it work? The key is the order by clause, and the placement of your cfoutput tags.
First you’ll notice we got rid of our cfloop tag and replaced it with a cfoutput tag with the query and group attributes.
Now what you may or may not know is when you do a
the ColdFusion server will loop over the results of the query and output the code between the start and end tag for each row in your result set.
Now with this logic in mind you might be thinking we are going to get one ugly output with the department name being repeated before each employee. Not with ColdFusion! You see the extra cfoutput tag pair later on in the table (at the start and end of the HTML code for our employee data row).
ColdFusion will execute everything on the outside of these tags once and everything on the inside it will repeat for each row, BUT it will only output the rows which have employees in that department.
So to put it into perspective the ColdFusion server will execute this code once
| #departmentName# |
|---|
then it will loop over this code for each employee record, making sure it only outputs those employees which belong to the department displayed above
and then it will output this code:
To finish the process up.
In short the ColdFusion server is going do to the double looping for you.
The caveat here is that your group=”” value in your cfoutput tag must be the same as your order by clause in your SQL statement.
Now wasn’t that a bit easier than doing all the looping yourself?
Eric Jones
