0

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

Iridio
  • 9,213
  • 4
  • 49
  • 71
Leron
  • 9,546
  • 35
  • 156
  • 257
  • You'll probably want to alias the `name` column in one (or both) of your tables so that you can access the group/user name directly from your query results. See http://www.w3schools.com/sql/sql_alias.asp – rjz Mar 30 '12 at 04:01
  • I don't think Alias is the main issue here. You can see my firs query - it's with alias, but don't see how that helps me to display the info i want. – Leron Mar 30 '12 at 04:05

2 Answers2

1

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" ');
Adi Gandra
  • 661
  • 6
  • 5
  • yes this kinda works. Althoug I don't want exactly id - because I want all groups so it's either looping the id's of the groups somehow or remove the WHERE clause, but then i get all the results and I think it becomes harder to filter this way. – Leron Mar 30 '12 at 04:13
  • I'm not exactly sure what you are asking. Do you want just each group with a count of how many users belong to that group? – Adi Gandra Mar 30 '12 at 04:15
  • No - count would be much more easier, it's a real life example so you can imagine it - I want a page where all the names of the existing groups ( fishing, sky diving, cooking, etc..) are displayed and after the name of each group - a list of the people who are participating, not just count. I thik it can't be done only with SQL it needs some sort of filtering, but can't figure out where exactly to do it. – Leron Mar 30 '12 at 04:24
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.

Broncha
  • 3,794
  • 1
  • 24
  • 34