Easily build where clauses for the CFWheels ORM with this whereify helper

Rather than stuff around building up a string when one’s WHERE clause is conditional.. I make heavy use of this simple little helper which takes an array, and returns a finished string ready to pass to the CFWheels ORM.

// helpers.cfm

 * Hint
 * I surround each array element in brackets and return delimited by an operator
public string function whereify(required array array, string operator="AND") {
	var loc = {};
	loc.array = [];
	for (loc.i=1; loc.i <= ArrayLen(arguments.array); loc.i++) {
		loc.array[loc.i] = "(#arguments.array[loc.i]#)";
	return ArrayToList(loc.array, " #arguments.operator# ");

And here’s how I use it..

// YourController.cfc

var loc = {};
// build a sexy where clause array..
loc.where = [];
ArrayAppend(loc.where, "foo = 'bar'");
if (StructKeyExists(params, "slayer")) {
	ArrayAppend(loc.where, "somecolumn IN (1,2,3)");

// use the whereify helper in your ORM call
// Produces: (foo = 'bar') AND (somecolumn IN (1,2,3))
users = model("User").findAll(whereify(loc.where));

// You can also specify an operator
// Produces: (foo = 'bar') OR (somecolumn IN (1,2,3))
users = model("User").findAll(whereify(loc.where, "OR"));

Enforcing the use of routes in CFWheels

If you really like routing (you’d find that really funny if you’re Australian) and want to enforce their use in all linkTo(), urlFor() and startFormTag() function calls.. Do as I do..

Put the function below into your /controllers/Controller.cfc

<!--- /controllers/Controller.cfc --->
<cffunction name="urlFor" access="public" output="false" hint="ensure routes are used for all urls">
    <cfif ! StructKeyExists(arguments, "noroute") && ! ( (StructKeyExists(url, "controller") && url.controller == "wheels") || StructKeyExists(arguments, "url") )>
      <cfif ! (StructKeyExists(arguments, "route") && Len(arguments.route) gt 0)>
        <cfthrow message="Please use a route rather than an action.. Thanks!">
    <cfreturn core.urlFor(argumentCollection=arguments)>

This overrides, the core urlFor() method, checking for the existence of a route argument.. UNLESS, the controller is “wheels” which probably means you’re using the test framework or managing a plugin. There is also a way to bypass the route nazi.. by using the noroute=true arguments.

Happy routing.. Teehee!

CFWheels Calculated Properties on Steroids using an afterFind Callback

One limitation with using calculated properties in your CFWheels models is that one is limited to what can be achieved using SQL. CFML has almost infinite possibilities for formatting strings which simply cannot be done easily (or at all) in SQL.

A technique that I use to get around this limitation is to create new properties using an afterFind callback for both objects AND queries in a single callback.

In the code below I create two new properties, createdAtShort and createdAtLong which are just different date masks applied to the trusty createdat property. It’s a fairly simple example, but highlights the huge potential of keeping this type of logic in your model and out of your views. Hopefully this will get your brain moist.

<!--- /models/User.cfc --->

<cfcomponent extends="Model" output="false">

  <cffunction name="init">
    <cfset afterFind("$$setStuffAfterFind")>

  <cffunction name="$$setStuffAfterFind" access="private">
    <cfif StructKeyExists(arguments, "createdAt")>
      <cfset arguments.createdAtShort = DateFormat(arguments.createdAt, "d/m/yy")>
      <cfset arguments.createdAtLong = DateFormat(arguments.createdAt, "dddd, dd mmmm yyyy") & " " & TimeFormat(arguments.createdAt, "hh:mmtt")>
      <cfreturn arguments>


Now you can call your model and the result should contain your new properties..

<!--- /controllers/Users.cfc --->

<cfset user = model("User").findOne()>
<cfset users = model("User").findAll(maxrows=5)>

<cfdump var="#user.properties()#">
<cfdump var="#users#">


  1. Avoid performing further database operations inside the callback when using findAll.. y’know the whole querying within a recordset loop thing..
  2. afterFind callbacks are not called on included models. So.. model("Company").findAll(include="Users") won’t contain your new properties.

UPDATE: Chris Peters has since shown me the error of my ways.. it turns out that the arguments scope can be used for both queries and objects. This has simplified the entire concept considerably (and made ‘my’ code look eerily similar to the official CFWheels docs! The code and description above have been modified to use arguments scope.

Run your CFWheels database migrations automatically on application start

It’s no secret that I’m a massive fan of the Coldfusion on Wheels framework.. and there are a couple of tools that I use in my deployment process that are invaluable to me.

One of those is the DBMigrate plugin. I recently came up with a way to automatically migrate to the latest database version when the application first starts.

This can be useful/essential when:

  • Your deployment process doesn’t use post-deploy hooks
  • You want to make your application more portable
  • You want to simplify your deployment script

Essentially, it calls a couple of the plugin’s methods whilst doing a little array-fu.


// migrate database
_dbm = {}
// create a pointer to the dbmigrate plugin
_dbm.plugin = application.wheels.plugins.dbmigrate
// create an array of available migrations sorted by version in descending order
_dbm.available = []
for(item in _dbm.plugin.getAvailableMigrations()){
ArraySort(_dbm.available, "numeric", "desc")
// migrate to the most recent version

There are a few caveats:

  • It will execute every time the application loads OR is manually reloaded via the reload=true parameter (though it’s fairy lightweight when there are no migrations to execute)
  • It will execute on every server in your cluster
  • There is (currently) no error handling
  • It could disable your application if your migrations fail