Newbie Many-to-many and CList view question

Yii 1.1 General Discussion
1

I am having trouble getting my head around how to accomplish the following:

I have the following tables:

user table

  • id

  • name

  • email

user_group table

  • user_id

  • group_id

group table

  • id

  • group_name

There is a many-to-many relationship between the user table and the group table. Now what I am trying to do build a browse users page which lists all the users in the system along with the groups that they belong to, so the page would look something like this:

Name: John Doe

Groups: football, tennis, swimming

Name: Jane Doe

Groups: hockey, basketball

Name: Jim Doe

Groups: hockey, football, rugby

etc. etc.

Bear in mind that I’ve simplified the tables and relationships here for the purpose of this post, in reality, the SQL I am building is much more complex.

Anyway, I am still learning SQL, so prefer to avoid using Yii’s CActiveDataProvider to build the more complex queries in the system, instead I am using CSqlDataProvider and the Query Builder to create the SQL for this page.

My question now is, I have the following SQL:

SELECT user.name, group.name FROM user, user_group, group WHERE user.id = user_group.user_id AND group.id = user_group.group_id GROUP BY user.id, group.id

which returns results as follows:

  1. John Doe | football

  2. John Doe | tennis

  3. John Doe | swimming

  4. Jane Doe | hockey

  5. Jane Doe | basketball

etc. etc.

And when i pass these results into CListView, I get the following results displayed in the HTML page:

ame: John Doe

Groups: football

Name: John Doe

Groups: tennis

Name: John Doe

Groups: swimming

Name: Jane Doe

Groups: hockey

Name: Jane Doe

Groups: basketball

etc.

Is there a simple way to get the page to display the groups so that they are in a comma separated list for each user, using CListView and CSqlDataProvider? If not, what is the best practice solution to this issue? The Yii documentation does not provide all that much information on many-to-many relationships, so any help would be much appreciated!

Many thanks.

2

You need to make a join. See the docs.

3

Er, maybe I’m misunderstanding you… but I believe I am making a join:

SELECT user.name, group.name

FROM user, user_group, group <- this is a join, no?

WHERE user.id = user_group.user_id AND group.id = user_group.group_id

GROUP BY user.id, group.id

In order to make this work with Yii, do I need to customise my own code to deal with this kind of grouping? Or can this be done quite easily with CListView?

4

in my idea , i can write a function in user model : logic may be :




               public function getMyGroup(){


                     //here you can find all groups this user belong to ,  you can use $this->id in this method context <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/tongue.gif' class='bbc_emoticon' alt=':P' /> 

                     /*

                      use many to many relation , or sql to fetch the group_id and group_name

                           then return a array('g_id'=>'g_name');

                           now you can generate a link:  <a href="link_to_group">g_name</a>

                            

                           join them to this form: <a>gn1</a> , <a>gn2</a>

                       */


                }

in your view ,just call user 's this method : echo $model->getMyGroup();

5

Which RDBMS do you use? If MySQL, try GROUP_CONCAT.