使用PHP / CodeIgniter显示多对多关系的两个表中的数据 [英] Displaying data from two tables with many-to-many relation using PHP/CodeIgniter

问题描述

我在两个表之间做简单的连接 - 第一个被调用的用户有以下字段：

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

id || name ||

|| id || name ||

，第二个表名为groups具有相同的字段：

and the second table called groups has the same fields:

|| id || name ||

|| id || name ||

关系b / w很多是鬃毛，因为一个用户可以在许多组组将包含多个用户。所以我有一个第三个表user_group包含字段：

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 ||

|| 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();

这样我可以从组表中获取'id' 。但是我不能想出的是如何让它显示 - 组的名称和用户参与。当我从表中创建和删除的id变得非常不规则我可能有20组和一些组可能有id = 53所以jsut循环从1到number_of_groups是不够好。

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?

推荐答案

您无法在一个查询中获取该群组以及该群组中的用户。您可以在一个查询中获得每个组的并置用户列表

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

group =>用户是多对多关系，因此组中的用户将为用户返回多行。

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

如果您还需要用户详细信息的列表，而不是并置表单。
您可以遍历组列表，然后向 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();
}

现在您可以循环访问 $ group 并且可以访问 $ group-> users

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

的用户注意& 在foreach循环中的 $ g 之前。由于foreach对要传递的变量的副本进行操作，因此您将引用传递给它。

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.

这篇关于使用PHP / CodeIgniter显示多对多关系的两个表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！