Displaying data from two tables with many-to-many

问题:

I am making simple connection between two table - first one called users has fields :

|| id || name ||

and the second table called groups has the same fields:

|| id || name ||

The relations b/w them is many to mane because one user can be in many groups(of interest) and a group will contain more than one user. So I have a third table user_group with fields:

|| user_id || group_id ||

Where the both are foregin keys one for users and one for groups.

The goal is to make a list with all the groups and the people participating in each one. For this I made my query:

$q = $this->db->query('SELECT u.name  FROM users u 
JOIN user_group ug ON u.id = ug.user_id 
JOIN groups g ON g.id = ug.group_id WHERE ug.group_id = "4" ');

then modified it using active records:

$this->db->select('users.name');
$this->db->from('users');
$this->db->join('user_group', 'user_group.user_id = users.id');
$this->db->join('groups', 'groups.id = user_group.group_id');
$this->db->where('user_group.group_id', 3);
$q = $this->db->get();

And this way I can take the users for any group by given the 'id' from the group table. But what I can't figure out is how to make it display both - the name of the group along with the users participating. When I create and delete from the tables the id's become very unorder I may have 20 groups and some group may have id = 53 so jsut looping from 1 to number_of_groups is not good enough. What's the way to do this?

Thanks Leron

回答1:

You cannot get the group as well as the users in that group all in one query. You could get the concatenated user list for each group in one query

SELECT g.group_name, GROUP_CONCAT(u.fullname) 
FROM group g
JOIN user_group ug ON g.id = ug.id
JOIN user u ON ug.user_id = u.id
GROUP BY g.id

The group => user is Many to Many relation so a user in a group will return multiple rows for users.

If you need the list of the user details as well and not the concatenated form. You can iterate over the group list and then add a key to the users.

$groups = $this->db->get('groups')->result();

foreach($group as &$g){
  $this->db->select('users.name');
  $this->db->from('users');
  $this->db->join('user_group', 'user_group.user_id = users.id');
  $this->db->where('user_group.group_id', $g->id);
  $g->users = $this->db->get()->result();
}

Now you loop through $group and can access the users with $group->users

Notice that & before $g in the foreach loop. Since foreach operates over the copy of the variable being passed, you have pass the reference to it.

回答2:

Just select the group name too, you just have to alias the field name:

$q = $this->db->query('SELECT u.name, g.name AS `group_name` FROM users u 
JOIN user_group ug ON u.id = ug.user_id 
JOIN groups g ON g.id = ug.group_id WHERE ug.group_id = "4" ');