Blog

Brad Wood

March 15, 2013

Spread the word


Share your thoughts

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.

Add Your Comment

(2)

Mar 15, 2013 11:41:38 UTC

by Dan Fredericks

Ok, so what version can these be used in? we have an older version of ColdBox, but it seems like some of these might be useful for us.

Mar 15, 2013 15:39:12 UTC

by Brad Wood

I don't know right off what version of ColdBox first had the QueryHelper plugin, but you can easily look in the /coldbox/system/plugins/ directory in your installation for the QueryHelper.cfc file and crack it open to see what's inside. All of the public methods in there are yours for the using!

Recent Entries

Ortus Monthly Recap - Jan/Feb

Ortus Monthly Recap - Jan/Feb

Ortus Solutions is kicking off 2025 with major milestones, from the highly anticipated Into the Box 2025 to groundbreaking advancements in BoxLang and key industry event appearances at Jfokus and DevNexus 2025. With new product releases, enhanced compatibility, and exclusive discounts, we’re equipping developers with the tools they need to build faster, smarter, and more efficiently.Let’s dive into the latest updates shaping the future of modern web development!

Maria Jose Herrera
Maria Jose Herrera
March 07, 2025
BoxLang 1.0.0 RC2 Launched

BoxLang 1.0.0 RC2 Launched

We’re entering the final stretch of our pre-releases, and we couldn’t be more excited to introduce RC2! 🚀 This release marks a major leap in performance and compatibility, the result of over six months of intensive development. Beyond enhanced stability and seamless integration, RC2 delivers game-changing performance optimizations that push the boundaries of efficiency. Get ready for our fastest, most refined release yet!

Luis Majano
Luis Majano
March 05, 2025
Building a Web App with BoxLang!

Building a Web App with BoxLang!

BoxLang, the new JVM-based scripting language from Ortus Solutions, is gaining traction among developers looking for a modern, lightweight alternative for building web applications. In a Recent blog post, Raymond Camden took it for a spin by developing a simple blog application, highlighting BoxLang's capabilities and ease of use. Let’s break down his experience and key takeaways.

Maria Jose Herrera
Maria Jose Herrera
March 04, 2025