How to display distinct groups with their corresponding values using a many-to-many relationship

Asked Jul 01 '15 at 21:44

Active Jul 01 '15 at 22:24

Viewed 29 times

I have a many-to-many relationship between my group model and my sport model with the usual group_sport table as the pivot table.

Group table

ID|Group_name
1 |Group 1
2 |Group 2
3 |Group 3

group_sport table

ID|groupID|sportID
1 |1      |1
2 |1      |2
3 |1      |3
4 |2      |2
5 |2      |4
6 |3      |5
7 |3      |6
8 |3      |4

sport table

ID|sport_name
1 |football
2 |tennis
3 |golf
4 |hockey
5 |cricket
6 |athletics

What I would like to do is display all distinct groups with their corresponding sport in the view so it would look something like this

Group 1|football, golf, tennis
Group 2|tennis, hockey
Group 3|cricket, athletics, hockey

I can achieve this by doing 3 separate queries but would there be a more efficient way?

Or what I don't mind doing is displaying all sports and ordering by group but then that means I would be displaying all sports however with their groups(the groups would be repeated which is what I don't want).

In this case would you know if JQuery could help in deleting certain elements so that my groups are not repeated?

edited Jul 01 '15 at 22:24

asked Jul 01 '15 at 21:44

Billy

2,823
3
16
33

How are your tables set up ? I mean what are you joining on ? If you do a straight join with the information provided would it be 8 rows? – Holmes IV Jul 01 '15 at 22:08
that's right yeah it would be 8 rows. Just wondering how I would render this table in the view without repeating the group name or IDs. – Billy Jul 01 '15 at 22:26
Is there a limit to the number of sport that a group can have? or is it dynamic? Or a the sport in an order, Sport1,sport2, etc... – Holmes IV Jul 01 '15 at 22:28
there's 20 sports in total and yes it is dynamic – Billy Jul 01 '15 at 22:33
A consideration also is that there's no limit on the number of groups. – Billy Jul 01 '15 at 22:35
Maybe a Recursive CTE is the way to go then – Holmes IV Jul 01 '15 at 22:41
hmmm ok mate thanks. Will need to do some research on this one – Billy Jul 01 '15 at 22:45
1

I knew i was on the right trail. I think this will do it http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string – Holmes IV Jul 01 '15 at 23:06
you've just saved me a lot of time :) – Billy Jul 01 '15 at 23:11
Up vote the OP on the link :) i just found it – Holmes IV Jul 01 '15 at 23:30
Didn't know I could do that – Billy Jul 01 '15 at 23:35

How to display distinct groups with their corresponding values using a many-to-many relationship

0 Answers0