É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.

Murph v1.23 et Custom Menu v3.11

Murph v1.23 et Custom Menu v3.11

Je n'ai pas écrit d'articles depuis quelques temps car mes semaines sont très chargées. Pour…

Murph v1.20 is out! 📣

Murph v1.20 is out! 📣

Une nouvelle version de Murph a été publiée le 27 juillet 2023 🥳 Murph est un framework ope…

Capture, un reverse proxy pour analyser les requêtes de vos applications

Capture, un reverse proxy pour analyser les requêtes de vos applications

Capture est un reverse proxy HTTP qui se place entre votre application cliente et une API. C…


Ajouter un commentaire

Votre commentaire - Vous pouvez utiliser du markdown

Renouveler