[Tip of the Week] Being More Productive with the Query Helper Plugin

The Query Helper plugin is a hidden gem in ColdBox. This plugin has a handful of super useful functions you can perform against query objects-- some of which you may have gone out of your way to write on your own in the past, or just simply lived without. Here a brief list of my favorite functions from the Query Helper plugin that allow you to do sweet one-line manipulations of one or more query objects:

filterQuery
Pare down a result set to only the records you want.

sortQuery/sortQueryNoCase
Resort a result set on a column of your choosing.

doLeftOuterJoin
Yes, you heard right-- perform an actual left outer join with two result sets. This alone is worth the cost of admission.

doQueryAppend
Very handy to union two result sets together quickly.

querySim
Great for mocking and testing. Allows you to easily create a data set out of one or more lines of of pipe-delimited values.

rotateQuery
Pivots a result set so rows become columns and columns become rows.

There are more goodies in there-- take a look at the docs, or directly in /coldbox/system/plugins/QueryHelper.cfc to find the rest of them.

As usual, the Query Helper plugin can be accessed from any framework object (views, controllers, interceptor) like so:

getPlugin(“QueryHelper”)

and can be injected into your models like so:

property name=“QueryHelper” inject=“coldbox:plugin:QueryHelper”;

More info here: http://wiki.coldbox.org/wiki/Plugins:QueryHelper.cfm

P.S. Remember, while these functions work great for smaller result sets, watch out for performance issues if dealing with lots of data (tens of thousands of rows). There are some things databases are better at.

Thanks!

~Brad

ColdBox Platform Evangelist
Ortus Solutions, Corp

E-mail: brad@coldbox.org
ColdBox Platform: http://www.coldbox.org
Blog: http://www.codersrevolution.com

woot! i had no idea about this. i’m so excited!

Agreed.

This is great info Brad, and I had no idea.

Dude, you should be. I use querysim all the time early in development. Love it