gistfile1.sql

-- tables in extreme pseudocode

-- saved_searches:
int search_id primary key
string search

-- saved_searches_helper:
int search_id foreign key saved_searches(search_id)
int term_id foreign key terms(term_id)
int term_count

-- terms:
int term_id primary key
string term

-- Given saved search (id 1) for tags "avatar" (id 2) and "fantastic" (id 4):

insert into saved_searches (1, "avatar fantastic");
insert into saved_searches_helper (search_id, term_id) values (1, 2, 2);
insert into saved_searches_helper (search_id, term_id) values (1, 4, 2);

-- and saved search (id 2) for tags "avatar" (id 2) and "really" (id 6) and "terrible" (id 3):

insert into saved_searches_helper (search_id, term_id, term_count) values (2, 2, 3);
insert into saved_searches_helper (search_id, term_id, term_count) values (2, 6, 3);
insert into saved_searches_helper (search_id, term_id, term_count) values (2, 3, 3);

-- and saved search (id 3) for just tag "avatar" (id 2):

insert into saved_searches_helper (search_id, term_id, term_count) values (3, 2, 1);

-- Now, given an update, e.g., "Avatar was really terrible!", tokenize into terms:
-- "avatar", "was", "really", "terrible", with corresponding ids 2, (omitted as stop word), 6, 3, then:

SELECT search_id FROM saved_searches_helper WHERE term_id IN (2, 6, 3) GROUP BY search_id HAVING COUNT(search_id) = term_count; # -> will match saved searches 2 and 3

-- Given an update "Avatar was fantastic omg omg omg", tokenize into:
-- "avatar" (id 2), "fantastic" (id 4), "omg" (id 99):
SELECT search_id FROM saved_searches_helper WHERE term_id IN (2, 4, 99) GROUP BY search_id HAVING COUNT(search_id) = term_count; # -> matches searches 1 and 3.