#sql #mariadb #recursive-query
#sql #mariadb #рекурсивный запрос
Вопрос:
У меня есть рекурсивный запрос, который возвращает все элементы до родительского элемента, но мне нужно, чтобы он не возвращал никакого результата, если какой-либо из элементов имеет значение active = 0 .
таблица organigram:
| idElement | idClient | idSubClient | idSport | idCategory | idTeam | idParent | активный |
|---|---|---|---|---|---|---|---|
| 1 | 1 | -1 | (null) | (null) | (null) | (null) | 1 |
| 2 | 1 | -1 | 30 | (null) | (null) | 1 | 1 |
| 3 | 1 | -1 | (null) | 100 | (null) | 2 | 0 |
| 4 | 1 | -1 | (null) | (null) | 120 | 3 | 1 |
| 5 | 1 | -1 | 35 | (null) | (null) | 1 | 1 |
| 6 | 1 | -1 | (null) | 125 | (null) | 5 | 1 |
| 7 | 1 | -1 | (null) | (null) | 130 | 6 | 1 |
таблица organigram_users:
| idRef | idElement | idUser | активный |
|---|---|---|---|
| 1 | 4 | 50 | 1 |
| 1 | 7 | 50 | 1 |
Обратный запрос:
with recursive elem as (
select elem.idUser, elem.idElement as root, o.idElement, o.idParent, o.idSport, o.active
from organigram_users elem
join organigram o on o.idElement = elem.idElement
where elem.idUser = 50 and o.idClient = 1 and o.idSubClient = -1
UNION ALL
select elem.idUser, elem.root, o.idElement, o.idParent, o.idSport, o.active
from elem
join organigram o on o.idElement = elem.idParent
)
select idElement, idParent, active,idsport, root
from elem
group by idelement
У меня есть организационная диаграмма, в которой я могу назначить пользователя нескольким элементам организационной диаграммы. В зависимости от элемента, которому он назначен, я хотел бы проверить всех родителей, если столбец «активный» равен 1 или 0. Если один из элементов parnet имеет active = 0, то он ничего не возвращает.
В этом случае пользователь назначается элементам 4 и 7. Запрос должен показать мне только родительские элементы элемента 7, потому что родительский элемент элемента 4 активен = 0
Ожидаемый результат (поскольку третий элемент имеет active = 0): 1 5 6 7
Но результат таков: 1 2 3 4 5 6 7
Я проверил свой рекурсивный запрос здесь с помощью MariaDB
Ответ №1:
Я думаю, что вам нужно:
with recursive elem as (
select elem.idUser, elem.idElement as root, o.idElement, o.idParent, o.idSport, o.active
from organigram_users elem
join organigram o on o.idElement = elem.idElement
where elem.idUser = 50 and o.idClient = 1 and o.idSubClient = -1
UNION ALL
select elem.idUser, elem.root, o.idElement, o.idParent, o.idSport, o.active
from elem
join organigram o on o.idElement = elem.idParent
), elements
as (
select idElement, idParent, active,idsport, root
from elem
group by idelement
)
select * from elements
where not exists (select 'x' from elements where active=false)
Комментарии:
1. Я меняю свой вопрос. В моем случае пользователь может быть назначен более чем одному элементу, и мне нужно получить все родительские элементы, пока элемент с idPadren не станет нулевым, но только если все родительские элементы активны = 1
2. Что вы обычно можете сделать в подобном случае, так это добавить запоминаемое значение для всех строк. Поэтому я бы выбрал 2 строковых значения y / n для представления активного / неактивного, а затем объединил их для каждой рекурсии. Итак, для строки привязки, если она активна, y else n как audittrail . Для последовательных строк (объединение всех частей), elem.audittrail (если активный, y else n).
3. В конце каждая строка будет иметь журнал аудита того, откуда она взялась, и все, что вам нужно сделать, это отфильтровать, где audittrail не похож на ‘%n%’. Что здесь немного сбивает с толку, так это то, что вы начинаете с дочерних элементов и находите родителей, в то время как рекурсия обычно идет от более низкой плотности узлов к более высокой. Возможно, дочерние элементы являются родителями в этой конкретной ситуации. В любом случае, как только вы начнете просматривать журнал аудита пути рекурсии, это должно дать вам несколько идей.