Блог

У меня есть следующие таблицы, USER и RECORDS . USER Таблица просто содержит имя пользователя и группу, к которой у них есть доступ. Если пользователь имеет null в качестве своей группы (например, у Боба), это означает, что он является суперпользователем и имеет доступ ко всем группам.

ПОЛЬЗОВАТЕЛЬ:

 username |  group
---------|--------
Bob      |  (null)
Kevin    |     1
John     |     2
Mary     |     1
  

Затем у меня есть RECORDS таблица со списком записей:

 record | field_name | value
----------------------------
1           AGE        92
1          HEIGHT      9'2    
1         __GROUP__     1 
 
2           AGE        11
2          HEIGHT      1'1    
2         __GROUP__     2  

3           AGE        68
3          HEIGHT      6'8  
  

Это не мой дизайн, но это то, к чему я должен запросить. В RECORDS таблице группа, к которой принадлежит запись, указывается __GROUP__ значением в столбце field_name. Итак, что я пытаюсь сделать, это получить список всех записей, к которым имеет доступ каждый пользователь.

Итак, на основе таблицы пользователя:

• У Боба есть доступ ко всем записям

• КЕВИН и МЭРИ имеют доступ только к записям, которые имеют field_name = ‘__ GROUP __’ и значение = 1

• У ДЖОНА есть доступ только к записям, которые имеют field_name = ‘__ GROUP __’ и значение = 2

Это означает

• У Боба есть доступ к записям 1, 2 и 3.
• У Кевина есть доступ только к записи 1
• Мэри имеет доступ только к записи 1
• У Джона есть доступ только к записи 2

Если в записи отсутствует ГРУППА, это означает, что только суперпользователь (пользователь с group = null) может получить к ней доступ.

Я понимаю, что если бы в RECORDS таблице был столбец «ГРУППА», это было бы проще, но, к сожалению, это не моя база данных, и я не могу вносить изменения в структуру.

** Извините, я забыл упомянуть, что пользователь может состоять в нескольких группах! Например, Мэри может быть в группах 1 и 2.

Один из вариантов использует exists :

 select u.*, r.*
from user u
cross join records r 
where u.grp is null or exists (
        select 1
        from records r1
        where 
            r1.ecord = r.record 
            and r1.field_name = '__GROUP__'
            and r1.value = u.grp
)
  

Это дает вам все records строки, к которым имеет доступ каждый пользователь.

Если вам нужен просто список идентификаторов записей, вы можете использовать агрегацию вместо этого:

 select u.userame, r.record
from users u
cross join records r
group by u.userame, u.grp, r.record
having 
    u.grp is null 
    or max(r.field_name = '__GROUP__' and r.value = u.grp) = 1