CODEWITHSUNDEEP
mysqlsqlmariadb
emmzee
emmzee

Reputation: 640

MySQL fulltext search only scores above zero

If I have a fulltext query like this:

SELECT title,
   MATCH(question) AGAINST ('string') AS q_score,
   MATCH(answer) AGAINST ('string') AS a_score,
   MATCH(keywords) AGAINST ('string') AS kw_score FROM helptable
   ORDER BY (4 * q_score) + (1 * a_score) + (2 * kw_score) DESC

That works, I get a relevancy ranked result.

However I don't want any results with a combined score of 0. But when I try to add something like:

   WHERE a_score > 0

It doesn't work ("#1054 - Unknown column 'a_score' in 'where clause'"). Is there a different way to do this? Thank you!

(MariaDB 10.4.10, InnoDB table)

Upvotes: 0

Views: 358

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

You can use a having clause:

SELECT title,
       MATCH(question) AGAINST ('string') AS q_score,
       MATCH(answer) AGAINST ('string') AS a_score,
       MATCH(keywords) AGAINST ('string') AS kw_score
FROM helptable
HAVING a_score > 0
ORDER BY (4 * q_score) + (1 * a_score) + (2 * kw_score) DESC;

MySQL extends the use of the HAVING clause so it can be used in non-aggregation queries. MySQL also allows the use of column aliases in the HAVING clause. Voila! This does what you want.

I'm not sure what you mean by "combined score", but you can add additional conditions as well:

HAVING a_score > 0 OR kw_score > 0

Upvotes: 1

Related Questions