Équivalent du MATCH AGAINST de MySQL sur PostgreSQL

Le blog est propulsé sur un système de gestion de contenu écrit sur Symfony. Les données sont gérées dans une base MariaDB et ça tourne très très bien :)

Pour apprendre à utiliser PostgreSQL, je me suis donné comme défi de rendre compatible ce blog avec PostgreSQL. Fort heureusement, j'ai un ORM et 90% du boulot est géré par 3 lignes de configuration.

Le moteur de recherche est un peu plus compliqué à migrer puisque j'ai généré des requêtes en dehors de l'ORM. Son fonctionnement est relativement standard car quand un utilisateur saisi des mots clés, une première requête SQL va donner un score aux articles du blog publiés et je vais afficher ceux qui dépassent une valeur donnée.

Pour ce faire, j'utilise MATCH AGAINST de MySQL/MariaDB et la requête donne ça :

SELECT
    post.id,
    post.title,
    post.tags,
    MATCH(post.title) AGAINST(:search) AS MATCH_TITLE,
    MATCH(post.content) AGAINST(:search) AS MATCH_CONTENT,
    MATCH(post.tags) AGAINST(:search) AS MATCH_TAGS
FROM post
WHERE
    post.active = 1 AND
    post.published_at < :date
ORDER BY
    MATCH_TITLE DESC,
    MATCH_TAGS DESC,
    MATCH_CONTENT DESC

Pour obtenir des résultats équivalents avec PostgreSQL, la requête doit changer car MATCH AGAINST n'existe pas et comme PostgreSQL offre des outils beaucoup plus complets, c'est moins évident. Je trouve d'ailleurs que la documentation est assez peu claire à ce sujet. J'ai mis du temps à pondre une requête qui fonctionnait. La voici :

SELECT
    post.id,
    ts_rank(to_tsvector(post.title), query) as match_title,
    ts_rank(to_tsvector(post.tags), query) as match_tags,
    ts_rank(to_tsvector(post.content), query) as match_content
FROM
    post,
    plainto_tsquery(:search) query
WHERE
    post.active = true AND
    post.published_at < :date
ORDER BY
    match_title DESC,
    match_tags DESC,
    match_content DESC

Dans les 2 cas, :search correspond aux mots clés et :date représente la date où la recherche est faite.

Les scores ne sont pas du même ordre de grandeur mais je retrouve des résultats équivalents sur les 2 moteurs de base de données.