Recherche plein texte avec Doctrine : créer sa propre fonction DQL pour MySQL

Toute application finit par avoir besoin d'une barre de recherche. La première version s'écrit en deux minutes avec un LIKE, et elle suffit souvent. Mais à mesure que le contenu grossit, ses limites apparaissent vite. Sur ce blog, j'ai remplacé le LIKE par la recherche plein texte de MySQL. Pour y arriver, il a fallu apprendre à Doctrine une fonction qu'il ne connaît pas. Voici comment.

Les limites du LIKE

La recherche la plus simple ressemble à ceci :

$qb->andWhere('a.title LIKE :search OR a.content LIKE :search')
    ->setParameter('search', '%' . addcslashes($search, '%_') . '%');

Elle fonctionne, mais :

• elle ne peut pas utiliser d'index : avec un % au début du motif, MySQL doit lire le contenu de chaque article, à chaque recherche ;
• elle ne sait pas trier par pertinence : un article qui contient le mot une fois dans son dernier paragraphe arrive au même rang qu'un article qui en parle dans son titre ;
• elle cherche une suite de caractères, pas des mots : chercher symfony doctrine ne trouve que les textes contenant exactement cette expression, dans cet ordre.

Pour quelques dizaines d'articles, ce n'est pas un problème. Pour quelques milliers, ça le devient.

L'index FULLTEXT

MySQL propose un type d'index dédié au texte : l'index FULLTEXT. Au lieu d'indexer une valeur entière, il découpe le texte en mots et garde, pour chaque mot, la liste des lignes qui le contiennent. C'est le principe d'un index à la fin d'un livre.

Avec Doctrine, on le déclare directement sur l'entité :

#[ORM\Entity(repositoryClass: ArticleRepository::class)]
#[ORM\Index(name: 'article_fulltext', columns: ['title', 'content'], flags: ['fulltext'])]
class Article

L'index porte sur les deux colonnes ensemble : la recherche se fera dans le titre et le contenu en une seule opération. La migration générée contient bien :

CREATE FULLTEXT INDEX article_fulltext ON article (title, content);

Le problème : Doctrine ne connaît pas MATCH

Pour interroger un index FULLTEXT, MySQL utilise une syntaxe particulière :

SELECT * FROM article
WHERE MATCH (title, content) AGAINST ('+symfony*' IN BOOLEAN MODE);

Or le DQL de Doctrine est volontairement indépendant de la base de données : il ne contient que ce que toutes les bases savent faire. MATCH ... AGAINST étant propre à MySQL, il n'existe pas en DQL. On pourrait écrire la requête en SQL brut, mais on perdrait l'hydratation des entités, les jointures du QueryBuilder et la pagination.

La bonne solution : ajouter nous-mêmes la fonction au DQL.

Créer une fonction DQL

Une fonction DQL personnalisée est une classe qui étend FunctionNode et qui a deux responsabilités : lire la syntaxe DQL, puis produire le SQL correspondant.

namespace App\Doctrine;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\Node;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;
use Doctrine\ORM\Query\TokenType;

/**
 * DQL : MATCH_AGAINST(a.title, a.content, :search)
 * SQL : MATCH (title, content) AGAINST (? IN BOOLEAN MODE)
 */
final class MatchAgainst extends FunctionNode
{
    /** @var list<Node> */
    private array $columns = [];

    private Node $needle;

    public function parse(Parser $parser): void
    {
        $parser->match(TokenType::T_IDENTIFIER);
        $parser->match(TokenType::T_OPEN_PARENTHESIS);

        do {
            $this->columns[] = $parser->StateFieldPathExpression();
            $parser->match(TokenType::T_COMMA);
        } while ($parser->getLexer()->isNextToken(TokenType::T_IDENTIFIER));

        $this->needle = $parser->StringPrimary();

        $parser->match(TokenType::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker): string
    {
        $columns = array_map(
            fn (Node $column) => $column->dispatch($sqlWalker),
            $this->columns,
        );

        return sprintf(
            'MATCH (%s) AGAINST (%s IN BOOLEAN MODE)',
            implode(', ', $columns),
            $this->needle->dispatch($sqlWalker),
        );
    }
}

La méthode parse() lit la fonction morceau par morceau : son nom, la parenthèse ouvrante, une ou plusieurs colonnes séparées par des virgules, le texte recherché, puis la parenthèse fermante. Si la syntaxe est incorrecte, Doctrine renvoie une erreur claire indiquant ce qu'il attendait.

La méthode getSql() produit le SQL. L'appel à dispatch() est essentiel : il laisse Doctrine traduire a.title en vrai nom de colonne, et :search en paramètre lié. On ne concatène donc jamais le texte de l'utilisateur dans la requête, ce qui nous protège des injections SQL.

Il reste à déclarer la fonction dans config/packages/doctrine.yaml :

doctrine:
    orm:
        dql:
            numeric_functions:
                MATCH_AGAINST: App\Doctrine\MatchAgainst

On la range dans numeric_functions car elle renvoie un nombre : le score de pertinence.

L'utiliser dans le repository

La fonction s'utilise maintenant comme n'importe quelle fonction DQL :

$qb->addSelect('MATCH_AGAINST(a.title, a.content, :search) AS HIDDEN score')
    ->andWhere('MATCH_AGAINST(a.title, a.content, :search) > 0')
    ->setParameter('search', $booleanQuery)
    ->orderBy('score', 'DESC')
    ->addOrderBy('a.publishedAt', 'DESC');

Deux détails :

• AS HIDDEN score calcule le score de chaque article sans l'ajouter au résultat. On récupère toujours de simples objets Article, comme avant ;
• on trie par ce score, puis par date à pertinence égale. Un article qui contient les mots dans son titre et plusieurs fois dans son contenu remonte en premier.

Préparer la saisie de l'utilisateur

Le mode booléen accepte des opérateurs : + rend un mot obligatoire, * en fait un préfixe, - l'exclut, les guillemets cherchent une expression exacte. C'est puissant, mais on ne peut pas transmettre la saisie de l'utilisateur telle quelle : un - ou un guillemet isolé donnerait des résultats inattendus, voire une erreur.

On nettoie donc la saisie, puis on construit nous-mêmes la requête booléenne :

private function toBooleanQuery(string $search): ?string
{
    $clean = preg_replace('/[+\-<>()~*"@]+/', ' ', $search);
    $words = preg_split('/\s+/', $clean, -1, PREG_SPLIT_NO_EMPTY);

    // InnoDB n'indexe pas les mots de moins de 3 caractères
    $words = array_filter($words, fn (string $word) => mb_strlen($word) >= 3);

    if (!$words) {
        return null;
    }

    return implode(' ', array_map(fn (string $word) => '+' . $word . '*', $words));
}

La saisie symf doct devient +symf* +doct* : les articles doivent contenir un mot commençant par « symf » et un mot commençant par « doct ». On obtient au passage la recherche par préfixe, très appréciée des utilisateurs qui ne tapent pas les mots en entier.

Quand la saisie ne contient que des mots trop courts (« JS », « IA »), la méthode renvoie null et le repository retombe sur l'ancien LIKE. La recherche fonctionne donc dans tous les cas.

Vérifier que l'index est utilisé

Le Profiler de Symfony permet de le vérifier en un clic : dans l'onglet Doctrine, le bouton « Explain query » affiche le plan d'exécution de MySQL. La ligne qui compte est celle-ci :

-> Full-text index search on a0_ using article_fulltext (title = '+dolo*')

MySQL utilise bien l'index, au lieu de parcourir le contenu de tous les articles.

Les pièges à connaître

• Les mots courts. Avec InnoDB, les mots de moins de 3 caractères ne sont pas indexés (réglage innodb_ft_min_token_size). D'où le retour au LIKE pour ces recherches.
• Les mots vides. MySQL ignore une liste de mots très fréquents, appelés stopwords. La liste par défaut est en anglais : pour un site en français, on peut définir sa propre liste avec le réglage innodb_ft_server_stopword_table.
• La portabilité. Cette fonction est propre à MySQL et MariaDB. Sur PostgreSQL, la recherche plein texte existe aussi, mais avec une syntaxe complètement différente (to_tsvector, to_tsquery).
• Les tests. Si vos tests tournent sur SQLite, cette requête échouera. Mieux vaut tester sur le même moteur que la production.

Et après ?

Pour un blog ou un catalogue de taille moyenne, la recherche plein texte de MySQL est un excellent compromis : aucun service supplémentaire à installer, des résultats triés par pertinence, et une vraie amélioration des performances. Si un jour vous avez besoin de la tolérance aux fautes de frappe ou de résultats qui s'affichent pendant la saisie, il sera temps de regarder du côté d'un moteur dédié comme Meilisearch.

Et surtout, vous savez maintenant ajouter n'importe quelle fonction SQL au DQL de Doctrine. La même technique vaut pour des fonctions de date, de géolocalisation ou de manipulation JSON.

Et vous, comment gérez-vous la recherche dans vos applications Symfony ? Dites-le en commentaire !