How to order query in MYSQL by relevance? (first results that the term is in the title, then in the content)

Asked

Viewed 71 times

-1

I have a table as follows:

id|nota|titulo       |ingredientes
1 | 3  |pão de batata|batata;farinha;agua;ovo
2 | 2  |bacalhoada   |bacalhau;batata;pimentão
3 | 1  |batata frita |batata

When I do a search that contains the term 'potato" I want him to bring these three results, but first bring everyone who has potato in the title, then in descending order of the note, the criteria would be like this:

  1. Contains potato in the title
  2. Contains potato in the ingredient
  3. Sort in descending order of note

In this example, the result would be

  1. Potato bread
  2. French fry
  3. Codfish
mysql select order-by

  • What you want is to define a ranking by relevance, and you can achieve this by using an index fulltext for both columns

  • You can sort based on the result of the 'like'. Maybe that’s what you are looking for: https://answall.com/questions/205331/como-order-pela-relev%C3%A2ncia-do-like and also: https://stackoverflow.com/questions/11144394/order-sql-by-strost-like

  • Possible duplicate of How to sort by Like Relevance?

1 answer

3

Basic case of use of ORDER BY

... sua query ...

ORDER BY titulo       LIKE "%batata%" DESC, 
         ingredientes LIKE "%batata%" DESC, 
         nota DESC

This works simply because the LIKE return bool (zero or one), and the DESC cause the results 1 (found) come first

If the criteria are simple, there’s no need to mess with the structures. If it’s not quite the way you were asked, search for Full Text Search right here on the site (but for your example is something like killing dove with ICBM).

Browser other questions tagged mysql select order-by

You are not signed in. Login or sign up in order to post.