With either solution I hope you have good indexes.
On Thu, Jun 17, 2010 at 5:42 PM, Carl Von Stetten <cmvon...@hotmail.com>wrote: > > Che, > > How about this: > > 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"> > <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 searchterm NOT IN (<cfqueryparam > value="#application.cpSearchTerms#" > list="yes" > cfsqltype="cf_sql_varchar">) > ) > order by searchtermcount desc > > Also, I don't think you need the <cfqueryparam> on the numresults clause > because you are passing a constant value. > > Not tested, so buyer beware... > Carl > > >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:334632 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
