lundi 14 septembre 2015

Select by multiple tags (whitelist and blacklist)

I have the following structure:

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

CREATE TABLE tags
(
  story_id INTEGER,
  name TEXT
);

Now I want to select all stories that have (at least) given N tags, and do not have another N tags.

Example: All stories with "fantasy" and "nature", but without "dragons"

Here's a query I tried (for now just the "whitelist" part, but it's extremely slow - so I'm sure I'm doing it wrong.

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

If I add "LIMIT 10" at the end, it works (but very slowly).

No idea how to include the blacklist criteria into the query, though.

Ideas?

I have about 20.000 stories and 75.000 tag entries.

Aucun commentaire:

Enregistrer un commentaire