Full Text Search في Postgres: بحث بالكلمات، بالعربي والإنجليزي

DROP TABLE IF EXISTS articles CASCADE;
CREATE TABLE articles (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  body  text NOT NULL
);
INSERT INTO articles (title, body) VALUES
  ('Running Node in production', 'Node servers keep running when you handle errors and signals.'),
  ('Postgres indexes', 'A good index makes queries run fast. Bad indexes slow writes.'),
  ('Python for data', 'Pandas and notebooks for data people.'),
  ('تعلم البرمجة بالعربي', 'المبرمجون الجدد بيتعلموا البرمجة أسرع بالأمثلة العملية.'),
  ('أساسيات قواعد البيانات', 'قاعدة البيانات بتحفظ البيانات وبتسأل عنها بسرعة.');


-- LIKE: حروف، مش كلمات
SELECT title FROM articles WHERE body ILIKE '%run%';
SELECT title FROM articles WHERE body ILIKE '%البرمجه%';

-- full text: كلمات بأصلها (stemming)
SELECT to_tsvector('english', 'Node servers keep running') AS doc,
       to_tsquery('english', 'run') AS query;
SELECT title FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('english', 'run');

SELECT to_tsvector('arabic', 'المبرمجون الجدد بيتعلموا البرمجة') AS arabic_doc;

$ psql -q -f schema.sql
           title            
----------------------------
 Running Node in production
 Postgres indexes
(2 rows)

 title 
-------
(0 rows)

                 doc                  | query 
--------------------------------------+-------
 'keep':3 'node':1 'run':4 'server':2 | 'run'
(1 row)

           title            
----------------------------
 Running Node in production
 Postgres indexes
(2 rows)

              arabic_doc              
--------------------------------------
 'برمج':4 'جدد':2 'مبرمج':1 'يتعلم':3
(1 row)

DROP TABLE IF EXISTS articles CASCADE;
CREATE TABLE articles (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  body  text NOT NULL
);
INSERT INTO articles (title, body) VALUES
  ('Running Node in production', 'Node servers keep running when you handle errors and signals.'),
  ('Postgres indexes', 'A good index makes queries run fast. Bad indexes slow writes.'),
  ('Python for data', 'Pandas and notebooks for data people.'),
  ('تعلم البرمجة بالعربي', 'المبرمجون الجدد بيتعلموا البرمجة أسرع بالأمثلة العملية.'),
  ('أساسيات قواعد البيانات', 'قاعدة البيانات بتحفظ البيانات وبتسأل عنها بسرعة.');


-- عمود محسوب: العنوان أهم من الكلام (A > B)، و GIN index
ALTER TABLE articles ADD COLUMN search tsvector GENERATED ALWAYS AS (
  setweight(to_tsvector('english', title), 'A') || setweight(to_tsvector('english', body), 'B')
) STORED;
CREATE INDEX articles_search ON articles USING gin (search);

-- websearch_to_tsquery: نفس اللي المستخدمين بيكتبوه في Google
SELECT websearch_to_tsquery('english', 'node -python "fast queries"') AS parsed;

SELECT title,
       round(ts_rank(search, q)::numeric, 3) AS rank,
       ts_headline('english', body, q, 'StartSel=[, StopSel=]') AS snippet
FROM articles, websearch_to_tsquery('english', 'index OR running') AS q
WHERE search @@ q
ORDER BY rank DESC;

$ psql -q -f schema.sql
                 parsed                  
-----------------------------------------
 'node' & !'python' & 'fast' <-> 'queri'
(1 row)

           title            | rank  |                               snippet                               
----------------------------+-------+---------------------------------------------------------------------
 Postgres indexes           | 0.469 | A good [index] makes queries [run] fast. Bad [indexes] slow writes.
 Running Node in production | 0.334 | Node servers keep [running] when you handle errors and signals.
(2 rows)

CREATE EXTENSION IF NOT EXISTS pg_trgm;
DROP TABLE IF EXISTS courses CASCADE;
CREATE TABLE courses (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, title text, description text);
INSERT INTO courses (title, description) VALUES
  ('أساسيات البرمجة', 'أول خطوة في عالم البرمجة من الصفر.'),
  ('Programming with Node', 'بناء servers بـ Node.js.'),
  ('قواعد البيانات', 'تصميم الجداول والبرمجة بـ SQL.'),
  ('Docker للمبتدئين', 'containers و images.');


ALTER TABLE courses ADD COLUMN search tsvector GENERATED ALWAYS AS (
  setweight(to_tsvector('arabic', title), 'A') ||
  setweight(to_tsvector('english', title), 'A') ||
  setweight(to_tsvector('arabic', description), 'B')
) STORED;
CREATE INDEX courses_search ON courses USING gin (search);
CREATE INDEX courses_title_trgm ON courses USING gin (title gin_trgm_ops);

\set q 'البرمجة'
SELECT title, round(ts_rank(search, q)::numeric, 3) AS rank
FROM courses, (SELECT websearch_to_tsquery('arabic', :'q') || websearch_to_tsquery('english', :'q') AS q) s
WHERE search @@ q ORDER BY rank DESC;

\set q 'programming'
SELECT title, round(ts_rank(search, q)::numeric, 3) AS rank
FROM courses, (SELECT websearch_to_tsquery('arabic', :'q') || websearch_to_tsquery('english', :'q') AS q) s
WHERE search @@ q ORDER BY rank DESC;

\set q 'البرمجه'
SELECT title, round(similarity(title, :'q')::numeric, 2) AS score
FROM courses WHERE similarity(title, :'q') > 0.3 ORDER BY score DESC;

$ psql -q -f schema.sql
      title      | rank  
-----------------+-------
 أساسيات البرمجة | 0.638
(1 row)

         title         | rank  
-----------------------+-------
 Programming with Node | 0.608
(1 row)

      title      | score 
-----------------+-------
 أساسيات البرمجة |  0.33
(1 row)