Can I adjust calculated MATCH scores?

I’m trying to increase the MATCH score if entries in the database have more than 200 words. Am I getting close with my query? Is this even possible what I’m trying to do?


length(body) - length(replace(body, ' ', ''))+1 as totalWords,
MATCH (title,body,keywords) AGAINST ('shoulder') AS score,
CASE WHEN (length(body) - length(replace(body, ' ', ''))+1 > 200) THEN  score +1 ELSE 0 END as adjustedScore

The normal strategy when you wish to use a correlation name in other expression is to push the query into a derived table.


select totalWords,
       case when totalWords > 200 then score + 1 else 0 end as adjustedScore
  from (select char_length(body) - char_length(replace(body,' ','')) + 1 as totalWords,
               match(title,body,keywords) against('shoulder') as score
          from <yourOriginalFromClause>) dt

Thanks swampBoogie!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.