Multiple LIKE in sqlite

Question

I'm trying to create a search function.

If the search input field is "foo bar", I split it into two keywords then do this query:

SELECT p.* FROM p_extra_fields as x INNER JOIN products as p ON x.product = p.id
  WHERE x.type = "1"
  AND
  (
     (x.key = "model" AND x.value LIKE "%foo%")
  OR (x.key = "model" AND x.value LIKE "%bar%")
  OR (x.key = "color" AND x.value LIKE "%foo%")
  OR (x.key = "color" AND x.value LIKE "%bar%")
  OR (x.key = "make" AND x.value LIKE "%foo%")
  OR (x.key = "make" AND x.value LIKE "%bar%")
  )      

GROUP BY x.product LIMIT 0, 50

The number of keywords may be higher so I might need more "likes". Also the number of "key" can increase :)

Is there any way I could simplify this query? Can I do something like LIKE("%foo%", "%bar%") ?

Answer 1

If you have SQLite FTS3 and FTS4 Extensions enabled then you can take advantage of Full Text Search (FTS) capabilities. You will need to recreate the p_extra_fields table as a VIRTUAL table. Then you can insert OR between your search terms and use the MATCH operator...

SELECT p.* 
FROM p_extra_fields x
JOIN products p ON p.id = x.product
WHERE x.key IN ('model', 'color', 'make')
AND x.type = '1'
AND x.value MATCH 'foo OR bar'
GROUP BY x.product LIMIT 0, 50;

Good info here also. Click here to see it in action at SQL Fiddle.