mysql - 独占查询、左外连接或添加查询

Question

我有两个表:

nets_permissions

    user_id INT NOT NULL,
    network_id BIGINT UNSIGNED NOT NULL,
    perm INT(3) NOT NULL,
    PRIMARY KEY(user_id, network_id)


devices_permissions

    user_id INT NOT NULL,
    network_id BIGINT UNSIGNED NOT NULL,
    device_id INT UNSIGNED NOT NULL,
    perm INT(3) NOT NULL,
    PRIMARY KEY(user_id, network_id, device_id),

nets_permissions 表 拥有每个用户的权限。用户可以有不同的 Perm 值:1 表示读取权限，2 表示写入权限，4 表示读取+命令权限。网络管理员的 perm 值为 3。

第二个表也是如此，但这次是针对特定网络中的设备。当网络和设备管理员授予用户权限时，用户将被添加到此表中。但网络管理员并未在此表中注册。他仅在nets_permission表中perm=3。

我必须创建一个查询，在 Tornado Web 服务器的处理程序中选择当前用户的权限。代码的形式如下:

# Retrieve the current user 
    usr = self.get_current_user()
    usr_id = usr['id']
    self.lock_tables("read", ['nets_permissions as n, devices_permissions as d'])
    usrperm = self.db.query("SELECT * FROM nets_permissions as n 
         LEFT OUTER JOIN devices_permissions as d 
              ON n.network_id = d.network_id WHERE d.user_id=%s 
                 AND d.device_id=%s", 
         int(usr_id), sens.id);
    self.unlock_tables()

在usr_id中我检索的是当前用户的id。

当前用户可以是网络的管理员(因此他只在nets_permissions表中perm=3)或对特定设备具有某些权限的用户(所以他只在devices_permissions表中具有某些值烫发)。

在查询结果“userperm”中，我将使用一个整数来比较 html 页面中的权限值，并向用户显示或不显示某些内容。

抱歉我的英语不好，但很难为我解释这个问题。我不知道我可以构造查询来获得我想要的结果。

非常感谢您的帮助。

Answer 1

SELECT distinct 'net', network_id, perm
FROM nets_permissions n
JOIN devices_permissions d
USING (user_id, network_id)
WHERE n.user_id = '%s'
AND d.device_id = '%s'

UNION ALL

SELECT 'device', network_id, perm
FROM devices_permissions
WHERE user_id = '%s'
AND device_id = '%s'

但是，您的数据模型似乎存在问题。如果设备没有拥有该设备权限的用户，则 devices_permissions 中不会有行，因此我们将无法找到其 network_id。您需要更好地规范化:应该有一个 device_network 表来关联它们。