按多个标签(白名单、黑名单)选择

Question

我有以下结构:

CREATE TABLE stories
(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  dir TEXT,
  alias TEXT,
  title TEXT
);
CREATE TABLE tags
(
  story_id INTEGER,
  name TEXT
);

现在我要选择所有具有(至少)给定N个标签，并且不具有其他N个标签的故事。

示例:所有有"奇幻"one_answers"自然"但没有"龙"的故事

这是我尝试的一个查询(现在只是"白名单"部分，但它非常慢-所以我肯定我做错了。

SELECT s.*
FROM stories s
WHERE 
   (SELECT COUNT(*)
       FROM tags t
       WHERE 
           t.story_id = s.id
           AND t.name IN ('fantasy', 'nature')
   ) = 2

如果我在末尾加上"LIMIT 10"，它可以工作(但很慢)。

不知道如何在查询中包含黑名单标准。

想法?

我有大约20,000个故事和75,000个标签条目。

Answer 1

这可以通过子查询轻松完成:

SELECT ...
FROM stories
WHERE id     IN (SELECT story_id FROM tags WHERE name = 'fantasy')
  AND id     IN (SELECT story_id FROM tags WHERE name = 'nature' )
  AND id NOT IN (SELECT story_id FROM tags WHERE name = 'dragons');

或者，使用复合查询来组合标记过滤器:

SELECT ...
FROM stories
WHERE id IN (SELECT story_id FROM tags WHERE name = 'fantasy'
             INTERSECT
             SELECT story_id FROM tags WHERE name = 'nature'
             EXCEPT
             SELECT story_id FROM tags WHERE name = 'dragons');

哪一个更快取决于你正在检查的标签数量，以及你的过滤器的选择性;你得试试。

如果在tags.name列上有一个索引，这两个查询都是有效的。