prisma with fts
1.0.0
ในบางจุดในชีวิตวิศวกรรมซอฟต์แวร์ของคุณคุณอาจเคยได้ยินวลี "เราจำเป็นต้องสร้างคุณสมบัติการค้นหาสำหรับสิ่งนี้" ชุดข้อมูลขนาดใหญ่ต้องการค้นหาได้ คุณสามารถเพิ่มตัวกรองได้ใช่ แต่ผู้ใช้จะต้องการพิมพ์ lrd of t และรับมิตรภาพของแหวนเป็นผลลัพธ์แรก คุณอาจคิดว่าจะใช้โซลูชันของบุคคลที่สามแฟนซีซึ่งดูมีแนวโน้มคุณเพียงแค่ชี้ฐานข้อมูลไปที่มันและการค้นหาได้รับการจัดการอย่างใด แต่ถ้าหากมีการซ่อมแซม SQL เล็กน้อยคุณสามารถไปถึงผลลัพธ์เดียวกันหรือดีกว่า?
เมื่อพูดถึงการวิเคราะห์ข้อความหรือการขุดมีแนวคิดที่แตกต่างกันสองสามข้อที่มีประโยชน์ในการเข้าใจก่อนที่จะออกไปในการเดินทางที่สวยงามของการค้นหาข้อความเต็มรูปแบบ เราสามารถเริ่มต้นด้วยการกำหนด เอกสาร เอกสารเป็นสิ่งที่คุณอาจรู้อยู่แล้วชุดของประโยคตามโครงสร้างบางอย่างเขียนด้วยภาษาที่เฉพาะเจาะจง El Cantar del Mío Cid เป็นเอกสารและ Sinopsis ของภาพยนตร์ปี 1930 ก็เป็นเอกสารเช่นกัน เมื่อพูดถึง Postgres เอกสารสามารถพบได้ในคอลัมน์หนึ่งหรือหลายคอลัมน์ เอกสารมักจะแยกวิเคราะห์เป็น โทเค็น ซึ่งอาจเป็นคำและวลีซึ่งเราสามารถดึง คำยีนส์ , หน่วยที่มีความหมายของข้อความ
Postgres ใช้เอกสารและวิเคราะห์คำศัพท์จากพวกเขาโดยใช้ พจนานุกรม มีพจนานุกรมเริ่มต้นพจนานุกรมที่ใช้ภาษาและคุณสามารถให้ของคุณเองได้ ความจริงก็คือถ้าคุณรู้ว่าเอกสารของคุณเป็นภาษาเยอรมันคุณอาจต้องการใช้พจนานุกรมภาษาเยอรมันเพื่อวิเคราะห์คำศัพท์ของคุณ ด้วยการใช้พจนานุกรมเฉพาะคุณจะได้รับการคัดค้านที่ดีขึ้นเฉพาะกับตัวอักษรและรากคำและนิรุกติศาสตร์ของภาษาของคุณ
tsvector tsvector เป็นชนิดข้อมูลในตัวใน Postgres 1 ซึ่งแสดงถึงรายการเรียงลำดับของคำยีนส์ที่แตกต่างและเป็นมาตรฐานที่แตกต่างกัน หากเราพิจารณาโมเดล 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 3 ที่จะใช้เอกสารของคุณ (และพจนานุกรมเสริม) และจะสร้างเวกเตอร์สำหรับมัน อย่างไรก็ตามจะเกิดอะไรขึ้นถ้าสารสกัดได้รับการอัปเดต? ถ้าฉันเพิ่มแถวใหม่ เราต้องคำนวณใหม่ และโอกาสที่ดีสำหรับคอลัมน์ที่สร้างขึ้น 4 จะเปล่งประกายใช่มั้ย ด้วย 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 ที่สร้างขึ้นในการอพยพในขณะที่เขียนมีข้อผิดพลาดที่ป้องกันไม่ให้เราตั้งค่าคอลัมน์ที่สร้างขึ้น 5 โชคดีที่นี่ไม่ใช่จุดจบนี่เป็นเพียงเส้นทางอื่น! เรายังสามารถบรรลุผลลัพธ์เดียวกันโดยใช้ทริกเกอร์!
-- 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 ใกล้เคียงกับพฤติกรรมของเครื่องมือค้นหาเว็บทั่วไปบางอย่าง คุณสามารถเลือกสิ่งที่เหมาะกับความต้องการของคุณ 3 นอกจากนี้คุณยังสามารถไปได้ระยะทางพิเศษและทำการค้นหาที่คลุมเครือ โดยการแปลงการค้นหาข้อความของคุณเป็น tsvector คุณสามารถผสมคำศัพท์และการแสดงออกปกติเพื่อสร้าง tsquery ฟัซซี่:
SELECT to_tsquery(string_agg(lexeme || ' :* ' , ' & ' ORDER BY positions)) AS q FROM unnest(to_tsvector(${searchQuery}))ในที่สุด Query 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 6 เพื่อให้เราสามารถให้ผลลัพธ์ที่ตรงกันที่ดีที่สุดก่อน!
โดยรวมแล้วคุณสามารถสร้างคุณสมบัติการค้นหาที่ทรงพลังโดยไม่ต้องพึ่งพาซอฟต์แวร์ของบุคคลที่สามการทำซ้ำฐานข้อมูลและไวยากรณ์ที่ซับซ้อน คุณเพียงแค่ต้องมี postgres และ SQL สิ่งที่คุณมีอยู่แล้ว ฉันค่อนข้างแน่ใจว่า DBMS อื่น ๆ จัดการกับการค้นหาข้อความแบบเต็มในลักษณะเดียวกัน การใช้งานนั้นง่ายตรงไปตรงมายืดหยุ่นและบำรุงรักษาได้ และถ้าคุณใช้ Prisma คุณสามารถบรรลุผลลัพธ์เดียวกันด้วยวิธีการที่สง่างามน้อยกว่า แต่ยังคงใช้งานได้
PS: อย่าลืมดัชนีกับ GIN !
tsvector ในเอกสาร Postgres
ปัญหาเปิดสำหรับการสนับสนุน tsvector ในที่เก็บของ Prisma
ฟังก์ชั่นการค้นหาข้อความและตัวดำเนินการ ↩ 2
คอลัมน์ที่สร้างขึ้นใน postgres
รองรับคอลัมน์ที่สร้างขึ้น
การจัดอันดับผลการค้นหา↩
