3

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.

My guess it that I need SELECT ??? as points ... ORDER BY points.

Machavity
  • 30,841
  • 27
  • 92
  • 100
Jens Törnell
  • 23,180
  • 45
  • 124
  • 206

1 Answers1

0

Maybe you can try this if it's InnoDB, I am not sure for MyISAM : 

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,
    MATCH(p.title, p.description, cat.title, co.title)
         AGAINST("nalle" IN BOOLEAN MODE) as Relevance
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, p.description, cat.title, co.title) AGAINST("nalle" IN BOOLEAN MODE)

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

In the example here you can see how they calculate the score. If the word you are looking for is find in more field the score will be higher.

Daniel E.
  • 2,440
  • 1
  • 14
  • 24
  • My matches are using OR but your matches are grouped into one which means that they are now using AND. – Jens Törnell Apr 10 '18 at 13:11
  • I disagree, look the mysql ref, I have linked in my answer and here : https://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html – Daniel E. Apr 10 '18 at 14:26
  • I tried it and I get a parse error. #1064 - Parse error near 'MATCH(p.title, p.description, cat.title, co.title) AGAINST("nalle" IN BOOLEAN MO' on line 7. – Jens Törnell Apr 11 '18 at 05:45
  • I should have mentioned that saw the comma and fixed it and after that I got the error. (before I had another error related to the comma). By the way, I'm using innoDB. – Jens Törnell Apr 11 '18 at 11:01
  • No, the comma error generated another error message: `3 erros was found during analysis. An alias was found. (near "Relevance" at position 265) An alias was expected. (near " " at position 264) Unknown token. (near "Relevance" at position 265)` – Jens Törnell Apr 12 '18 at 07:23
  • The only difference I see between classic example and here is the simple quote / double quote in the Match. For example, here : https://stackoverflow.com/questions/1241602/mysql-match-across-multiple-tables – Daniel E. Apr 12 '18 at 07:53