2

For each rows, I want to get the relevance of each description compared to an undefined number of keywords. I know that "THEN +1" does not work, but I would like to come to this result (...to have a number (starting from 0 each rows) that is incremented for each keyword present)

SELECT *,
    (CASE description LIKE '%keyword1%' THEN +1 
          description LIKE '%keyword2%' THEN +1
          (...) 
          ELSE 0
    END) as relevance_description
FROM (...)
ORDER BY relevance_description DESC

So, if a description contains "keyword1" and "keyword2", relevance_description should be 2 for this row.

Ashish Gaur
  • 2,030
  • 2
  • 18
  • 32
Maxime R.
  • 133
  • 1
  • 9

1 Answers1

3

You can do this with separate clauses, and add them together:

SELECT *,
       ((CASE description LIKE '%keyword1%' THEN 1 else 0 end) +
        (case description LIKE '%keyword2%' THEN 1 else 0 end) +
        . . .
       ) as relevance_description
FROM (...)
ORDER BY relevance_description DESC;

In some databases, booleans are treated as integers, so you could just write:

SELECT *,
       ((description LIKE '%keyword1%') +
        (description LIKE '%keyword2%') +
        . . .
       ) as relevance_description
FROM (...)
ORDER BY relevance_description DESC;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, i get the same problem and solved it in Laravel directly in the builder. In case someone need it .. https://stackoverflow.com/a/53488440/3132123 – TiDJ Nov 26 '18 at 20:26