Trick for the Newbies

Tweet

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

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:


#departmentName#
#lastName#, #firstName# #email#

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?

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Scott Barnes

    You can also achieve the same result but actually faster then the “group” attribute by doing this:



    Start new distinction.

  • PaulWeston

    That cfoutput query=’x’ stuff I tried a while ago and ended up with some considerable problems. It looks neater but there is a nasty side effect on queries inside queries. If you have a very complex scripted page and have a few of these embedded in another cfoutput query=’x’ type scenario you end up with strange results. I used to have a test piece of code that proved this in about twenty lines. The same issue occured from CF 4.5 through to 6.1.

    I now treat the resultset like an array. It has never failed since. If you’re only going down 1 level (ie not nested) then there is no problem and your way works fine.

    I’d like to be able to claim I discovered this ‘feature’ on my own but it was after a bit of Googling around, someone out there broke it down why I was having problems and it all then made sense. I’ve lost the link but it’s out there somewhere.

    - PaulWeston

  • Nate

    A lot of times problems with nested CFQUERIES can be solved by always scoping your query variables. For instance: Query1.Firstname, Query2.Firstname. This way you will never have variable collision…if that was the strange result you were experiencing. I remember having this problem back in CF 4.0 before I knew about scope.