Multiple LIKE in sqlite

Question

I am trying to create a search function.

If the search input field is "foo bar", I split it into two keywords and ran the following 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 may need more “like”. Also, the number of "key" may increase :)

Can this query be simplified? Can I do something like LIKE("%foo%", "%bar%") ?

Answer 1

If you have SQLite FTS3 and FTS4 extensions , you can take advantage of Full Text Search (FTS) features. You will need to recreate the p_extra_fields table as the VIRTUAL table. You can then insert OR between your search queries 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 . Click here to see it in action in SQL Fiddle.

Answer 2

I think this where clause is simpler:

  WHERE x.type = "1" and x.key in ('model', 'color', 'make') and (x.value like '%foo%' or x.value like '%bar%') 

Answer 3

I had the same requirement, and I was looking for a mechanism that would match as REGEXP "A|B|C" , which means matching A , B , C

So finally, this is the solution I came across:

 WITH words(str, strSubString, hasComma) AS ( VALUES ('', "foo,bar", 1) UNION ALL SELECT SUBSTR(strSubString, 0, CASE WHEN INSTR(strSubString, ',') THEN INSTR(strSubString, ',') ELSE LENGTH(strSubString) + 1 END), LTRIM(SUBSTR(strSubString, INSTR(strSubString, ',')), ','), INSTR(strSubString, ',') FROM ssgPaths WHERE hasComma ) SELECT p.* FROM p_extra_fields as x INNER JOIN products as p ON x.product = p.id JOIN words AS w ON x.value LIKE '%' || w.str || '%' AND w.str != '' WHERE x.type = "1" and x.key in ('model', 'color', 'make'); 

Matching criteria are equivalent to @Gordon's query:

 WHERE x.type = "1" and x.key in ('model', 'color', 'make') and (x.value like '%foo%' or x.value like '%bar%') 

But this gives you the flexibility to map values ​​dynamically depending on your query parameter (you can extract "foo,bar" for the parameter).

For example, without changing the query, you can go through "foo,bar,boo" and match the regular expressions: "foo|bar|boo"