sql - 无法引用联合之外的列

Question

我正在我的应用程序中实现基于角色的访问控制，如果一个角色继承另一个角色，则需要选择性地包含一个额外的行。

我尝试在子查询中使用带有 where 子句的联合。我正在使用 postgres。

使用 knex，我的代码如下所示:

export const rowToJSONArray = (query: QueryBuilder, column: string) => {
  const id = randomBytes(8).toString("hex")

  return `
    ARRAY(
      SELECT
        row_to_json("${id}")
      FROM ( ${query.toQuery()} ) as "${id}"
    ) as "${column}"
  `
}

export const getPermissionListQuery = (as = "Role_permissions") => {
  const subquery = db
    .select("name", "type")
    .from("Permissions")
    .join("RolePermissions", "RolePermissions.permissionId", "Permissions.id")
    .where("RolePermissions.roleId", db.raw(`"Roles"."id"`))

  return rowToJSONArray(subquery, as)
}

export const getUserRoleListQuery = (withPermissions = false) => {
  const subquery = db
    .select("name")
    .from("Roles")
    .leftJoin("UserRoles", "Roles.id", "UserRoles.roleId")
    .where("UserRoles.userId", db.raw(`"Users"."id"`))
    .orWhere("Roles.id", db.raw(DEFAULT_ROLE_ID)) // imply the default role always
    .union(query => {
      query
        .select("name")
        .from("Roles as UnionRoles")
        .where("Roles.inheritId", "UnionRoles.id")
    })
    .orderBy("Roles.priority", "asc")

  if (withPermissions) {
    subquery.select(db.raw(getPermissionListQuery("permissions")))
  }

  return rowToJSONArray(subquery, "User_roles")
}

因此，我希望如果理论上角色 1 继承角色 2，如果用户拥有角色 1，那么角色 2 也将包含在用户拥有的角色列表中。

但是，我却收到了这个错误:

error: invalid reference to FROM-clause entry for table "Roles"
hint: 'Perhaps you meant to reference the table alias "UnionRoles".'

Answer 1

这是生成的 Postgres 查询的样子。测试数据

create table Roles (
  id int not null
  ,inheritId int
  ,"name" varchar(50) 
);    
insert into Roles(id, inheritId, "name")
values
  (1,null,'Role 1')
 ,(2,1,'Role 2')
 ,(3,2,'Role 3')
 ,(4,2,'Role 4');

create table Users (
  id int not null
  ,"name" varchar(50) 
);
insert into Users(id, "name")
values
  (1,'User A')
 ,(2,'User B')
 ,(3,'User C')
 ,(4,'User D');

create table UserRoles (
  userId int not null
  ,roleId int not null
);
insert into UserRoles(userId, roleId)
values
   (1,1)
  ,(1,4)
  ,(3,3) -- changed it
  ,(4,4); 

使用相同递归 CTE 的两个查询。 CTE 遍历继承链以找到以任意角色开始的所有有效角色。

用户 + 有效角色:

with recursive r as(
   select id as baseid, inheritid, "name"
   from Roles
   union all
   select r.baseid, c.inheritid, c."name"
   from r
   join Roles c on c.Id = r.inheritid
)
select distinct u."name" as userName, r."name"  as roleName
from users u 
join userRoles ur on ur.userId = u.Id
join r on r.baseId = ur.roleId
order by u."name", r."name"; 

输出

username    rolename
User A  Role 1
User A  Role 2
User A  Role 4
User C  Role 1
User C  Role 2
User C  Role 3
User D  Role 1
User D  Role 2
User D  Role 4 

用户的有效角色名称

with recursive r as(
   select id as baseid,  inheritid, "name"
   from Roles
   union all
   select r.baseid,  c.inheritid, c."name"
   from r
   join Roles c on c.Id = r.inheritid
)
select distinct r."name"
from userRoles ur
join r on r.baseId = ur.roleId
where ur.userId=3
order by r."name";

输出

name
Role 1
Role 2
Role 3

我对 Knex 不太熟悉，希望这能帮助您以正确的方式构建查询。

编辑
添加权限

create table Permissions (
    id int
   ,"name" varchar(50) 
   ,"type" varchar(50) 
);

insert into Permissions (id, "name", "type")
values 
     (1, 'pm1', 'ptype1')
    ,(2, 'pm2', 'ptype1')
    ,(3, 'pm3', 'ptype2')
    ,(4, 'pm4', 'ptype2');

create table RolePermissions(
   permissionId int not null
  ,roleId int not null
);
insert into RolePermissions(permissionId, roleId)
values 
     (1, 1)  
    ,(2, 1)  
    ,(1, 3)  
    ,(2, 3)
    ,(3, 3)
    ,(2, 4)
    ,(4, 4);

用户的有效权限。注意角色的 effectiveId，在之前的查询中不需要，因为我们只使用了角色名称，角色的 "Name" 可以在这里省略。

with recursive r as(
   select id as baseid, id as effectiveId, inheritid, "name"
   from Roles
   union all
   select r.baseid, c.id, c.inheritid, c."name"
   from r
   join Roles c on c.Id = r.inheritid
)
select distinct p."name" permissionName
   , p."type" permissionType
from userRoles ur
join r on r.baseId = ur.roleId
join RolePermissions rp on r.EffectiveId = rp.roleId
join Permissions p on rp.permissionId = p.id
where ur.userId=3
order by p."name"; 

返回

permissionname  permissiontype
pm1 ptype1
pm2 ptype1
pm3 ptype2

Fiddle

您可能希望根据需要更改select 列表和order by 子句。