Есть таблица с ролями вида Role (Id, Parent) и таблица UserRole (User, Role). Роли могут быть иерархическими. Есть ли какой-то элегантный запрос без курсоров, чтобы получать таблицу виду FlattenUserRole (User, Role) где бы строчки по User дублировались столько раз, сколько ролей (включая родительские) имеет User.
Здравствуйте, Sаныч, Вы писали:
S>Есть таблица с ролями вида Role (Id, Parent) и таблица UserRole (User, Role). Роли могут быть иерархическими. Есть ли какой-то элегантный запрос без курсоров, чтобы получать таблицу виду FlattenUserRole (User, Role) где бы строчки по User дублировались столько раз, сколько ролей (включая родительские) имеет User.
Это называется транзитивное замыкание. "Элегантный" запрос возможен в Oracle c его Hierarchical clause. В других СУБД с поддержкой recursive CTE запрос без курсоров тоже возможен, но назвать его элегантным уже сложно (если под элегантностью понимать лаконичность).
Здравствуйте, wildwind, Вы писали:
W>Здравствуйте, Sаныч, Вы писали:
S>>Есть таблица с ролями вида Role (Id, Parent) и таблица UserRole (User, Role). Роли могут быть иерархическими. Есть ли какой-то элегантный запрос без курсоров, чтобы получать таблицу виду FlattenUserRole (User, Role) где бы строчки по User дублировались столько раз, сколько ролей (включая родительские) имеет User.
W>Это называется транзитивное замыкание. "Элегантный" запрос возможен в Oracle c его Hierarchical clause. В других СУБД с поддержкой recursive CTE запрос без курсоров тоже возможен, но назвать его элегантным уже сложно (если под элегантностью понимать лаконичность).
У нас MSSQL. Могли бы вы привести пример запроса? С CTE знаком, но больше как конструкцию для вложенных запросов. А тут нужна рекурсия.
Здравствуйте, Sаныч, Вы писали:
S>Есть таблица с ролями вида Role (Id, Parent) и таблица UserRole (User, Role). Роли могут быть иерархическими. Есть ли какой-то элегантный запрос без курсоров, чтобы получать таблицу виду FlattenUserRole (User, Role) где бы строчки по User дублировались столько раз, сколько ролей (включая родительские) имеет User.
Вот нашёл статейку по теме. На MS-SQL поддерживается начиная с версии MSSQL Server 2005. Если нужно, могу SQL-запрос накидать применимо конкретно к MS-SQL
Здравствуйте, Maniacal, Вы писали:
M>Здравствуйте, Sаныч, Вы писали:
S>>Есть таблица с ролями вида Role (Id, Parent) и таблица UserRole (User, Role). Роли могут быть иерархическими. Есть ли какой-то элегантный запрос без курсоров, чтобы получать таблицу виду FlattenUserRole (User, Role) где бы строчки по User дублировались столько раз, сколько ролей (включая родительские) имеет User.
M>Вот нашёл статейку по теме. На MS-SQL поддерживается начиная с версии MSSQL Server 2005. Если нужно, могу SQL-запрос накидать применимо конкретно к MS-SQL
Я думаю так ровно обратное, что мне требуется. Я не хочу представлять в иерархии. Я как раз хочу сделать плоским список.
Здравствуйте, Sаныч, Вы писали: S>У нас MSSQL. Могли бы вы привести пример запроса? С CTE знаком, но больше как конструкцию для вложенных запросов. А тут нужна рекурсия.
Рекурсия CTE как раз помогает развернуть иерархию в "плоский" вид. Если я правильно понял вашу задачу, то можно реализовать так. Проверьте только меня.
Данные
create table #role(Id int, Parent int, Name varchar(30))
create table #user(Id int, Name varchar(30))
create table #userrole(UserId int, RoleId int)
truncate table #user
insert into #user select 1, 'Иван'
insert into #user select 2, 'Пётр'
insert into #user select 3, 'Сергей'
insert into #user select 4, 'Игорь'
truncate table #role
insert into #role select 1, null, 'Администратор'
insert into #role select 2, 1, 'Менеджер'
insert into #role select 3, 1, 'Разработчик'
insert into #role select 4, 2, 'Специалист'
insert into #role select 5, null, 'Кассир'
truncate table #userrole
insert into #userrole select 1, 4
insert into #userrole select 2, 3
insert into #userrole select 3, 1
insert into #userrole select 4, 5
;with cte as
(
select r.Id as RoleId, r.Parent, r.Name as Role, ur.UserId
from #role r
join #userrole ur on r.Id = ur.RoleId
union all
select r.Id as RoleId, r.Parent, r.Name, t.UserId
from #role r
join cte t on r.Id = t.Parent
)
select c.RoleId, c.Role, u.Id, u.Name
from cte c
join #user u on c.UserId = u.Id
order by u.Name, c.Role
Здравствуйте, Sаныч, Вы писали:
S>>>Есть таблица с ролями вида Role (Id, Parent) и таблица UserRole (User, Role). Роли могут быть иерархическими. Есть ли какой-то элегантный запрос без курсоров, чтобы получать таблицу виду FlattenUserRole (User, Role) где бы строчки по User дублировались столько раз, сколько ролей (включая родительские) имеет User.
M>>Вот нашёл статейку по теме. На MS-SQL поддерживается начиная с версии MSSQL Server 2005. Если нужно, могу SQL-запрос накидать применимо конкретно к MS-SQL
S>Я думаю так ровно обратное, что мне требуется. Я не хочу представлять в иерархии. Я как раз хочу сделать плоским список.
Если бы данные о ролях хранились бы не в виде Parent-Child, а в виде Nested Sets, то такие запросы делались бы очень легко (без рекурсии)
Здравствуйте, Буравчик, Вы писали:
Б>Если бы данные о ролях хранились бы не в виде Parent-Child, а в виде Nested Sets, то такие запросы делались бы очень легко (без рекурсии)
Они и так делаются легко с помощью CTE. А nested sets требуют дополнительной поддержки при обновлении таблицы.
