How to do MYSQL search search for word in title and then in text and sort by title first

Question:

I have a table with title and content and I wanted to do a search to see if the term is in any of these fields.

If so, bring the title results first and then the content

tabela
id|titulo|conteudo
1 |abc   |oai poa
2 |aaa   |abc
3 |abc   |xcvv
4 |ttt   |rt d

in the example above, in a search for "abc" would like to bring first ids 1 and 2 and finally 3 , which does not have abc in the title, but in the content. And as a second order criterion, put in alphabetical order, I know how to do it only using 1 criterion:

SELECT titulo
FROM tabela
WHERE titulo LIKE '%abc%'
ORDER BY titulo ASC

Answer:

I would do similar to what Arthur did, only assigning the importance of the result to a value. For example:

  • If there is a result in the title, attribute it to the result 2
  • If there is a result in the content, it assigns the result 1
  • Add it all up, and put it in descending order (FROM HIGHEST RELEVANCE TO SMALLEST)

Select would look like this:

SELECT
   titulo, id
FROM
   tabela
WHERE 
    titulo   LIKE '%abc%' 
 OR conteudo LIKE '%abc%' 

ORDER BY (
    CASE WHEN `titulo` LIKE '%abc%'
    THEN 2 # título é o mais importante então o valor é 2
    ELSE 0
    END
  ) + (
    CASE WHEN `conteudo` LIKE '%abc%'
    THEN 1 # conteúdo é o segundo mais importante então o valor é 1
    ELSE 0
    END
  ) DESC;

After the comment "Oh, one more thing, there are items that have the term in the title and in the content, in this case, prioritize the title"

In this case you can do something like this:

SELECT
   titulo, id
FROM
   tabela
WHERE 
    titulo   LIKE '%abc%' 
 OR conteudo LIKE '%abc%' 

ORDER BY (
    CASE WHEN `titulo` LIKE '%abc%' AND `conteudo` NOT LIKE '%abc%' 
    THEN 2 # título é o mais importante então o valor é 2
    ELSE 0
    END
  ) + (
    CASE WHEN `conteudo` LIKE '%abc%' AND `titulo` NOT LIKE '%abc%' 
    THEN 1 # conteúdo é o segundo mais importante então o valor é 1
    ELSE 0
    END
  ) + (
    CASE WHEN `conteudo` LIKE '%abc%' AND `titulo` LIKE '%abc%' 
    THEN 2 # valor é 2 pois a relevância é o título
    ELSE 0
    END
  ) DESC
Post Views: 0
Scroll to Top