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

Question

我有点难过。

我有一个包含以下字段的项目表:ID、标题、创建者、组。

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

这个想法是只有登录用户创建的项目或登录用户是项目用户组的一部分的项目才能被该用户看到。

每个用户都可以属于数量不限的组，每个组可以包含数量不限的用户。

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

然后我首先在项目表中搜索由登录用户创建的项目。
其次，搜索该用户的“组”表以找到他们所属的每个组的 ID，然后再次递归搜索项目表并加载当前找到的 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

The only way at the moment that I can think of doing this is to have a separate table for each user (listing all the groups of which they are members).

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

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

给定:

The idea is that only items created by the logged in user or items where the logged in user is part of the item's user group can be seen by that user.

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

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