Обсуждение: SQL Help - multi values
Hello I've just posted this question on the MySQL list but as I'm thinking of moving to PostGresQL I thought I'd post to this list too... Hope someone can help! Basically the problem I am having is how best to handle multiple values for a specific column, in this case the values in question are coming from an HTML SELECT MULTI box processed by PHP. The way I have been doing this so far is to have a delimited value stored in a varchar column, e.g. If my select box returns the values 2,4 and 7 I insert into my table the string '|2|4|7|'. Surely there must be a better way than this - but it escapes me. In this setup the only way to match a specifc value when searching is to use the query: SELECT dataid,title FROM table WHERE category LIKE '%|4|%' Which obviously has a huge performance penalty - and of course you can't JOIN against any of these values. The only other way I thought of was to use a separate table for the category entries: SELECT dataid,title,category FROM table LEFT JOIN table_categories ON table.dataid=table_categories.dataid But in the example above this would return 3 entries, which I don't want, and I can't select a particular dataid which satisfies more than category, e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). Any ideas? Please help! Cheers, james
Short answer - use arrays for storing and contrib/intarray for
indexed access
Regards,
Oleg
On Sun, 10 Feb 2002, James Carrier wrote:
> Hello
>
> I've just posted this question on the MySQL list but as I'm thinking of moving
> to PostGresQL I thought I'd post to this list too... Hope someone can help!
>
> Basically the problem I am having is how best to handle multiple values for
> a specific column, in this case the values in question are coming from an
> HTML SELECT MULTI box processed by PHP.
>
> The way I have been doing this so far is to have a delimited value stored
> in a varchar column, e.g. If my select box returns the values 2,4 and 7 I
>
> insert into my table the string '|2|4|7|'.
>
> Surely there must be a better way than this - but it escapes me. In this
> setup the only way to match a specifc value when searching is to use the query:
> SELECT dataid,title FROM table WHERE category LIKE '%|4|%'
> Which obviously has a huge performance penalty - and of course you can't
> JOIN against any of these values.
>
> The only other way I thought of was to use a separate table for the
> category entries:
>
> SELECT dataid,title,category FROM table LEFT JOIN table_categories ON
> table.dataid=table_categories.dataid
>
> But in the example above this would return 3 entries, which I don't want,
> and I can't select a particular dataid which satisfies more than category,
> e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement
> would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%').
>
> Any ideas? Please help!
>
> Cheers,
>
> james
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
Regards, Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
On Sun, Feb 10, 2002 at 06:34:52AM +0000, James Carrier <james.carrier@bulletonline.com> wrote: > > But in the example above this would return 3 entries, which I don't want, > and I can't select a particular dataid which satisfies more than category, > e.g. has categories 4 and 7 (i.e. for the example above the LIKE statement > would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%'). It is possible to write queries that return a list of dataids that having an enrty for more than one specific category. However, it seems like you probably want to encode the category list in a bit string based on how you have indicated you want to use it.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> Basically the problem I am having is how best to handle multiple
> values for a specific column, in this case the values in
> question are coming from an HTML SELECT MULTI box
Two easy, fairly portable ways that come to mind are to use
boolean columns or to use the bits of a large number. If you have
a very small number of possible values, you might just want to
use boolean columns like this:
CREATE TABLE htmlselect (title varchar(20),choice1 bool,choice2 bool,choice3 bool
);
This allows you to use SQL like this:
INSERT INTO htmlselect (title,choice1,choice2,choice3)
VALUES ('Pigpen','t','f','t');
SELECT title FROM htmlselect
WHERE choice1 is true OR choice3 is true;
Your application is responsible for keeping the actual
mapping of what each column "means" of course.
A better way (IMO) is to set the choices up as powers of 2, and
use a number to keep track of which values are set:
CREATE TABLE htmlselect ( title varchar(20), choices integer
);
Your application can add up all the exponents, or you can just
let SQL do it, as below. Use the binary AND operator to test
the values for your WHERE clause.
The SQL looks like this:
INSERT INTO hmtlselect (title, choices)
VALUES ('Pigpen', 2^1 + 2^3 + 2^7);
SELECT title FROM htmlselect
WHERE choices & 2^1 > 0 OR choices & 2^7 > 0;
An integer (in postgreSQL) will let you use 31 different values,
from 2^0 all the way to 2^31. Use smallint if you have 15 values
or less, and bigint will get you up to 62 different values.
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200202101150
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iQA/AwUBPGazL7ybkGcUlkrIEQIiOgCgwDQpNeTL2+7LDmYBrVSniCTPmF4Aniqy
PXL48tR/6anaXXBKZEAdV2n1
=2/dT
-----END PGP SIGNATURE-----
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,PRIMARYKEY (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
On Sunday, February 10, 2002, at 05:34 PM, James Carrier wrote:
>
> Surely there must be a better way than this - but it escapes me. In this
> setup the only way to match a specifc value when searching is to use
> the query:
> SELECT dataid,title FROM table WHERE category LIKE '%|4|%'
> Which obviously has a huge performance penalty - and of course you can't
> JOIN against any of these values.
>
> The only other way I thought of was to use a separate table for the
> category entries:
>
> SELECT dataid,title,category FROM table LEFT JOIN table_categories ON
> table.dataid=table_categories.dataid
>
> But in the example above this would return 3 entries, which I don't
> want,
> and I can't select a particular dataid which satisfies more than
> category,
> e.g. has categories 4 and 7 (i.e. for the example above the LIKE
> statement
> would be: WHERE category LIKE '%|4|%' AND LIKE '%|7|%').
create table mytab (myid serial, title text);
create table mytabcats (mytabid int REFERENCES mytab(myid), category
int);
insert INTO mytab (title) values('Title A');
insert INTO mytabcats (mytabid,category) values(1,10);
insert INTO mytabcats (mytabid,category) values(1,11);
insert INTO mytabcats (mytabid,category) values(1,12);
insert INTO mytab (title) values('Title B');
insert INTO mytabcats (mytabid,category) values(2,20);
insert INTO mytabcats (mytabid,category) values(2,21);
insert INTO mytabcats (mytabid,category) values(2,22);
insert INTO mytabcats (mytabid,category) values(2,100);
insert INTO mytabcats (mytabid,category) values(1,100);
select distinct myid, title from mytab left join mytabcats ON myid =
mytabid where category in (10,100);
-- myid | title
--------+---------
-- 1 | Title A
-- 2 | Title B
==============================
David Stanaway
Personal: david@stanaway.net
Work: david@netventures.com.au
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