SQLite regular table and table for fts

Question

I have table news (id, news_id, news_title) and I creat FTS table:

CREATE VIRTUAL TABLE news_search USING fts4 (news_title, tokenize=porter);

I use trigger to keep table NEWS and news_search in sync:

CREATE TRIGGER IF NOT EXISTS insert_news_trigger 
AFTER INSERT ON news 
BEGIN 
INSERT OR IGNORE INTO news_search (news_title) VALUES (NEW.news_title);
END;

Question: how to use search? When I do MATCH in news_search table it returns me only records from this table, but I need *news_id* from news table. May be I should add *news_id* column to news_search table?

What is the proper way to use fts in sqlite?

Answer 1

Read the documentation; FTS tables also have a rowid column (also called docid) that you can set explicitly to the same value as the corresponding key of the original table.

Assuming that news.id is the rowid (i.e., INTEGER PRIMARY KEY), you should change your trigger to also copy that ID value into the news_search table. You can the use that to look up the original record:

SELECT *
FROM news
WHERE id IN (SELECT docid
             FROM news_search
             WHERE news_title MATCH '😸')