Re: Am I doing things right? (selecting groups of objects problems)
Lecho <[EMAIL PROTECTED]> writes: >I have a db with objects table, each of those objects may belong to groups >of objects. The number of groups can be about 256 and an object >belongs from one to many different groups at once. Lecho, I threw together the following tables/data/queries that I believe handle your setup: create table objs ( obj_id int, obj_namevarchar(20) ); create table grps ( grp_id int, grp_descvarchar(20) ); create table grp_map ( obj_id int, grp_id int ); insert into objs (obj_id, obj_name) values (1, 'Obj1'); insert into objs (obj_id, obj_name) values (2, 'Obj2'); insert into objs (obj_id, obj_name) values (3, 'Obj3'); insert into objs (obj_id, obj_name) values (4, 'Obj4'); insert into grps (grp_id, grp_desc) values (10, 'Grp10'); insert into grps (grp_id, grp_desc) values (20, 'Grp20'); insert into grps (grp_id, grp_desc) values (30, 'Grp30'); insert into grp_map (obj_id, grp_id) values (1, 10); insert into grp_map (obj_id, grp_id) values (1, 30); insert into grp_map (obj_id, grp_id) values (2, 20); insert into grp_map (obj_id, grp_id) values (2, 30); insert into grp_map (obj_id, grp_id) values (3, 10); insert into grp_map (obj_id, grp_id) values (3, 20); insert into grp_map (obj_id, grp_id) values (3, 30); insert into grp_map (obj_id, grp_id) values (4, 30); select distinct(obj_id) from grp_map where grp_id in (10, 20); mysql> select distinct(obj_id) from grp_map -> where grp_id in (10, 20) -> ; ++ | obj_id | ++ | 1 | | 2 | | 3 | ++ 3 rows in set (0.00 sec) This will tell you all the objects that belong to groups 10 or 20 Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Am I doing things right? (selecting groups of objects problems)
On Fri, 2004-04-09 at 09:48, [EMAIL PROTECTED] wrote: > Hello, > > I've recently came across a problem I couldn't seem to solve right by myself. > > I have a db with objects table, each of those objects may belong to groups > of objects. The number of groups can be about 256 and an object > belongs from one to many different groups at once. > > I cannot find a good way to store/select information about groups for > objects that belong to several groups. > > My first idea was to put a 'groups' column and keep there the > id_numbers of groups to which particular object belongs to (with > separators). So the groups column would have values like '100,203,204,208' > etc. Of course this is not the right way to do it as those values are not > atom and the lenght of the field may easily grow out of tinytext length > type I was using... > > It worked ok though for small number of groups and objects though I > know this was a perfect example of how *not* do do it in a RDB... If > I wanted an object that belonged to 2 groups (100 and 203 for example) > I would do "SELECT [...] LIKE '%100%203%'". You definitely do not want to do it this way. You'd have to keep the 'groups' column items sorted to make sure you never have a situation where you query for '%100%203%' when it contains (203,204,100). This makes it complicated/non-trivial to add or remove objects from a group. Additionally, you'd be doing a full text search for each query which is vastly slower than querying on an indexed field. > My next attempt was to create separate table with object_id and > group_id column, this way an object that belongs to many groups would have > several rows in the table. If I wanted to know what are the groups > that my object belongs to - a piece of cake. But things get really rough if > I want to get objects that (for example) belong to two particular (1 & > 2) groups. I would go with something like that: > > SELECT DISTINCT(g1.object_id) FROM connecting_table as g1, connecting_table > as g2 WHERE g1.group_id = 1 AND g2.group_id = 2 AND g1.object_id = > g2.object_id; > > But this is getting more and more complex if I want to select objects > that belong to 3, 4 and N groups at once :/ The first solution > although very ugly seems to be much more clearer (and maybe even more > efficient for groupn number of 2+?). This is the right way and more efficient, but why are you making it so difficult on yourself? ;) SELECT DISTINCT object_id FROM connecting_table WHERE group_id = 1 AND group_id = 2; There's no need to select the same table twice. You can make this even simpler by using 'IN': SELECT DISTINCT object_id FROM connecting_table WHERE group_id IN (1, 2) > My working solution for now is to have a separate table with object_id > and groups_id1 column of INT type, I have assigned the first 31 existing > groups to corresponding bits of the INT field. That is if I want to > select objects that belong to the groups that have ids of 1,3,5 and 26 i > would set the bits nr 1,2,4 and 26 of a variable $MY_INT and then query: > > SELECT object_id,groups_id1 FROM connecting_table WHERE > BIT_COUNT(groups_id1 & $MY_INT) = 4; > > this seems to give me what I want but now if I want to add next 31 > groups I need to create another groups_id2 column and things again > get a bit complicated - I need now to check in which column the > current group flag I need to find out may be stored and act > accordingly (so groups with id 1-31 in column groups_id1, groups 32-63 in > groups_id2 etc). Not a big problem and this solutions seems to be quite > cheap (is it?) but somehow I am feeling there is a better way to > do all this... This has the same problems as the LIKE '%100%203%' approach, but a full text search is replaced by math on each row. In both cases you lose the ability to use any kind of index. > Sorry for this long letter I hope I have managed to put the problem > straight, I may miss something obvious here but as I > said I have limited knowledge about sql. I would be much grateful for any > tips. > > > > Lecho -- |- Garth Webb -| |- [EMAIL PROTECTED] -| signature.asc Description: This is a digitally signed message part
Am I doing things right? (selecting groups of objects problems)
Hello, I've recently came across a problem I couldn't seem to solve right by myself. I have a db with objects table, each of those objects may belong to groups of objects. The number of groups can be about 256 and an object belongs from one to many different groups at once. I cannot find a good way to store/select information about groups for objects that belong to several groups. My first idea was to put a 'groups' column and keep there the id_numbers of groups to which particular object belongs to (with separators). So the groups column would have values like '100,203,204,208' etc. Of course this is not the right way to do it as those values are not atom and the lenght of the field may easily grow out of tinytext length type I was using... It worked ok though for small number of groups and objects though I know this was a perfect example of how *not* do do it in a RDB... If I wanted an object that belonged to 2 groups (100 and 203 for example) I would do "SELECT [...] LIKE '%100%203%'". My next attempt was to create separate table with object_id and group_id column, this way an object that belongs to many groups would have several rows in the table. If I wanted to know what are the groups that my object belongs to - a piece of cake. But things get really rough if I want to get objects that (for example) belong to two particular (1 & 2) groups. I would go with something like that: SELECT DISTINCT(g1.object_id) FROM connecting_table as g1, connecting_table as g2 WHERE g1.group_id = 1 AND g2.group_id = 2 AND g1.object_id = g2.object_id; But this is getting more and more complex if I want to select objects that belong to 3, 4 and N groups at once :/ The first solution although very ugly seems to be much more clearer (and maybe even more efficient for groupn number of 2+?). My working solution for now is to have a separate table with object_id and groups_id1 column of INT type, I have assigned the first 31 existing groups to corresponding bits of the INT field. That is if I want to select objects that belong to the groups that have ids of 1,3,5 and 26 i would set the bits nr 1,2,4 and 26 of a variable $MY_INT and then query: SELECT object_id,groups_id1 FROM connecting_table WHERE BIT_COUNT(groups_id1 & $MY_INT) = 4; this seems to give me what I want but now if I want to add next 31 groups I need to create another groups_id2 column and things again get a bit complicated - I need now to check in which column the current group flag I need to find out may be stored and act accordingly (so groups with id 1-31 in column groups_id1, groups 32-63 in groups_id2 etc). Not a big problem and this solutions seems to be quite cheap (is it?) but somehow I am feeling there is a better way to do all this... Sorry for this long letter I hope I have managed to put the problem straight, I may miss something obvious here but as I said I have limited knowledge about sql. I would be much grateful for any tips. Lecho -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
