根据 Mysql 和 PDO 中其他表的结果从表中选择行

Question

我有点难住了。

我有一个项目表，其中包含以下字段：ID、标题、创建者、组。

我还有另一个包含字段的组表：ID、created_by、group_name
以及最终的用户表，其中包含以下字段：ID、用户名、密码等。

这个想法是，只有由登录用户创建的项目或登录用户属于项目用户组的项目才能被该用户看到。

每个用户可以属于未指定数量的组，并且每个组可以包含未指定数量的用户。

目前我能想到的唯一方法是为每个用户创建一个单独的表（列出他们所属的所有组）。

然后，我首先在项目表中搜索由登录用户创建的项目。
其次，搜索该用户的“groups”表以查找他们所属的每个组的 id，然后再次递归搜索 items 表并加载当前找到的 group_id 与项目的组 id 匹配的每个项目。

我知道如果对少量项目、组和/或用户正确编码，这将起作用，但我怀疑每个页面需要很长时间才能加载/处理较大的表。考虑到您可能有数千个用户，因此有数千个表，为每个用户创建一个新表似乎也很混乱。

我怀疑表连接可能提供解决方案，但我目前还不知道如何提供。如果是这种情况，我非常乐意重命名表字段以实现此目的。

我当前检索项目的代码是这样的（我知道这可能并不理想）：

$query = "SELECT * FROM items WHERE user_id=:u_id";
$stmt = $conn->prepare($query);
$stmt->execute(array(':u_id'=>$_SESSION['u_id']));
$exist = '<option></option>';
while( $uRow = $stmt->fetch() ) {
    $exist .= '<option value="'.$uRow['id'].'">'.$uRow['title'].'</option>';
}
$user_groups_tbl = "user_groups_".$_SESSION['u_id'];
$query1 = "SELECT * FROM $user_groups_tbl";
$query2 = "SELECT * FROM items WHERE group_id=:group";
$stmt1 = $conn->prepare($query1);
$stmt2 = $conn->prepare($query2);
$stmt1->execute();
while( $gRow = $stmt1->fetch() ) {      
    $stmt2->execute(array(':group'=>$gRow['group_id']));
    while( $row = $stmt2->fetch() ) {
        if( $row['user_id'] !== $_SESSION['u_id'] ) {
            $exist .= '<option value="'.$uRow['id'].'">'.$uRow['title'].'</option>';
        }
    }
}
 return $exist;  

我希望我的需求和意图很明确。任何帮助将不胜感激。

Answer 1

目前我能想到的唯一方法是为每个用户创建一个单独的表（列出他们所属的所有组）。

哎呀！不要那样做！让我们使用一些规范化来重新创建您的表。

-- Our users
CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    ...
);

-- And our groups
CREATE TABLE groups (
    group_id INTEGER PRIMARY KEY,
    ...
);

-- New!  A list of all groups and the users that belong to them.
-- This is also conveniently a list of users and the groups that they belong to.
CREATE TABLE group_users (
    user_id INTEGER REFERENCES users(user_id),
    group_id INTEGER REFERENCES groups(group_id),
    UNIQUE KEY(user_id, group_id)
);

-- Finally, our mysterious "items"
CREATE TABLE items (
    item_id ...,
    title ...,
    user_id INTEGER REFERENCES users(user_id),
    group_id INTEGER REFERENCES groups(group_id)
);

鉴于：

其想法是，只有由登录用户创建的项目或登录用户属于该项目用户组的项目才能被该用户看到。

SELECT *
  FROM items
 WHERE items.user_id = ?
    OR items.group_id IN(
           SELECT group_id
             FROM group_users
            WHERE user_id = ?
       )

这应该抓取用户创建的所有项目以及属于用户所属组的所有项目。 （注意：根据您的 MySQL 版本，此查询可能无法正确优化。您可能需要将 WHERE 子句中的子查询转换为 FROM 子句中的子查询。）

Answer 2

嘿德伦特，这是个好问题。正如您所提到的，处理多对多关系（许多用户到许多组）的标准方法是使用联接表。以下是表格：

• items：ID、标题、created_by、group_ID
• groups：ID、created_by、group_name
• users：ID、用户名、密码，等等
• ，只有一个新表 - users_groups：user_ID、group_ID

现在，每当用户加入组时，只需向 user_group 表添加一行即可。例如，如果用户 327 加入组 14：

INSERT INTO users_groups SET user_ID=327, group_ID=14;

要列出用户可以访问的所有项目：

SELECT * FROM items
JOIN users_groups ON users_groups.group_ID = items.group_ID
WHERE users_groups.user_ID = :user

确保将 :user 替换为当前用户的用户 ID。另外，我可能不会使用与表中完全相同的列名，所以要小心。

列出用户所属的组：

SELECT * FROM groups
JOIN users_groups ON users_groups.group_ID = groups.ID
WHERE users_groups.user_ID = :user

Answer 3

您正在寻找的关系（我认为）称为“拥有并属于许多”或“HABTM”。您将需要带有字段 user_id 和 group_id 的所谓数据透视表。

然后，如果您有一个用户，您可以找到与该 user_id 关联的所有 group_id。然后您可以使用它来查找项目。