Mysql order by relevance and title-CodePudding

I have a sql query for a search feature that is a bit complex, but it works great.

SELECT
    SQL_CALC_FOUND_ROWS p.id as id,
    p.title as title,
    co.title as company,
    p.price as price,
    p.image_url as image_url FROM products p 
JOIN product_categories pc ON p.id = pc.product_id
JOIN categories cat ON pc.category_id = cat.id
JOIN companies co ON p.company_id = co.id

        WHERE MATCH(p.title) AGAINST("nalle" IN BOOLEAN MODE)
        OR MATCH(p.description) AGAINST("nalle" IN BOOLEAN MODE)
        OR MATCH(cat.title) AGAINST("nalle" IN BOOLEAN MODE)
        OR MATCH(co.title) AGAINST("nalle" IN BOOLEAN MODE)

    GROUP BY p.id
    ORDER BY p.title
    LIMIT :offset, :limit

Now it's ordered by product title but it should put the most relevant results first. It means that if it matches both p.title and p.description it is more relevant than if it only matches p.title. The best match would be to match all four.

Because of the complexity of the query, I don't know how I could calculate it.

CodePudding user response：

Each of the 4 MATCH() functions returns a boolean value which is evaluated as 1 or 0, so you can get the sum of all returned values and sort by that descending:

ORDER BY MATCH(p.title) AGAINST("nalle" IN BOOLEAN MODE)   
         MATCH(p.description) AGAINST("nalle" IN BOOLEAN MODE)  
         MATCH(cat.title) AGAINST("nalle" IN BOOLEAN MODE)  
         MATCH(co.title) AGAINST("nalle" IN BOOLEAN MODE) DESC

CodePudding user response：

how about using the relevance score. I found an example like:

SELECT table. * ,
MATCH (
col1, col2, col3
)
AGAINST (
' (Term1)  (Term1)'
) AS Score
FROM table
WHERE MATCH (
col1, col2, col3
) 
AGAINST (
' (Term1)  (Term1)'
)

I believe you can figure out the rest