10

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%") ?

MPelletier
  • 16,256
  • 15
  • 86
  • 137
Anna K.
  • 1,887
  • 6
  • 26
  • 38
  • After a lot of reading I was able to do what you need without installing REGEXP imp or FTS3 and FTS4 extensions, check my solution – dsharew Mar 01 '18 at 11:23

3 Answers3

7

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.

davmos
  • 9,324
  • 4
  • 40
  • 43
  • so i have to make a virtual table that's a copy of my `p_extra_fields` table? – Anna K. Aug 24 '13 at 23:53
  • Nice, after some research was able to do it without using ```regex``` and ```match```, check my solution :) – dsharew Mar 01 '18 at 11:24
  • Important note: LIKE("%foo%") searches for words that CONTAIN 'foo'; MATCH 'foo' searches for words that ARE EQUAL to 'foo'. As far as I know, with FTS you cannot search for words that contain another word – Alex Busuioc Oct 07 '21 at 15:05
3

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%')
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I had same requirement and I was looking for a mechanism that would a match like REGEXP "A|B|C" which means match either A, B, C.

So finally this is the solution I came up with:

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');

The matching criteria is equivalent to @Gordon's answer:

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 of matching the values dynamically depending on your query parameter ( you can extract out "foo,bar" to be a parameter).

E.g without changing your query you can pass "foo,bar,boo" and till match similar to regex match: "foo|bar|boo"

dsharew
  • 10,377
  • 6
  • 49
  • 75