What's the recommended way of storing "tags" in a database, and then
filtering based on the existence, or *non*-existence, of those tags on
some entities?
Our application stores contacts, where each contact may have any number
of tags. We do this with the tables contacts, contacts_tags and tags. We
also have segments, which defines "filters" on contacts based on
specific tags they must have and/or must *not* have. This is defined by
the tables segments and segments_tags. (See bottom of post for table
definitions).
Finding contacts matching a given segment which has BOTH positive
(required tags) and negative (non-existing tags) requirements is easy
enough (simplified):
SELECT segmentid, email
FROM segments_tags st
INNER JOIN contacts_tags ct USING (tagname)
INNER JOIN contacts USING (email)
WHERE st.tagtype = 1
GROUP BY 1, 2
HAVING COUNT(*)
= (SELECT COUNT(*) FROM segments_tags
WHERE segmentid = st.segmentid AND tagtype = 1)
EXCEPT
SELECT segmentid, email
FROM segments_tags st
INNER JOIN contacts_tags ct USING (tagname)
INNER JOIN contacts USING (email)
WHERE st.tagtype = 0;
However, segments which ONLY contain negative requirements (that's
"tagtype" = 0) doesn't work, for obvious reasons.
Is there a way to make this work with a single query for both cases?
Possibly using CTE (which I'm not very familiar with)?
Table definitions:
Table "public.contacts"
Column | Type | Modifiers
---------------+-----------------------------+-----------------
email | email | not null
name | text |
status | character(1) | not null default 'a'::bpchar
statuschanged | timestamp without time zone |
Indexes:
"contacts_pkey" PRIMARY KEY, btree (email)
Table "public.contacts_tags"
Column | Type | Modifiers
---------+-------+-----------
email | email | not null
tagname | text | not null
Indexes:
"contacts_tags_pkey" PRIMARY KEY, btree (email, tagname)
"contacts_tags_tagname" btree (tagname)
Foreign-key constraints:
"contacts_tags_email_fkey" FOREIGN KEY (email) REFERENCES
contacts(email) ON UPDATE CASCADE ON DELETE CASCADE
"contacts_tags_tagname_fkey" FOREIGN KEY (tagname) REFERENCES
tags(tagname) ON UPDATE CASCADE ON DELETE CASCADE
Table "public.tags"
Column | Type | Modifiers
-----------+-----------------------------+-----------------------
tagname | text | not null
createdat | timestamp without time zone | not null default now()
Indexes:
"tags_pkey" PRIMARY KEY, btree (tagname)
Table "public.segments"
Column | Type | Modifiers
-------------+-----------------------------+---------------------
segmentid | integer | not null default
nextval('segments_segmentid_seq'::regclass)
segmentname| text | not null
createdat | timestamp without time zone | not null default now()
Indexes:
"segments_pkey" PRIMARY KEY, btree (segmentid)
Table "public.segments_tags"
Column | Type | Modifiers
-----------+---------+----------
segmentid | integer | not null
tagname | text | not null
tagtype | integer | not null
Indexes:
"segments_tags_pkey" PRIMARY KEY, btree (segmentid, tagname)
Foreign-key constraints:
"segments_tags_segmentid_fkey" FOREIGN KEY (segmentid) REFERENCES
segments(segmentid) ON UPDATE RESTRICT ON DELETE CASCADE
"segments_tags_tagname_fkey" FOREIGN KEY (tagname) REFERENCES
tags(tagname) ON UPDATE CASCADE ON DELETE CASCADE
Regards,
-- a.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
