问题:postgresql :: 检索匹配精确条件的行或匹配部分条件的行数

我有表“权限”

| user_id | object_id | readable | writable |

我需要通过以下规则确定当前 user_id 是否可以访问给定的 object_id:

• 如果 object_id 根本没有记录,则返回 true

• 如果 object_id 有记录但不同的 user_id,而给定的 user_id 没有记录,则返回 false

• 如果有给定 user_id 和 object_id 的记录,则检查提供的可读和可写条件

我不确定是否可以构建不涉及嵌套查询的 SQL 查询,现在我提出了

select (
    select count(*) 
    from permission 
    where 
        object_id = 123456
    ) == 0 OR (
        select readable=true, writable=false 
        from permission 
        where user_id=1 and object_id=123456
    )

有没有更优雅的解决方案?

谢谢!

解答

尝试:

SELECT count(*) as total_count, --count of permission records for object
       bool_or(user_id = 1 AND readable) as user_readable, -- user has read permission, null if no permission record
       bool_or(user_id = 1 AND writable) as user_writable, -- user has write permission, null if no permission record
FROM permission
WHERE object_id = 123456

然后从此查询构建您的逻辑案例,例如:

SELECT total_count = 0 OR user_readable as readable,
       total_count = 0 OR user_writable as writable
FROM (first select here)