Search code examples
mysqlmatchranking

Trying to correct for MATCH ... AGAINST on top results

SELECT 
MATCH(`product_name`) AGAINST ('leica' IN BOOLEAN MODE) * 100 AS name,
MATCH(`product_category_name`) AGAINST ('leica' IN BOOLEAN MODE) * 50 AS category,
MATCH(`product_description`) AGAINST ('leica' IN BOOLEAN MODE) * 20 AS description 
FROM products  
WHERE MATCH (`product_name`, `product_category_name`, `product_description`) AGAINST ('leica' IN BOOLEAN MODE) 
ORDER BY (name)+(category)+(description) DESC LIMIT 0, 24

So this works great except in one case. When a product has 'leica' in both the name and the description, it gets bumped to the top. I would like the description to only factor in if it doesn't match in the product_name or product_category. I was thinking about adding * -5 to something, but I can't figure out how to get an accurate number to correct for what appears in the description without some kind of IF clause.

Solution

  • Don't add the values, use them as separate values to order by:

    ORDER BY name DESC, category DESC, description DESC

    Then you also don't need the multipliers.