MySQL Full-text Search (pesquisa) com order by relevância

Hoje trago pra vocês uma dica sobre o MySQL Full-text Search in Boolean Mode. Não sei se todos sabem ou já perceberam, mas como está escrito na própria documentação do MySQL, quando fazemos uma consult Full-text in Boolean Mode o mesmo não retorna os resultados ordenados por relevância, o que é chato para o usuário. Esses dias enquanto estava montando a pesquisa do www.usersbase.com acabei precisando encontrar uma forma de retornar por relevância. Depois de algumas pesquisas consegui encontrar a solução, aproveitei e implementei aqui no MNDTI que também usa full-text in boolean mode.

Mas então como resolver isso? É simples, se você tem apenas um tabela e vamos considerar que você tem um campo chamado titulo, a query ficaria assim:

SELECT titulo, MATCH(titulo) AGAINST ('{$termo}' IN BOOLEAN MODE) AS relevancia FROM ".SQL_PREFIXO."noticias WHERE MATCH(titulo) AGAINST('{$termo}' IN BOOLEAN MODE) ORDER BY relevancia DESC

Com php:

$SQL->Query("SELECT titulo, MATCH(titulo) AGAINST ('{$termo}' IN BOOLEAN MODE) AS relevancia FROM ".SQL_PREFIXO."noticias WHERE MATCH(titulo) AGAINST('{$termo}' IN BOOLEAN MODE) ORDER BY relevancia DESC");

Esse sql->query é da minha classe, você deve adaptar ao seu site ou sistema.

Se você quiser consultar mais campos?

$SQL->Query("SELECT titulo,descricao, MATCH(titulo,descricao) AGAINST ('{$termo}' IN BOOLEAN MODE) AS relevancia FROM ".SQL_PREFIXO."noticias WHERE MATCH(titulo,descricao) AGAINST('{$termo}' IN BOOLEAN MODE) ORDER BY relevancia DESC");

Ok, mas se quisermos consultar em duas tabelas por exemplo?
Vamos imaginar que temos uma tabela de notícias e na outra os dados dessa notícia.

$SQL->Query("SELECT n.id,n.titulo,nd.id,nd.descricao, MATCH(n.titulo,nd.descricao) AGAINST ('{$termo}' IN BOOLEAN MODE) AS relevancia  FROM ".SQL_PREFIXO."noticias AS n, ".SQL_PREFIXO."dados AS nd  WHERE MATCH(n.titulo,nd.descricao) AGAINST('{$termo}' IN BOOLEAN MODE) AND n.id = nd.id ORDER BY relevancia DESC");

Na verdade este exemplo se aplicaria melhor em uma tabela por exemplo de usuários e na outra tabelas os dados. Onde na tabela usuarios e consultaria por exemplo o e-mail e na segunda o nome e sobrenome. Agora basta você adaptar e usar a imaginação.

Essa constante SQL_PREFIXO é uma prefixo que uso, você deverá remover.

Veja a documentação sobre fulltext