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

Question

我有像这样的表“权限”

| user_id | object_id | readable | writable |

我需要确定给定的 object_id 是否可以被当前的 user_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
    )

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

谢谢!

Answer 1

尝试:

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)