I agree with Dave, as I popped in on this thread thinking you were using OracleText and my laziness has prevented me from writing a Google-to-OracleText query in PL/SQL. I was hoping you might have already.
I was able to generate an error when I searched on a NOT term without specifiying the term. (Just a '-'). It also throws an error if there are double quotes. -Adam On 10/11/05, Dave Carabetta <[EMAIL PROTECTED]> wrote: > On 10/11/05, Daniel Kessler <[EMAIL PROTECTED]> wrote: > > As y'all have read, I'm trying to replicate a google-like search on > > our database. I think that I have the CF and DB (Oracle) logic > > worked out but I'd like someone else to look it over and see if it's > > right. I want to search the same way that google does since most > > people are used to that. I want words separated by spaces, quoted > > text (a phrase) treated as a single word, + meaning that it must have > > the word and - that it must not contain the word. > > > > Here's the search page which allows for a search of a few different > > columns: > > http://hhp.umd.edu/people/people_search.cfm > > > > and here's the code that I use (and any improvements are welcome): > > > > > > <!--- > > before this, I make the three arrays described here: > > - search_ar: the OR search items > > - search_must: the AND "must contain" items, designated > > as + before the word > > - search_not: the items that "must not contain" in the > > returned results, designated as minus before the word > > ---> > > > > <CFQUERY NAME="people_search" DATASOURCE="dpch"> > > SELECT > > id,fname,lname,type,title,dept,phone,location,email,summary,imagePath,li > > nk > > FROM PEOPLE > > WHERE > > <cfloop from="1" to="#arrayLen(search_ar)#" index="ii"> > > UPPER(#PreserveSingleQuotes(the_search_column)#) > > LIKE UPPER('%#search_ar[ii]#%') > > <cfif ii LT arrayLen(search_ar)>OR</cfif> > > </cfloop> > > <cfif arrayLen(search_ar) GT 0 AND arrayLen > > (search_must) GT 0>AND</cfif> > > <cfif arrayLen(search_must) GT 0> > > ( > > <cfloop from="1" to="#arrayLen(search_must)#" > > index="jj"> > > UPPER(#PreserveSingleQuotes > > (the_search_column)#) LIKE UPPER('%#search_must[jj]#%') > > <cfif jj LT arrayLen(search_must)>AND</cfif> > > </cfloop> > > ) > > </cfif> > > <cfif (arrayLen(search_must) GT 0 OR (arrayLen > > (search_ar) GT 0 AND arrayLen(search_must) EQ 0)) AND arrayLen > > (search_not) GT 0>AND</cfif> > > <cfif arrayLen(search_not) GT 0> > > ( > > <cfloop from="1" to="#arrayLen(search_not)#" > > index="kk"> > > UPPER(#PreserveSingleQuotes > > (the_search_column)#) NOT LIKE UPPER('%#search_not[kk]#%') > > <cfif kk LT arrayLen(search_not)>AND</cfif> > > </cfloop> > > ) > > </cfif> > > > > <!--- ---> > > > > AND status = 'active' > > ORDER BY upper(lname) ASC > > </CFQUERY> > > > > It might have a Google "feel" from a UI standpoint, but your code > above will more than likely not scale at all. First, when you use > Oracle functions around column names such as: > > UPPER(column) = '#UCase(myvalue)#' > > Oracle will not use any indexes defined on that column, which means > it's going to do a full table scan for that value (translation: not > scalable). Second, LIKE queries, in general, should be avoided, > particularly for heavy search queries. You should really look into > using Oracle's Full Text Search capabilities, as the performance > difference can be dramatic. It's a little more difficult to implement > up front, but that effort will go a long way to saving your database. > Some might also say that you should be using cfqueryparam as well so > that bind parameters are used, but I've read in several places that > user-defined search queries like you have are actually *better off* > without bind parameters because you don't risk Oracle creating an > initial explain plan for a query based on one set of terms terms, only > to find out that another set of search terms should use a completely > different explain plan. > > I don't know what the traffic at that site is like, nor do I know the > number of records your searching against (or will search against in > the future), but I think you should do some more research into Full > Text Searching before committing to your current approach, as I think > you're setting yourself up for trouble, if not now, down the road. > > Regards, > Dave. > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220740 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
