Faceted Search in Coldfusion and SQL?

Question

Hi guys,

I'm working on a faceted search in Coldfusion and SQL. I've tried creating a query like this:

SELECT * FROM Turbos 
WHERE
  PartNumber LIKE '%#trim(SearchCriteria)#%' 
  OR PartDescription LIKE '%#trim(SearchCriteria)#%' 
  AND (PumpingSpeed BETWEEN #minimum# AND #URL.speed#) 
  AND InletFlange LIKE '#URL.inlet#' 
  AND Bearing LIKE '#URL.bearing#' 
  AND Condition LIKE '#URL.condition#'

The problem is the server is returning rows that don't contain EVERY piece of data I'm supplying. How can I select ONLY those rows which contain all the criteria?

Answer 1

Just a guess really, but change

...'%#trim(SearchCriteria)#%' OR PartDescription ...

to

...'%#trim(SearchCriteria)#%' AND PartDescription ...

edit
or are you saying some have null values?

post comment edit
Imagine for example that InletFlange is empty for your part, and the user didn't put it in their search.
Then InletFlange LIKE '#URL.inlet#' will compare "" LIKE "", which is of course true, so the product shows up.

There are many ways to solve this. For example

1. Default the search criteria to "N/A" or something
2. Default the column in the database in a similar fashion.
3. something along the lines of AND NOT ISNULL(InletFlange,'')=='' for each criteria

Answer 2

just wrap the OR bit in brackets:

(PartNumber LIKE '%#trim(SearchCriteria)#%' OR PartDescription LIKE '%#trim(SearchCriteria)#%') AND...

at the moment you have A or B and C which is being read as A or (B and C). You want (A or B) and C.

And make sure you use cfqueryparam as suggested above.

Answer 3

Okay, I found the answers:

Leigh: +1 to clarifying the question. (It is unlikely this has any bearing on the results, but what is the point of using LIKE without wildcards "%"? If it is an equality comparison you are after, just use equals ie ... AND Condition = '#URL.condition#' )

You were quite right, I was missing the percent symbols in my LIKE comparisons (DUH!). I removed them earlier thinking they were the problem, but they weren't, this was:

Aiden: just wrap the OR bit in brackets:

(PartNumber LIKE '%#trim(SearchCriteria)#%' OR PartDescription LIKE '%#trim(SearchCriteria)#%') AND...

Thank you Aiden!

Answer 4

While this isnt an answer to your question - you might want to consider using a dedicated search solution such as Solr (which is included with CF9). Its a more powerful fulltext solution than just raw SQL and includes facetting