From: Andrew G. Hammond <drew@xyzzy.dhs.org>
To: James Carrier <james.carrier@bulletonline.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: SQL Help - multi values
Date: Sun, 10 Feb 2002 15:33:33 -0500
Message-ID: <20020210203333.GA22011@xyzzy.dhs.org> (raw)
In-Reply-To: <5.1.0.14.2.20020210063242.00b18da0@bunce.bitecomm.co.uk>
References: <5.1.0.14.2.20020210063242.00b18da0@bunce.bitecomm.co.uk>
Since you've only given us a vague description to work with, I can only
give you a vague answer in return.
For situations like what you've described above, I tend to use the
following schema:
CREATE TABLE widget (
widget_id SERIAL PRIMARY KEY,
some_data TEXT
);
CREATE TABLE category (
category_id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE w_x_c (
widget_id INTEGER NOT NULL REFERENCES widget,
category_id INTEGER NOT NULL REFERENCES category,
PRIMARY KEY (category_id, widget_id)
);
-- primary key will implicitly create index good for mapping categories
-- to widgets, and here's an index to go the other way
CREATE INDEX w_x_c_rev_idx ON w_x_c (wigdet_id, category_id);
-- Which categories a widget belongs to (naturally you'd use a subselect
-- or whatever instead of 1)
SELECT * FROM category
NATURAL JOIN
SELECT * FROM w_x_c WHERE widget_id = 1;
-- Since you'll be doing this in the context of a web multi select,
-- You probably want a list of all the categories, one per row, with
-- a column (widget_id) that is either NULL or a number to tell you
-- if that row is selected or not.
SELECT * FROM category
NATURAL LEFT OUTER JOIN
SELECT widget_id FROM w_x_c WHERE widget_id = 1;
-- Of course that gives you a row for each category, which is mighty
-- handy if you're doing web work. But it sounds like you'd prefer
-- things in a different format:
CREATE FUNCTION fugly_concat_step (text, text) RETURNS text
AS 'SELECT ($1 || $2 || ''|'')::text;' LANGUAGE 'sql';
CREATE AGGREGATE fugly_concat (
BASETYPE = text, STYPE = text,
SFUNC = fugly_concat_step,
INITCOND = '|'
);
SELECT widget_id fugly_concat(category_id::text)
FROM w_x_c WHERE widget_id = 1
GROUP BY widget_id;
Now, when you want to add a new category, simply insert it into the
category table. Takes a little extra PHP coding, but you won't ever
have to update your code because you've added or changed categories.
--
Andrew G. Hammond mailto:drew@xyzzy.dhs.org http://xyzzy.dhs.org/~drew/
56 2A 54 EF 19 C0 3B 43 72 69 5B E3 69 5B A1 1F 613-389-5481
5CD3 62B0 254B DEB1 86E0 8959 093E F70A B457 84B1
"To blow recursion you must first blow recur" -- me
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: drew@xyzzy.dhs.org, james.carrier@bulletonline.com
Subject: Re: SQL Help - multi values
In-Reply-To: <20020210203333.GA22011@xyzzy.dhs.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox