prisma with fts download prisma with fts源代碼下載

prisma with fts

其他源碼

1.0.0

下載

完整的，模糊的文字搜索與Postgres和Prisma

在您的軟件工程生活中的某個時刻，您可能已經聽過“我們需要為此構建搜索功能”一詞。大型數據集需要搜索。是的，您可以添加過濾器，但是最終用戶將始終想鍵入lrd of t ，並將戒指的獎學金作為第一個結果。您可能想到了使用一些精美的第三方解決方案，看起來很有希望，您只需將數據庫指向其中，並以某種方式處理搜索。但是，如果有一點點的SQL修補，您可以取得相同的結果，甚至更好？

Postgres基礎知識用於全文搜索

在文本分析或採礦方面，有一些不同的概念在進行完整的全文搜索之旅之前有用。我們可以從定義文檔開始。文檔幾乎是您可能已經知道的，是用特定語言編寫的某種結構的一組句子。 El CantardelMíoCid是一份文件，1930年的電影的中國人也是一份文件。關於Postgres，可以在一個或多個列中找到文檔。文檔通常被解析為代幣，這可能是單詞和短語，我們可以從中檢索詞彙，有意義的文本單位。

Postgres使用詞典從中拿出文檔，並解析其詞彙。有一個默認詞典，基於語言的詞典，您甚至可以提供自己的詞典。事實是，如果您知道自己的文檔是德語的，那麼您可能想使用德語詞典來解析您的詞彙。通過使用特定的詞典，您可以獲得更好的詞彙，特定於語言的字母和單詞根和詞源。

遇到`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
}

我們可以根據電影的摘錄來想到搜索功能。不幸的是，截至今天，Prisma缺乏對tsvector ^2的支持。但是，可以利用@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做到這一點。有許多方便的功能可以幫助您將文本搜索查詢轉換為Postgres所理解的內容。 phraseto_tsquery可以採用字典， websearch_to_tsquery近似於某些常見的Web搜索工具的行為。您可以選擇適合您需求的人³ 。您也可以加倍努力進行模糊搜索。通過將文本搜索轉換為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索引！