prisma with fts

البحث عن النص الكامل والغامض مع Postgres و Prisma

في مرحلة ما من حياة هندسة البرمجيات الخاصة بك ، ربما تكون قد سمعت عبارة "نحتاج إلى إنشاء ميزة بحث لهذا". تتطلب مجموعات البيانات الكبيرة أن تكون قابلة للبحث. يمكنك إضافة مرشحات إليها ، نعم ، لكن المستخدم النهائي سيرغب دائمًا في كتابة lrd of t والحصول على زمالة الحلقة كنتيجة أولى. من المحتمل أنك فكرت في استخدام بعض الحلول الثالثة الفاخرة ، والتي تبدو واعدة ، فقط توجه قاعدة البيانات إليها ويتم التعامل مع البحث بطريقة أو بأخرى. ولكن ماذا لو ، مع القليل من العبث SQL ، يمكنك الوصول إلى نفس النتيجة ، أو حتى أفضل؟

أساسيات postgres للبحث عن النص الكامل

عندما يتعلق الأمر بتحليل النص أو التعدين ، هناك بعض المفاهيم المختلفة المفيدة لفهمها قبل المغامرة في رحلة البحث النصية الكاملة. يمكننا أن نبدأ بتحديد وثيقة . المستند هو إلى حد كبير ما تعرفه بالفعل ، مجموعة من الجمل بعد نوع من الهيكل ، مكتوبة بلغة محددة. El Cantar del Mío CID هو وثيقة ، و sinopsis لفيلم 1930 هو وثيقة أيضًا. عندما يتعلق الأمر بـ Postgres ، يمكن العثور على المستندات في واحد أو العديد من الأعمدة. عادة ما يتم تحليل المستندات في الرموز ، والتي يمكن أن تكون الكلمات والعبارات ، والتي يمكننا من خلالها استرداد lexemes ، وحدات ذات مغزى من النص.

يأخذ Postgres المستندات وتوصيف lexemes منها باستخدام القواميس . هناك قاموس افتراضي وقواميس قائمة على اللغة ويمكنك حتى توفير خاص بك. الحقيقة هي ، إذا كنت تعرف أن وثيقتك باللغة الألمانية ، فمن المحتمل أن ترغب في استخدام القاموس الألماني لتحليل lexemes الخاص بك. باستخدام قواميس محددة ، يمكنك الحصول على أفضل lexemes خاصة بأباطير لغتك وجذور الكلمة وأصلات.

تلبية tsvector

tsvector هو نوع بيانات مدمج في Postgres ¹ ، وهو يمثل قائمة مصنفة من lexemes المتميزة والمطابقة. إذا اعتبرنا نموذج 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 ² . ومع ذلك ، يمكن للمرء الاستفادة من الديكور @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 سلوك بعض أدوات البحث الشائعة على الويب. يمكنك اختيار الخاص بك الذي يناسب احتياجاتك ³ . يمكنك أيضًا الذهاب إلى أميل إضافي وإجراء بحث غامض. من خلال تحويل البحث عن النص إلى tsvector ، يمكنك مزج Lexemes والتعبيرات العادية لإنشاء 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 ، الأشياء التي لديك بالفعل. أنا متأكد من أن DBMSS الأخرى تتعامل مع البحث عن النص الكامل بطريقة مماثلة. التنفيذ بسيط ومباشر ومرن وقابل للصيانة. وإذا كنت تستخدم Prisma ، فيمكنك تحقيق نفس النتائج مع نهج أقل أناقة ولكن لا يزال وظيفيًا.

ملاحظة: لا تنسى الفهرسة مع GIN !

الحواشي

1. tsvector في وثائق Postgres. ↩

2. قضية مفتوحة لدعم tsvector في مستودع Prisma. ↩

3. وظائف البحث النصية والمشغلين. ↩ ↩ ²

4. أعمدة تم إنشاؤها في postgres. ↩

5. دعم الأعمدة التي تم إنشاؤها. ↩

6. تصنيف نتائج البحث ↩