prisma with fts

Полный, нечеткий текстовый поиск с Postgres и Prisma

В какой -то момент в своей жизни программного обеспечения вы, возможно, слышали фразу «нам нужно создать функцию поиска для этого». Большие наборы данных требуют, чтобы можно было поиск. Вы можете добавить к нему фильтры, но конечный пользователь всегда захочет напечатать lrd of t и получить стипендию кольца в качестве первого результата. Вы, вероятно, подумали об использовании какого -то причудливого стороннего решения, которое выглядит многообещающим, вы просто укажите на базу данных, и поиск каким -то образом обрабатывается. Но что, если, с небольшим количеством SQL, вы можете добраться до того же результата или даже лучше?

Основы Postgres для полного текстового поиска

Когда дело доходит до анализа текста или майнинга, есть несколько различных концепций, которые полезны для того, как отправиться в прекрасное путешествие полного текстового поиска. Мы можем начать с определения документа . Документ - это то, что вы, вероятно, уже знаете, набор предложений после какой -либо структуры, написанного на определенном языке. El Cantar Del Mío CID - это документ, а Sinopsis фильма 1930 года также является документом. Когда дело доходит до Postgres, документы можно найти в одном или многих других столбцах. Документы обычно проанализируются в токены , которые могут быть словами и фразами, из которых мы можем получить лексы , значимые единицы текста.

Postgres берет из них документы и анализы, используя словари . Существует словарь по умолчанию, языковые словаря, и вы даже можете предоставить свои собственные. Правда в том, что если вы знаете, что ваш документ находится на немецком языке, вы, скорее всего, захотите использовать немецкий словарь для анализа ваших лексиков. Используя определенные словаря, вы можете получить лучшие лексы, специфичные для корней и этимологий и этимологии Word.

Познакомьтесь с tsvector

tsvector -это встроенный тип данных в Postgres ¹ , который представляет собой отсортированный список различных, нормализованных лексиков. Если мы рассмотрим следующую модель Prisma

 model Movie {
  id        String  @ id @ default ( cuid ( ) )
  title     String
  year      Int
  extract   String ?
  thumbnail String ?
  genre     String ?

  createdAt DateTime @ default ( now ( ) )
  updatedAt DateTime @ updatedAt
}

Мы могли бы придумать функцию поиска, основанную на извлечении фильма. К сожалению, на сегодняшний день Приста не имеет поддержки tsvector ² . Тем не менее, можно использовать декоратор @unsupported и отправиться в красивый сырой SQL.

 model Movie {
  id        String  @ id @ default ( cuid ( ) )
  title     String
  year      Int
  extract   String ?
  thumbnail String ?
  genre     String ?

  createdAt DateTime @ default ( now ( ) )
  updatedAt DateTime @ updatedAt

  search Unsupported ( "tsvector" ) ? @ default ( dbgenerated ( "''::tsvector" ) )
}

Я знаю, что если вы используете Prisma, вы, вероятно, не очень любите SQL, но как только вы попытаетесь сделать что -то немного на заказ или достичь продуктивной стадии, вы найдете любое ограничение ORM.

Генерирование векторов из столбцов

Хорошо, теперь у нас есть хороший векторный столбец для полного текстового поиска. С помощью простого сценария SQL мы можем заполнить этот столбец:

 ALTER TABLE " Movie "
SET search = to_tsvector( ' english ' , extract)

to_tsvector -одна из многих встроенных функций в Postgres ³ , которая займет ваш документ (и дополнительный словарь) и создаст для него вектор. Однако что произойдет, если выдержка будет обновлен? Что если я добавлю новый ряд? Ну, нам нужно пересчитать. А какая хорошая возможность для сгенерированных колонн ⁴ чтобы сиять, верно? С SQL вы бы сделали следующее.

 ALTER TABLE " Movie " ADD COLUMN search tsvector
GENERATED ALWAYS AS (to_tsvector( ' english ' , extract)) STORED;

Но жизнь не так проста, потому что мы используем Prisma. Несмотря на то, что команда Prisma предоставляет нам npx prisma migrate dev --create-only , способ возиться с SQL, сгенерированным в миграциях, во время написания, существует ошибка, которая мешает нам создать сгенерированный столбец ⁵ . К счастью, это не конец, это просто еще один путь! Мы все еще можем достичь того же результата, используя триггеры!

 -- Function to be invoked by trigger

CREATE OR REPLACE FUNCTION update_tsvector_column () RETURNS TRIGGER AS $$
BEGIN
  NEW . search : = to_tsvector( ' english ' , COALESCE( NEW . extract , ' ' ));

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY definer SET search_path = public, pg_temp;

-- Trigger that keeps the TSVECTOR up to date

DROP TRIGGER IF EXISTS " update_tsvector " ON " Movie " ;

CREATE TRIGGER " update_tsvector "
  BEFORE INSERT OR UPDATE ON " Movie "
  FOR EACH ROW
  EXECUTE FUNCTION update_tsvector_column ();

Время запрашивать

Теперь у вас есть ваш столбец tsvector , пришло время запросить. И да, вы делаете это с ts_query . Есть много удобных функций, которые могут помочь вам преобразовать запрос на текстовый поиск в то, что поймет постгры. phraseto_tsquery может принять словарь, а websearch_to_tsquery приближается к поведению некоторых общих инструментов поиска веб -сайта. Вы можете выбрать тот, который соответствует вашим потребностям ³ . Вы также можете пройти лишнюю милю и выполнять нечеткий поиск. Преобразуя свой текстовый поиск в tsvector , вы можете смешивать лексы и регулярные выражения, чтобы создать нечеткий tsquery :

 SELECT to_tsquery(string_agg(lexeme || ' :* ' , ' & ' ORDER BY positions)) AS q FROM unnest(to_tsvector(${searchQuery}))

Наконец, ваш запрос SQL Prisma может выглядеть так.

 const movies = await prisma . $queryRaw < MovieRecord [ ] > `
    WITH query AS (SELECT to_tsquery(string_agg(lexeme || ':*', ' & ' ORDER BY positions)) AS q FROM unnest(to_tsvector( ${ searchQuery } )))
    SELECT
      id, title, genre, year, extract, ts_rank(search, query.q) AS rank
    FROM
      "Movie", query
    ${ searchQuery ? Prisma . sql `WHERE search @@ query.q` : Prisma . empty }
    ORDER BY
      year, rank
    LIMIT 10
  `
/** Direct representation of a row in the Movie table. */
interface MovieRecord {
  id : string
  title : string
  year : number
  genre ?: string
  extract : string
}

Обратите внимание, что мы заказываем по ts_rank ⁶ , поэтому мы можем сначала предоставить лучшие результаты подходящих результатов!

Заключение

В целом, вы можете создать мощную функцию поиска, не полагаясь на стороннее программное обеспечение, дублирование базы данных и сложные синтаксисы. Вам просто нужны Postgres и SQL, вещи, которые у вас уже есть. Я почти уверен, что другие DBMS обрабатывают полный текстовый поиск аналогичным образом. Реализация проста, простой, гибкой и поддержанной. И если вы используете Prisma, вы можете достичь тех же результатов с менее элегантным, но все же функциональным подходом.

PS: Не забудьте индексировать с GIN !

Сноски

1. tsvector в документации Postgres. ↩

2. Открытая проблема для поддержки tsvector в репозитории Prisma. ↩

3. Функции текстового поиска и операторы. ↩ ↩ ²

4. Сгенерированные столбцы в Postgres. ↩

5. Поддержка сгенерированных столбцов. ↩

6. Результаты поиска ↩