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"));