CODEWITHSUNDEEP
mysqlsqlfull-text-search
Yekver
Yekver

Reputation: 5185

Sort in multiple table Match Against

I'm using query such like this one:

SELECT p.name, d.overview
FROM products AS p 
LEFT JOIN descriptions AS d ON p.DescriptionID = d.ID 
WHERE MATCH (p.name) AGAINST ('ram' IN BOOLEAN MODE) > 0 
OR MATCH (d.overview) AGAINST ('ram' IN BOOLEAN MODE) > 0;

How to make relevant sorting of this results?

I've tried 

ORDER BY MATCH (p.name) AGAINST ('ram' IN BOOLEAN MODE) 
OR MATCH (d.overview) AGAINST ('ram' IN BOOLEAN MODE) DESC

But this does't help coz result's is not sorting according to their relevancy.

Upvotes: 0

Views: 454

Answers (1)

Andomar
Andomar

Reputation: 238086

MATCH ... OR MATCH ...
          ^^

The or makes this a binary expression. Binary expressions evaluate to true or false. So you're sorting on whether they match or not. Because the where clause already ensures all rows match, this not not add ordering.

Try to order by an integer expression, like the summed relevancy:

ORDER BY
    MATCH (p.name) AGAINST ('ram' IN BOOLEAN MODE) +
    MATCH (d.overview) AGAINST ('ram' IN BOOLEAN MODE) DESC

Upvotes: 1

Related Questions