SQLite FTS4 с предпочтительным языком

Question

У меня есть таблица SQLite, которая была сгенерирована с помощью модуля FTS4. Каждая запись указана как минимум дважды на разных языках, но все еще имеет уникальный идентификатор (столбец int, не индексируется). Вот что я хочу сделать: я хочу найти термин на предпочитаемом языке. Я хочу объединить результат с поиском для того же термина, используя другой язык. Однако для второго поиска я хочу игнорировать все записи (идентифицированные по их идентификатору), которые я уже нашел во время первого поиска. В общем, я хочу сделать это:

WITH term_search1 AS (
    SELECT *
    FROM myFts
    WHERE myFts MATCH 'term'
    AND languageId = 1)
SELECT *
FROM term_search1
UNION
SELECT *
FROM myFts
WHERE myFts MATCH 'term'
AND languageId = 2
AND id NOT IN (SELECT id FROM term_search1)

Проблема здесь в том, что запрос term_seach1 будет выполнен дважды. Есть ли способ материализации моих результатов, может быть? Любое решение для ограничения его до 2 запросов (вместо 3) было бы здорово.

Я также пытался использовать рекурсивные запросы, что-то вроде:

WITH RECURSIVE term_search1 AS (
    SELECT *
    FROM myFts
    WHERE myFts MATCH 'term'
    AND languageId = 1
UNION ALL
    SELECT m.*
    FROM myFts m LEFT OUTER JOIN term_search1 t ON (m.id = t.id)
    WHERE myFts MATCH 'term'
    AND m.languageId = 2
    AND t.id IS NULL
)
SELECT * FROM term_search1

Это тоже не сработало. Очевидно, он только что выполнил два поиска для languageId = 2 (возможно, это ошибка?).

Заранее спасибо:)

Answer 1

Вы можете использовать таблицы TEMPORARY, чтобы уменьшить количество запросов к myFts до 2:

CREATE TEMP TABLE results (id INTEGER PRIMARY KEY);

INSERT INTO results 
    SELECT id FROM myFts
    WHERE myFts MATCH 'term' AND languageId = 1;

INSERT INTO results
    SELECT id FROM myFts
    WHERE myFts MATCH 'term' AND languageId = 2
    AND id NOT IN (SELECT id FROM results);

SELECT * FROM myFts
    WHERE id IN (SELECT id FROM results);

DROP TABLE results;

---

Если есть возможность изменить схему, вы должны хранить только текстовые данные в таблице FTS. Таким образом вы избежите неверных результатов при поиске совпадений чисел и строк languageId не желательно. Создайте другую мета-таблицу, содержащую нетекстовые данные (например, id а также languageId) и отфильтруйте строки, соединив rowid из myFts, Таким образом, вам нужно будет запросить таблицу FTS только один раз - используйте временную таблицу для хранения результатов таблицы FTS, а затем используйте мета-таблицу для их упорядочения.

Answer 2

Это лучшее, что я могу придумать:

SELECT *
FROM myFts t1
JOIN (SELECT COUNT(*) AS cnt, id 
      FROM myFts t2
      WHERE t2.languageId in (1, 2) 
      AND t2.myFts MATCH 'term'
      GROUP BY t2.id) t3
ON t1.id = t3.id
WHERE t1.myFts MATCH 'term'
    AND t1.languageId in (1, 2) 
    AND (t1.languageId = 1 or t3.cnt = 1)

Я не уверен, если второй MATCH пункт необходим. Идея состоит в том, чтобы сначала посчитать приемлемые строки, а затем выбрать лучший.

Редактировать: я понятия не имею, почему это не работает с вашим столом. Вот что я сделал, чтобы проверить это (SQLite версии 3.8.10.2):

CREATE VIRTUAL TABLE myFts USING fts4(
  id integer,
  languageId integer,
  content TEXT
);

insert into myFts(id, languageId, content) values (10, 1, 'term 10 lang 1');
insert into myFts(id, languageId, content) values (10, 2, 'term 10 lang 2');
insert into myFts(id, languageId, content) values (11, 1, 'term 11 lang 1');
insert into myFts(id, languageId, content) values (12, 2, 'term 12 lang 2');
insert into myFts(id, languageId, content) values (13, 1, 'not_erm 13 lang 1');
insert into myFts(id, languageId, content) values (13, 2, 'term 13 lang 2');

выполнение запроса дает:

sqlite> SELECT *
   ...> FROM myFts t1
   ...> JOIN (SELECT COUNT(*) AS cnt, id 
   ...>       FROM myFts t2
   ...>       WHERE t2.languageId in (1, 2) 
   ...>       AND t2.myFts MATCH 'term'
   ...>       GROUP BY t2.id) t3
   ...> ON t1.id = t3.id
   ...> WHERE t1.myFts MATCH 'term'
   ...>     AND t1.languageId in (1, 2) 
   ...>     AND (t1.languageId = 1 or t3.cnt = 1);
10|1|term 10 lang 1|2|10
11|1|term 11 lang 1|1|11
12|2|term 12 lang 2|1|12
13|2|term 13 lang 2|1|13
sqlite>