Working Around SQL Namespace Bug In CFWheels Models In ColdFusion

At the heart of the CFWheels framework is a robust Object-Relational Mapper (ORM) that translates your high-level object fetching into low-level, complex SQL statements. A nice feature of this ORM is that it can perform this SQL translation for nested-object fetching (which was the inspiration for my previous post on declaratively destructuring ColdFusion query objects). Unfortunately, there's a bug in the Microsoft SQL Server (MSSQL Server) implementation that breaks when the nested objects are defined in a namespace. This post is my attempt to work around this bug.

But first, let's demonstrate the issue. At PAI, manufactured truck parts move through the manufacturing process based on a "Router" (ie, the "route" through the set of machines). Each "router" has a set of "Operations" that the part has to move through (ie, the "machine" and its operation instructions). Both of these models exist in a namespace:

• manufacturing.routing.Router
• manufacturing.routing.Operation

And, they are defined in a one-to-many relationship based on the routerID. On its face, pulling this information back using the ORM is (should be) dead-simple because the ORM hides all the complexity:

<cfscript>

	// Model: manufacturing.routing.Router [1..]
	// Model: manufacturing.routing.Operation [..Many]
	router = model( "manufacturing.routing.Router" ).findByKey(
		key = 48,
		include = "operations",
		returnAs = "structs"
	);

	dumpModel( router );

</cfscript>

Aside: Note that I'm using my light-weight CFWheels debugging utility instead of the native dump() method in Lucee CFML. This helper function recursively plucks-out the model properties without all of the Wheels methods.

What this CFWheels code should do is pull back a single ColdFusion struct — the Router with ID 48; and, that Router struct should have a property, operations, that contains an array of Operation structs. Unfortunately, when we run this code, we get the following error:

Incorrect syntax near '.'.

The . that's causing the issue is the CFWheels model namespace. If we look at a truncated version of the SQL statement that Wheels is generating, it looks like this:

SELECT
	mfgRouters.id,
	mfgRouters.productCode,
	/* ... rest of router attributes ... */

	mfgRouterOperations.id            AS manufacturing.routing.Operationid,
	mfgRouterOperations.routerid      AS manufacturing.routing.Operationrouterid,
	mfgRouterOperations.operationcode AS manufacturing.routing.Operationoperationcode,
	mfgRouterOperations.sort          AS manufacturing.routing.Operationsort,
	/* ... rest of operation attributes ... */
FROM
	mfgRouters
LEFT OUTER JOIN
	mfgRouterOperations
ON
	mfgRouters.id = mfgRouterOperations.routerid

/* ... rest of filtering logic ... */

The root of the issue is that CFWheels isn't escaping the column aliases. The (first) . that is breaking the SQL is this one:

AS manufacturing.routing.Operationid

Here, this looks to the SQL Server like a database schema namespace; but, the SQL Server is expecting an identifier. To fix this, all CFWheels would need to do is escape the column aliases:

• MSSQL: AS [manufacturing.routing.Operationid]
• MySQL: AS `manufacturing.routing.Operationid`

Until CFWheels fixes this in the core, we can work around the bug by performing the escape manually in our ORM select argument. The unfortunate part of this that it's quite verbose:

<cfscript>

	// Model: manufacturing.routing.Router [1..]
	// Model: manufacturing.routing.Operation [..Many]
	router = model( "manufacturing.routing.Router" ).findByKey(
		key = 48,
		select = "
			mfgRouters.id,
			mfgRouters.productCode,
			
			mfgRouterOperations.id            AS [manufacturing.routing.Operationid],
			mfgRouterOperations.routerid      AS [manufacturing.routing.Operationrouterid],
			mfgRouterOperations.operationcode AS [manufacturing.routing.Operationoperationcode],
			mfgRouterOperations.sort          AS [manufacturing.routing.Operationsort]
		",
		include = "operations",
		returnAs = "structs"
	);

	dumpModel( router );

</cfscript>

To work around the bug, all I'm doing is generating the SELECT statement that the ORM would be generating on its own; only, I'm explicitly escaping the column aliases in my select ORM method argument. And, since this is what the ORM was trying to do, the explicitly-aliased columns get seamlessly translated back into the nested result that the ORM was intending to generate:

I'm guessing / hoping that this would be a one-line fix in the CFWheels framework core. But, until I can wrap my head around the logic in the ORM, this at least gives me a path forward in which I don't have to fall-back to writing pure SQL.

UPDATE: Overriding $selectClause()

After digging through the CFWheels internals, and trying to follow the flow of control for a .findAll() method call, down through the various private methods, and through to where the array-of-structs gets created, I realized that there's one internal private method that generates the SELECT clause of a SQL statement. And, if you recall from above, I'm working around this Wheels bug by explicitly providing my own SELECT columns (and explicitly escaping the namespace aliasing). Which means, I can actually apply this bug fix implicitly by overriding the internal method that generates the SELECT statement.

Of course, overriding an internal method used by CFWheels is not the smartest. But, presumably this bug will be fixed eventually within the framework and I can just remove my monkey-patching.

Thankfully, the one method I needed to override was very small. All it did was call another method and then prepend the SELECT token. Which means, copy-pasting it and editing it was easy. All I'm doing in my global override below is adding a reReplace() call in between the column list building - another private method - and the prepending of the SELECT:

<cfscript>

	/**
	* I override the internal Wheels $selectClause() function, wrapping the dot-delimited
	* column aliases with a Microsoft SQL (MSSQL) escape notation "[column]". This fixes
	* a bug in which "include" logic creates SELECT statements with invalid column names.
	*/
	public string function $selectClause(
		required string select,
		required string include,
		boolean includeSoftDeletes = "false",
		required string returnAs
		) {

		// Core logic.
		var columnList = $createSQLFieldList(
			clause = "select",
			list = arguments.select,
			include = arguments.include,
			includeSoftDeletes = arguments.includeSoftDeletes,
			returnAs = arguments.returnAs
		);

		// PATCHING BUG: Escape any column alias that contains a ".".
		var escapedColumnList = columnList.reReplace(
			" AS (\w+(\.\w+)+)",
			" AS [\1]", // Wrap in brackets.
			"all"
		);

		return "SELECT #escapedColumnList#";

	}

</cfscript>

This function is being included in my /app/global/functions.cfm, which is what Wheels mixes-into the Global.cfc. And since the Wheels mixin mechanics won't override conflicting method names, it means my custom implementation of this private method will take precedence in the global namespace.

Note that my fix — bracket notation [column] — is Microsoft SQL Server (MSSQL) specific. If this were a MySQL application, I'd be using backtick notation `column`.

Want to use code from this post? Check out the license.

Short link: https://bennadel.com/4813