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

Question

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

If you have, bring the results of the title 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 above example, in a search for "abc" would like to bring first the ids 1 and 2 and lastly the 3, which does not have abc in the title, but has in the content. And as the second order criterion, put in alphabetical order, I can do only using 1 criterion:

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

Answer 1

I would make it look like what Arthur did, just by assigning the importance of the result to a value. For example:

• If there is result in the title assigns the result 2
• If there is result in content assigns to result 1
• Sum it all, and put it in descending order ( FROM THE HIGHEST RELEVANCE TO A MINOR)

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 "Ah, one more thing, there are items who have the term in the title and in the content, in this case, prioritize title"

Master if 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

Answer 2

You can use boolean values in the order, the expression is evaluated at 0 if the clause is false and 1 if true.

https://stackoverflow.com/questions/591608/using-boolean-expression-in-order-by-clause?rq=1

That way the Query looks like this:

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

ORDER BY (titulo LIKE '%abc%') ASC, titulo ASC