« Run IE6, IE7, and IE8 on the same PC! | Web Developer position at the City of Portland »
July 17, 2009
ColdFusion Tip: Query columns in correct order
One quirk in Coldfusion that I've had to battle before is that columns in query objects are sorted in alphabetical order. Normally this isn't too much of an issue because you just output the columns in the desired order when you're displaying the output on a page. But there are times when you need to loop over a list of the columns and you'd like them to appear in the order defined in the SQL query or DB schema.
You can use queryObj.columnList to get a list of query's columns, but as I already mentioned this list is sorted alphabetically. To get a column list in the correct order, use this simple trick:
ArrayToList( queryObj.getColumnNames() )
This method appears to be an undocumented method of the underlying Java query object.
Posted at 10:27 AM in ColdFusion
Comments
wish I'd known that 3 months ago - where did you find that out?
Posted on July 17, 2009 at 12:00 PM
2. Richard Davies says:
I discovered this hack from http://objecttoexcel.riaforge.org/. I had submitted a patch containing an alternative (and more verbose) solution to the project. The project owner liked the idea, but chose to implement it with this better solution instead.
So I can't take credit for being the first to discover this, but I'm guessing it isn't common knowledge so I wanted to share it.
Posted on July 17, 2009 at 12:10 PM
3. Raymond Camden says:
Why not use getMetaData()? It returns the columns in the order you used in SQL, and gives you a bit more info about the columns.
Posted on July 17, 2009 at 12:58 PM
4. Leigh says:
Good researching.
If you are looking for a documented option, you can take the long way around and use getMetaData(query). It returns an array of structures which can loop through your properly ordered list.
http://www.coldfusionjedi.com/index.cfm?mode=entry&entry=1CEE22D9-B4B6-62CD-1AC9A7C42D02ACFC
Posted on July 17, 2009 at 1:02 PM
5. Richard Davies says:
Ray and Leigh,
That's the approach I was previously using before I learned this technique. But this new one-liner takes the cake compared to having to loop over the struct returned from getMetaData() and manually building the column list.
True, getMetaData() does return additional info, but in this case I don't need it and it just gets in the way. I prefer the simplicity and efficiency of this new method.
Posted on July 17, 2009 at 1:32 PM
6. Mike Causer says:
If you want all of the columns, in their specified case and in order, try this:
loc.columns = loc.query.getMeta().getColumnLabels();
Posted on October 29, 2009 at 5:26 PM
1. brian says: