Take the following query. Is there a more efficient way to handle the where
clause? Specifically, the last two 'and' statements that loop through two
different lists, one that includes results and one that excludes results.
Thanks, Che
select searchterm, searchtype, numresults, timestamp
from usersearch
where searchtype = <cfqueryparam value="#arguments.searchtype#"
cfsqltype="cf_sql_smallint">
and numresults <> <cfqueryparam value="0"
cfsqltype="cf_sql_smallint">
and (
<cfloop index="i" list="#theModels#">searchterm like <cfqueryparam
value="%#trim(i)#" cfsqltype="cf_sql_varchar" maxlength="255"><cfif i neq
listlast(theModels)> or </cfif></cfloop>
)
and (
<cfloop index="i" list="#application.cpSearchTerms#">searchterm <>
<cfqueryparam value="#trim(i)#" cfsqltype="cf_sql_varchar"
maxlength="255"><cfif i neq listlast(application.cpSearchTerms)> and
</cfif></cfloop>
)
order by searchtermcount desc
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Order the Adobe Coldfusion Anthology now!
http://www.amazon.com/Adobe-Coldfusion-Anthology-Michael-Dinowitz/dp/1430272155/?tag=houseoffusion
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:334624
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
