Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Рекурсивный CTE, аналог Оракловской CONNECT BY PRIOR  [new]
KvantVS
Member

Откуда: Pavlodar, Kazakhstan
Сообщений: 9
Всем здрасти.
Имеется ИС, написанная на ASP. При входе - юзер авторизуется. В ней есть 1. юзеры (табл. REF_USERS_IUS), 2. много aspx-страниц (табл. MENU), и 3. разрешения (табл. PERMITTED_LINKS) - можно ли определенному юзеру открывать (и показывать в меню) определенную страницу.

Меню выполнено в виде дерева, причем ветки (не листья, а именно родительские пункты меню) тоже находятся в той же таблице, но никакую страницу собой не представляют.
Таблица разрешений хранит связь типа много-ко-многим для юзера и страницы.
Если запись есть - значит можно показывать эту страницу этому юзеру.
Если записи нет - значит страницу не выводим.

При построении дерева в Oracle с помощью CONNECT BY, в результат попадали именно все пункты по пути следования (включая родительские пункты ветки) до конечного листа.
При построении дерева в MS SQL с помощью рекурсивных CTE, в результат НЕ попадают родительские пункты меню. Потому что в таблице разрешений указаны только конечные страницы (листья).
Родительские пункты не указываются, но предполагается, что раз их дочерний элемент имеет разрешение, то их родители тоже должны выводиться.
Как это лучше реализовать? Как заставить MS SQL вывести и родительские пункты тоже?
28 ноя 14, 11:56    [16918150]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный CTE, аналог Оракловской CONNECT BY PRIOR  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
правильно написать рекурсивный запрос с использованием CTE
28 ноя 14, 12:00    [16918182]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный CTE, аналог Оракловской CONNECT BY PRIOR  [new]
Glory
Member

Откуда:
Сообщений: 104760
KvantVS
Как заставить MS SQL вывести и родительские пункты тоже?

Просто написать запрос правильно
28 ноя 14, 12:10    [16918254]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный CTE, аналог Оракловской CONNECT BY PRIOR  [new]
iap
Member

Откуда: Москва
Сообщений: 47049
KvantVS
При построении дерева в MS SQL с помощью рекурсивных CTE, в результат НЕ попадают родительские пункты меню
В результат всё-всё попадает.
Все результаты из промежуточных шагов рекурсии.
Обычная задача - отфильтровать это всё.
28 ноя 14, 12:11    [16918269]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный CTE, аналог Оракловской CONNECT BY PRIOR  [new]
KvantVS
Member

Откуда: Pavlodar, Kazakhstan
Сообщений: 9
iap
В результат всё-всё попадает.
Все результаты из промежуточных шагов рекурсии.
Обычная задача - отфильтровать это всё.

Нет, это понятно. Имеется ввиду не попадают потому, что в таблице разрешений (PERMITTED_LINKS) мы не записываем разрешения для родительских пунктов. А мы опираемся на таблицу PERMITTED_LINKS. Поэтому в MS SQL в выводимом результате их не будет. А используя Оракловский CONNECT BY, мы добивались этого.

WITH cte1(id_menu, id_parent, menu_item, link_menu, src, seq)
AS
(
	SELECT
		id_menu, 
		id_parent, 
		menu_item, 
		link_menu, 
		src, 
		SEQ
	FROM 
		MENU
	WHERE
		id_menu IN 
		(
			SELECT 
				menu.id_menu 
			FROM 
				permitted_links 
			LEFT JOIN menu 
				ON permitted_links.menu_id = menu.id_menu   
				OR permitted_links.menu_id = menu.inherit_id_menu
			WHERE 
				[user_id] = 2287 AND visible = 1 AND menu.state_id <> 0 AND LEN(menu.link_menu) > 0
		) 

	UNION ALL

	SELECT
		nplus1.id_menu, 
		nplus1.id_parent, 
		nplus1.menu_item, 
		nplus1.link_menu, 
		nplus1.src, 
		nplus1.seq
	FROM MENU AS m
	INNER JOIN cte1 ON cte1.ID_MENU = m.ID_PARENT
	WHERE m.VISIBLE = 1
)
SELECT * FROM cte1


Glory, LexusR, вот понять бы в какую сторону улучшать, пока на ум приходит только курсор
28 ноя 14, 12:43    [16918531]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный CTE, аналог Оракловской CONNECT BY PRIOR  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31781
KvantVS
Нет, это понятно. Имеется ввиду не попадают потому, что в таблице разрешений (PERMITTED_LINKS) мы не записываем разрешения для родительских пунктов. А мы опираемся на таблицу PERMITTED_LINKS. Поэтому в MS SQL в выводимом результате их не будет. А используя Оракловский CONNECT BY, мы добивались этого.
Я не понимаю, в чём разница. Что значит "используя Оракловский CONNECT BY, мы добивались этого"? Вы указывали ораклу "не показывать записи, которые не разрешены в permitted_links", а он всё равно показывает?

Вы напишите логически такой же вопрос, как и в оракле, и будет так же работать, только и всего.
28 ноя 14, 12:59    [16918628]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный CTE, аналог Оракловской CONNECT BY PRIOR  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31781
KvantVS
Имеется ввиду не попадают потому, что в таблице разрешений (PERMITTED_LINKS) мы не записываем разрешения для родительских пунктов. А мы опираемся на таблицу PERMITTED_LINKS. Поэтому в MS SQL в выводимом результате их не будет
Первый запрос в CTE (до union all) запрашивает именно родительские записи, а второй запрос уже рекурсивно подтягивает остальное.

Непонятно, зачем вы делаете проверку на permitted_links в первом запросе, если вам это не нужно.
28 ноя 14, 13:01    [16918650]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный CTE, аналог Оракловской CONNECT BY PRIOR  [new]
Glory
Member

Откуда:
Сообщений: 104760
KvantVS
Glory, LexusR, вот понять бы в какую сторону улучшать, пока на ум приходит только курсор

В сторону https://www.sql.ru/forum/127456/rekomendacii-po-oformleniu-soobshheniy-v-forume п.4 и п.6
28 ноя 14, 13:01    [16918651]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный CTE, аналог Оракловской CONNECT BY PRIOR  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31781
alexeyvg
KvantVS
Имеется ввиду не попадают потому, что в таблице разрешений (PERMITTED_LINKS) мы не записываем разрешения для родительских пунктов. А мы опираемся на таблицу PERMITTED_LINKS. Поэтому в MS SQL в выводимом результате их не будет
Первый запрос в CTE (до union all) запрашивает именно родительские записи, а второй запрос уже рекурсивно подтягивает остальное.

Непонятно, зачем вы делаете проверку на permitted_links в первом запросе, если вам это не нужно.
А, или у вас первый запрос выдаёт все меню, а потом для каждого элемента меню вы показываете аго детей? Это странно как то...

В общем, дело тут только в логике запроса, а не в отличиях языков T-SQL и PL\SQL
28 ноя 14, 13:05    [16918687]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный CTE, аналог Оракловской CONNECT BY PRIOR  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
мне кажется у вас должно быть что то типа такого
WITH cte1(id_menu, id_parent, menu_item, link_menu, src, seq)
AS
(
	SELECT
		id_menu, 
		id_parent, 
		menu_item, 
		link_menu, 
		src, 
		SEQ
	FROM MENU m
	INNER JOIN permitted_links l 
	ON  (l.menu_id = m.id_menu OR l.menu_id = m.inherit_id_menu)
	AND l.[user_id] = 2287 
	WHERE m.state_id <> 0 
	AND LEN(menu.link_menu) > 0
	AND m.visible = 1

	UNION ALL

	SELECT
		m.id_menu, 
		m.id_parent, 
		m.menu_item, 
		m.link_menu, 
		m.src, 
		m.seq
	FROM MENU AS m
	INNER JOIN cte1 ON cte1.ID_PARENT = m.ID_MENU
	WHERE m.VISIBLE = 1
)
SELECT * FROM cte1
28 ноя 14, 13:31    [16918905]     Ответить | Цитировать Сообщить модератору
 Re: Рекурсивный CTE, аналог Оракловской CONNECT BY PRIOR  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4807
KvantVS
При построении дерева в Oracle с помощью CONNECT BY, в результат попадали именно все пункты по пути следования (включая родительские пункты ветки) до конечного листа.
При построении дерева в MS SQL с помощью рекурсивных CTE, в результат НЕ попадают родительские пункты меню. Потому что в таблице разрешений указаны только конечные страницы (листья).
Родительские пункты не указываются, но предполагается, что раз их дочерний элемент имеет разрешение, то их родители тоже должны выводиться.
Как это лучше реализовать? Как заставить MS SQL вывести и родительские пункты тоже?


Как человек, который неоднократно писал и обход дерева на Oracle с помощью CONNECT BY, и обход дерева на MS SQL с помощью рекурсивного CTE, могу сказать, что обе технологии позволяют добиться идентичного результата. Синтаксис разный, но смысл конструкций одинаков.

Примеров рекурсивного CTE тут на форуме полно. Склоняюсь к тому, что вам надо 1) отступить от идеи MS SQL полное ... по сравнению с Oracle и 2) Сделать RTFM по MS SQL документации

http://msdn.microsoft.com/en-us/library/ms175972.aspx

Guidelines for Defining and Using Recursive Common Table Expressions
The following guidelines apply to defining a recursive common table expression:
The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. All CTE query definitions are anchor members unless they reference the CTE itself.
Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.
The number of columns in the anchor and recursive members must be the same.
The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.
The FROM clause of a recursive member must refer only one time to the CTE expression_name.
The following items are not allowed in the CTE_query_definition of a recursive member:
SELECT DISTINCT
GROUP BY
PIVOT (When the database compatibility level is 110 or higher. See Breaking Changes to Database Engine Features in SQL Server 2014.)
HAVING
Scalar aggregation
TOP
LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)
Subqueries
A hint applied to a recursive reference to a CTE inside a CTE_query_definition.
The following guidelines apply to using a recursive common table expression:
All columns returned by the recursive CTE are nullable regardless of the nullability of the columns returned by the participating SELECT statements.
An incorrectly composed recursive CTE may cause an infinite loop. For example, if the recursive member query definition returns the same values for both the parent and child columns, an infinite loop is created. To prevent an infinite loop, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, DELETE, or SELECT statement. This lets you control the execution of the statement until you resolve the code problem that is creating the loop. The server-wide default is 100. When 0 is specified, no limit is applied. Only one MAXRECURSION value can be specified per statement. For more information, see Query Hints (Transact-SQL).
A view that contains a recursive common table expression cannot be used to update data.
Cursors may be defined on queries using CTEs. The CTE is the select_statement argument that defines the result set of the cursor. Only fast forward-only and static (snapshot) cursors are allowed for recursive CTEs. If another cursor type is specified in a recursive CTE, the cursor type is converted to static.
Tables on remote servers may be referenced in the CTE. If the remote server is referenced in the recursive member of the CTE, a spool is created for each remote table so the tables can be repeatedly accessed locally. If it is a CTE query, Index Spool/Lazy Spools is displayed in the query plan and will have the additional WITH STACK predicate. This is one way to confirm proper recursion.
Analytic and aggregate functions in the recursive part of the CTE are applied to the set for the current recursion level and not to the set for the CTE. Functions like ROW_NUMBER operate only on the subset of data passed to them by the current recursion level and not the entire set of data pased to the recursive part of the CTE. For more information, see example K. Using analytical functions in a recursive CTE that follows.


F. Using a recursive common table expression to display a hierarchical list
The following example builds on Example D by adding the names of the manager and employees, and their respective titles. The hierarchy of managers and employees is additionally emphasized by indenting each level.
USE AdventureWorks2012;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), e.FirstName + ' ' + e.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), e.FirstName + ' ' + e.LastName)
    FROM dbo.MyEmployees AS e
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('|    ' , EmployeeLevel) +
        e.FirstName + ' ' + e.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '|    ' + FirstName + ' ' + 
                 LastName)
    FROM dbo.MyEmployees AS e
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO
28 ноя 14, 17:02    [16920934]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить