Classement de recherche en texte intégral PostgreSQL par position

Récemment, j'ai rencontré un problème avec la recherche en texte intégral. J'utilise cette fonctionnalité dans ma saisie de recherche, où le backend envoie des indices de correspondances possibles au fur et à mesure que vous tapez. La base de données principale est PostgreSQL. J'avais besoin que les indices soient classés selon la position du terme recherché dans le texte.

Donc, si vous recherchez le titre "Star Wars", vous obtiendrez le message "Star Wars" en premier au lieu de "Comment Star Wars 7-9 a changé le monde de Star Wars (un documentaire amusant sur Star Wars)" qui peut avoir classement plus élevé puisque le terme est présent 3 fois.

Recherche en texte intégral dans PostgreSQL

La recherche en texte intégral dans PostgreSQL peut être réalisée assez facilement. Il existe deux outils principaux à utiliser :

• tsvector - représente un document consultable.
• tsquery - représente la requête de recherche à effectuer sur un document.

Disons que nous souhaitons rechercher les titres de nos articles de blog. Pour les rendre consultables, nous pouvons utiliser la requête suivante :

SELECT 
id, 
title 
FROM blogposts
WHERE to_tsquery('JavaScript') @@ to_tsvector(posts.title);

Dans ce cas, nous convertissons dynamiquement les titres des articles en tsvecteur à chaque recherche. Cependant, cette transformation prend du temps. Une meilleure approche consiste à effectuer cette transformation à l'avance dans la base de données et à la stocker également sous forme d'index pour les titres pour une recherche plus rapide.

Créons une nouvelle colonne de vecteurs de titres et indexons également cette nouvelle colonne :

ALTER TABLE blogposts ADD COLUMN search_vector tsvector;
UPDATE blogposts SET search_vector = (to_tsvector(posts.title));
CREATE INDEX titles_fts_idx ON blogposts USING gin(search_vector);

Essayez maintenant de rechercher le terme "JavaScript"

SELECT 
id, 
title
FROM blogposts
WHERE to_tsquery('JavaScript') @@ search_vector;

Vous pouvez également créer des index à partir des vecteurs ts directement sur la colonne des titres comme ceci :

CREATE INDEX titles_fts_idx ON blogposts USING GIN (to_tsvector(posts.title));

et utilisez la recherche comme ceci :

SELECT 
id, 
title
FROM blogposts
WHERE to_tsquery('JavaScript') @@ posts.title;

Désormais, la recherche en texte intégral sera incroyablement rapide, s'effectuant en quelques millisecondes.

Classement des résultats

PostgreSQL fournit la fonctionnalité ts_rank, qui vous permet d'évaluer les résultats de recherche et de les classer en fonction de leur classement. PostgreSQL prend en charge les options de classement suivantes :

• 0 (valeur par défaut) ignore la longueur du document
• 1 divise le rang par 1 le logarithme de la longueur du document
• 2 divise le rang par la longueur du document
• 4 divise le rang par la distance harmonique moyenne entre les étendues (ceci n'est implémenté que par ts_rank_cd)
• 8 divise le rang par le nombre de mots uniques dans le document
• 16 divise le rang par 1 le logarithme du nombre de mots uniques dans le document
• 32 divise le rang à lui seul 1

Vous pouvez utiliser le ts_rank comme ceci :

SELECT
    ...
ts_rank(search_vector, to_tsquery('JavaScript'), 0) as rank_title
    ...
ORDER BY rank_title DESC NULLS LAST

Cependant, il n'existe pas d'option de classement intégrée basée sur la position du terme de recherche dans la chaîne (c'est-à-dire la colonne de titre).

POSITION à la rescousse

Heureusement, il existe la fonction POSITION dans PostgreSQL. La fonction PostgreSQL POSITION est utilisée pour trouver l'emplacement d'une sous-chaîne dans une chaîne donnée. Dans notre cas nous pouvons l'utiliser comme ça

SELECT 
id, 
title 
FROM blogposts
WHERE to_tsquery('JavaScript') @@ to_tsvector(posts.title);

ts_rank utilise l'entier de normalisation 2 car 2 divise le classement par la longueur du document
Le nombre magique 0,0001 consiste à éviter de diviser par 0 car la fonction POSTION compte à partir de 1 et non de 0 et renvoie 0 si la chaîne n'est pas trouvée.

Le code final peut ressembler à ceci :

ALTER TABLE blogposts ADD COLUMN search_vector tsvector;
UPDATE blogposts SET search_vector = (to_tsvector(posts.title));
CREATE INDEX titles_fts_idx ON blogposts USING gin(search_vector);

Rechercher plus de termes

Une mise en garde doit être mentionnée si vous recherchez plusieurs termes à la fois (comme JavaScript et TypeScript).

Les arguments de la fonction to_tsquery peuvent être utilisés avec une grande flexibilité, y compris les opérateurs logiques, etc. La fonction POSITION, par contre, est "juste" une sous-chaîne dans une chaîne.

Exemple du monde réel

Voici mon exemple d'un point de terminaison du monde réel dans l'application Web SvelteKit qui utilise la bibliothèque npm postgres (sql) :

SELECT 
id, 
title
FROM blogposts
WHERE to_tsquery('JavaScript') @@ search_vector;

Voici les liens vers la documentatio en la matière :

• https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-PARSING-DOCUMENTS
• https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-RANKING
• https://www.postgresql.org/docs/9.1/functions-string.html

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!