From: | Anders Steinlein <anders(at)steinlein(dot)no> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Filtering by tags |
Date: | 2010-06-30 15:54:51 |
Message-ID: | 4C2B68CB.7010104@steinlein.no |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
No one with any response on this?
-- a.
Anders Steinlein wrote:
> 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 | Tom Lane | 2010-06-30 16:07:13 | Re: Backend Crash v8.4.2 |
Previous Message | Kelly Burkhart | 2010-06-30 14:57:22 | Re: Backend Crash v8.4.2 |