Как найти записи между FTS5 и MATCH?

Question

Как я могу найти (ценовой) диапазон в базе данных SQLite3 с помощью таблицы FTS5?

Это сильно упрощенный пример таблицы:

CREATE VIRTUAL TABLE fruits USING fts5 (id, name, price);
INSERT INTO fruits (id,name,price) VALUES (1, 'Apple with A', 5);
INSERT INTO fruits (id,name,price) VALUES (2, 'Pineapple with B', 10);
INSERT INTO fruits (id,name,price) VALUES (3, 'Cucumber with C', 20);
INSERT INTO fruits (id,name,price) VALUES (4, 'Melon with D', 25);
INSERT INTO fruits (id,name,price) VALUES (5, 'Kiwi with E', 30);
INSERT INTO fruits (id,name,price) VALUES (6, 'Cucumber with F', 35);
INSERT INTO fruits (id,name,price) VALUES (7, 'Cucumber with G', 40);

Следующая команда возвращает ожидаемые две записи 3 и 7 для Cucumber:

SELECT * FROM fruits WHERE fruits MATCH 'name:Cucumber AND (price:20 OR price:40)';

Как мне найти огурцы в ценовом диапазоне от 20 до 40 (чтобы включить запись 6 в приведенном выше примере)? Если я попробую это с

SELECT * FROM fruits WHERE fruits MATCH 'name:Cucumber AND (price: BETWEEN 20 AND 40)';

или же

SELECT * FROM fruits WHERE fruits MATCH 'name:Cucumber AND (price: BETWEEN 19 AND 41)';

Я не получаю никакого результата (или сообщения об ошибке) вообще. Разве нельзя использовать MATCH и BETWEEN в одном запросе?

---

И дополнительно: почему команда

SELECT * FROM fruits WHERE fruits MATCH 'name:C';

вернуть только одну запись (id: 3), а не 3, 6 и 7, при условии, что будет также найден C в "Огурец", а не только C в "с C"?

Answer 1

Таблицы FTS хранят все как текст; не имеет смысла иметь id а также price столбцы в таблице FTS.

Единственные эффективные запросы к таблице FTS - это поиск слов (и поиск по внутренней docid).

Вы должны рассматривать таблицу FTS не как таблицу, а как индекс. Сохраните другие данные в "реальной" таблице и выполните любые другие запросы к этой таблице:

SELECT *
FROM fruits
WHERE id IN (SELECT docid
             FROM fruits_fts
             WHERE fruits_fts MATCH 'Cucumber')
  AND price BETWEEN 20 AND 40;

Для поиска слов, начинающихся с C, вы должны использовать префикс поиска.