Re: SQL Help - multi values
От | James Carrier |
---|---|
Тема | Re: SQL Help - multi values |
Дата | |
Msg-id | 5.1.0.14.2.20020211141623.00ab7900@bunce.bitecomm.co.uk обсуждение исходный текст |
Ответ на | Re: SQL Help - multi values ("Andrew G. Hammond" <drew@xyzzy.dhs.org>) |
Список | pgsql-sql |
Thanks a lot for the help Andrew (and everyone else who responded!) Cheers james At 15:33 10/02/2002 -0500, Andrew G. Hammond wrote: > 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 James Carrier Bullet Online :: Aim Higher [http://www.bulletonline.com] 41b Beavor Lane, London W6 9BL Tel +44 (0) 20 8834 3442 Fax +44 (0) 20 8741 2790
В списке pgsql-sql по дате отправления: