ColdFusion Muse

Cool query function tip using array syntax.

I often pull in a full data set then use query of a query to pull subsets of data from it. The purpose is to minimize the number of hits to the database server (which is usually the cheif bottleneck of any application). This works pretty well, but today I stumbled on a tip that can really save some effort and reduce the sheer number of lines of code. It has to do with using aggregate functions to derive other values from the data. For example, if I have a large query that is being output in a row and I want totals at the bottom I usually run a query like this:

I can then output #getTotals.total_a#, #getTotals.total_b# and #gettotals.total_c# in my last grand totals row to display the values. This actually works pretty well. But there is another way that was brought to light by Jim Davis on CF-Talk. You probably know that a query object is basically a structure of arrays. That's why you can access cells of the query like queryname.columname[rownumber] or queryname['columname'][rownumber]. That's pretty cool and it is useful when dealing with queries inside of CF script or in other cases. However, you can't treat the query as a structure. For example structKeyList(queryname) doesn't work. It turns out you can treat the column names as arrays and most of the array functions work.

To return to my previous example, I would not need the query of a query at all. I would simply need to use #arraySum(myBigQuery['col_a'])#, #arraySum(myBigQuery['col_b'])# and #arrayAvg(myBigQuery['col_c'])# in my totals column. Note that in CFMX the bracket syntax is required (queryname['col']) because it's seemingly the only way to get a reference to the array (as opposed to the value of the first index) to return. This saves the q of a q operation and makes the code pretty readible as well - the best of both worlds. I'm going to test his tip at the first opportunity.

A caveat was mentioned by S. Isaac Dealey - whom I always think of as "Sir Isaac Dealey" because he should be knighted on the basis of his vast knowlege (ha). He says that the syntax seems to vary from version to version, and he specifically mentioned CF 5 to CFMX. That's definitely something to watch for. It took a few trys to get it to work. I've tested arraySum(), isArray(), arrayMin(), arrayMax(), arrayAvg(), arrayToList(), arraySort(). Note that I don't know what "arraySort()" does to the underlying query. Pretty cool though.