From: | Anders Steinlein <anders(at)steinlein(dot)no> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Filtering by tags |
Date: | 2010-06-24 17:18:22 |
Message-ID: | 4C23935E.9030509@steinlein.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Browne | 2010-06-24 19:06:22 | Re: HA for PostgreSQL (Auth-Server) |
Previous Message | Bill Thoen | 2010-06-24 17:16:45 | When to use Vacuum? |