Why not perform your cflooping outside the SQL. This is much faster than
forcing the query to manage the loop parsing and excution:
<cfset xsearch = ''>
<cfloop list='#theModels#' index='i'>
<cfset xsearch = xsearch & " or searchterm like '%#i#%' ">
</cfloop>
<cfif listlen( application.cpSearchTerms)>
<cfset xsearch = xsearch & " and ( ">
<cfloop list="#application.cpSearchTerms#" index='i'>
<cfset xsearch = xsearch & " searchterm <> trim(i) ">
</cfloop>
<cfset xsearch = xsearch & ")">
</cfif>
(you'll need to cleanup the 'and' and 'or' to consider that the first time it
loops through to
build the search criteria)
select searchter, blahblahotherfields
from tablename
where searchtype = <cfqueryparam ....>
<cfif len(xsearch)>
and #xsearch#
</cfif>
limit #numresults#
order by searchtermcount desc
To limit records returned, depending on the database used, there may be some
other options...
MySql limits results by "limit xx' clause such as 'Select aa from bb limit xx'
SQL Server sets the limit as "top xx" such as 'Select top xx from....'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:334651
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
