ColdFusion DAO Dynamic Finder Example

dynamic-finder.cfm

<cffunction name="onMissingMethod" access="public" returntype="query" output="false" hint="Dynamic finder.">     
    <cfargument name="missingMethodName" type="string" required="true" />
	<cfargument name="missingMethodArguments" type="struct" required="true" />
    	
    <cfset var searchFields = [] />
	<cfset var qRead = "" />
	<cfset var searchItem = "" />
	<cfset var i = 0 />
	<cfset var searchValue = "" />
    	
	<cfif ARGUMENTS.missingMethodName contains "findBy">
		
		<!--- we have a dynamic finder - delimit fields by 'and' and convert to an array --->
		<cfset searchFields = listToArray(replaceNoCase(lCase(ARGUMENTS.missingMethodName), "findby", ""), "and", false, true) />
		
		<!--- check for max number of dynamic finder fields --->
		<cfif arrayLen(searchFields) gt 4>
			<cfthrow message="Dynamic Finder: Max of 4 dynamic finder fields exceeded." />
		</cfif>
		
		<!--- first check this field has a match in the list of field names --->
		<cfloop array="#searchFields#" index="LOCAL.searchItem">
			
			<cfif not listFindNoCase(VARIABLES.allFieldNames, LOCAL.searchItem)>
				<cfthrow message="Dynamic Finder: '#LOCAL.searchItem#' is not a valid field name." />			
			</cfif>
			
		</cfloop>
		
		<!--- run query --->
		<cfquery name="qRead" datasource="_DATASOURCE_">
			
			select *
			from Customers
			
			<cfset i = 1 />
			<cfloop array="#searchFields#" index="searchItem">
				
				<!--- check for a named search param, or else by argument number --->
				<cfif structKeyExists(ARGUMENTS.missingMethodArguments, searchItem)> 
					<cfset searchValue = ARGUMENTS.missingMethodArguments[searchItem] />
				<cfelseif structKeyExists(ARGUMENTS.missingMethodArguments, i)>
					<cfset searchValue = ARGUMENTS.missingMethodArguments[i] />	
				<cfelse>	
					<cfthrow message="Dynamic Finder: You appear to have an incorrect named parameter for dynamic finder: #ARGUMENTS.missingMethodName#()." />	
				</cfif>
				
				<cfif i eq 1>
					where
				<cfelse>
					and
				</cfif>
				
				#searchItem# = 
				<cfif isDate(searchValue)>
					<cfqueryparam value="#searchValue#" cfsqltype="cf_sql_timestamp"  />
				<cfelse>
					<cfqueryparam value="#searchValue#" cfsqltype="cf_sql_varchar"  />
				</cfif>
				
				<cfset i++ />
			</cfloop>
			
			order by CustomerID desc
		
		</cfquery>
		
		<cfreturn qRead />
		
	<cfelse>
		<cfthrow type="Missing Method" message="Missing method '#ARGUMENTS.missingMethodName#()'." />		
	</cfif>

</cffunction>