Assuming you follow the philosophy of the MVC frameworks out there, you’ve likely come across Beans, Services, DAOs (data access objects), and Gateways. Without getting too much into detail about what each of these types of components do, which I’ll get into in a future blog post, your gateway layer is meant to interact with your database where you need to do more than just create, read, update, or delete objects (which is what a DAO is for).

Model-view-controller at Wikipedia

getMatching Method

The getMatching method is the core of most gateway objects. It’s a universal method to be called via your services which should allow optional elements and return query objects based on the arguments specified. One of the bigger hurdles is to figure out how to provide the optional attributes. Most developers may use cfargument tags such as:

<cfargument name="userID" required="false" type="numeric" default="-1" />

There are several issues that arise from defining your arguments this way. With a default value, the value will always be specified. So how would you go about returning all users in a specified table? Continuing the example above, you might be inclined to write a query like:

<cffunction name="getMatching" access="public" output="false" returntype="query">
	<cfargument name="userID" required="false" type="numeric" default="-1" />

	<cfset var userQuery = '' />

	<cfquery name="userQuery" datasource="#variables.dsn#">
		SELECT	userID,
				firstName,
				lastName,
				birthday
		FROM	Users
		WHERE	1 = 1
				<cfif arguments.userID neq -1>
					AND userID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.userID#" />
				</cfif>
	</cfquery>

	<cfreturn userQuery />
</cffunction>

For most cases this will work just fine (assuming you remember that -1 means return all users). What what happens when you need to check on a date? Do you specify a default value of say, January 1st, 1970? What if you need that date?

<cffunction name="getMatching" access="public" output="false" returntype="query">
	<cfargument name="userID" required="false" type="numeric" default="-1" />
	<cfargument name="birthday" required="false" type="date" default="#CreateDate(1970, 1, 1)#" />

	<cfset var userQuery = '' />

	<cfquery name="userQuery" datasource="#variables.dsn#">
		SELECT	userID,
				firstName,
				lastName,
				birthday
		FROM	Users
		WHERE	1 = 1
				<cfif arguments.userID neq -1>
					AND userID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.userID#" />
				</cfif>
				<cfif DateCompare(arguments.birthday, CreateDate(1970, 1, 1), 'd') neq 0>
					AND birthday = <cfqueryparam cfsqltype="cf_sql_date" value="#arguments.birthday#" />
				</cfif>
	</cfquery>

	<cfreturn userQuery />
</cffunction>

A Better Solution

Let’s get away from checking default values and rely on the arguments structure to determine whether values are requested or not. In ColdFusion, if you omit the default value, it essentially will enforce the value when it’s passed it, but will not be available if it’s not. This allows you to rely upon the StructKeyExists method to perform our cfif statements:

<cffunction name="getMatching" access="public" output="false" returntype="query">
	<cfargument name="userID" required="false" type="numeric" />
	<cfargument name="birthday" required="false" type="date" />

	<cfset var userQuery = '' />

	<cfquery name="userQuery" datasource="#variables.dsn#">
		SELECT	userID,
				firstName,
				lastName,
				birthday
		FROM	Users
		WHERE	1 = 1
				<cfif StructKeyExists(arguments, 'userID')>
					AND userID = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.userID#" />
				</cfif>
				<cfif StructKeyExists(arguments, 'birthday')>
					AND birthday = <cfqueryparam cfsqltype="cf_sql_date" value="#arguments.birthday#" />
				</cfif>
	</cfquery>

	<cfreturn userQuery />
</cffunction>

Summary

The getMatching method is a handy gateway function which returns a wide range of database content relying on a series of optional arguments. Writing this effectively will reduce pain later on and allow you to concentrate more on your service layer than what your gateways are supposed to be returning.

Tags: ColdFusion, gateway, intermediate, MVC

This entry was posted on Friday, April 30th, 2010 at 10:48 am and is filed under ColdFusion. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.