ReSQL.ru
     
powered by simpleCommunicator - 2.0.28     © 2024 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Map
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / with recursion
5 сообщений из 5, страница 1 из 1
with recursion
    #40138931
Nicolle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nicolle
Участник
Здравствуйте! Есть рекурсивный запрос такого плана:
Код: PL/pgSQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
with recursive query(inIdParent, inIdType, level) as
    (select distinct v.inIdParent, v.inIdType, 1
     from view v
     where v.inIdChild = 123 /*idChild*/
     union
     select distinct l.inIdParent, l.inIdType, rec.Level + 1
     from (select distinct rec.inIdParent, rec.Level from query rec) rec
     inner join view v on v.inIdChild = rec.inIdParent
    )
    select *
    from query v
    inner join types tt on tt.inId = v.inIdType
    where v.inIdParent = 456--/*IdRoot*/
      and tt.inIdLink in (1/*IdLink1*/, 2/*IdLink2*/);
Для идентификатора idChild разматывается все дерево наверх до нужного родителя IdRoot по связям IdLink1 и IdLink2.
Не нравится такой момент - для idChild мы всегда находим все уровни до конца и уже потом ищем подходящего родителя по нужным нам условиям.
Можно ли как-то переписать запрос так, чтобы он останавливался и не разматывал дерево дальше, если найдет нужного родителя?

С помощью цикла while я такое поведение описала так:

Создаю временную таблицу tmp и задаю переменную v_inLevel = 1.
Код: PL/pgSQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
insert into tmp (inIdParent, inIdType, inLevel)
  select distinct v.inIdParent, v.inIdType, v_inLevel as inLevel
  from view v
  where v.inIdChild = 123 /*idChild*/;

  while not v_done and v_inLevel < 4000 loop
    v_inLevel := v_inLevel + 1;

    --Если дошли до версии нужного родительского объекта, то выход
    if exists (select 1
               from tmp v
               inner join types tt on tt.inId = v.inIdType
               where v.inLevel = v_inLevel - 1
                 and v.inIdParent = 456--/*IdRoot*/
                 and tt.inIdLink in (1/*IdLink1*/, 2/*IdLink2*/)
               limit 1) then
      v_done := found;
      return;
    end if;

    insert into tmp (inIdParent, inIdType, inLevel)
    select distinct l.inIdParent, l.inIdType, v_inLevel as inLevel
    from (select distinct rec.inIdParent from tmp rec where rec.inLevel = v_inLevel - 1) rec
    inner join view l on l.inIdChild = rec.inIdParent
    where not exists (select 1 from tmp r where r.inIdParent = l.inIdParent and r.inIdTypeRel = l.inIdTypeRel);
    v_done := not found;
  end loop;
Возможно ли похожее поведение реализовать с помощью with recursive?
...
Рейтинг: 0 / 0
23.10.2024, 11:44
| Ответить | Цитировать | Написать | Ответы  
with recursion
    #40138936
Фотография SeaGate
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SeaGate
Участник
Nicolle [игнорируется] 

почему нерабочий запрос? что такое "l"? Зачем union и distinct?
Код: SQL
1.
2.
3.
select distinct l.inIdParent, l.inIdType, rec.Level + 1
     from (select distinct rec.inIdParent, rec.Level from query rec) rec
     inner join view v on v.inIdChild = rec.inIdParent
Что-нибудь такое:
https://onecompiler.com/postgresql/42vxnwxp5
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
create view view(inIdChild, inIdParent, inIdType) as (
  values (123, 100, 1),
         (100, 200, 2),
         (200, 456, 2),
         (456, 789, 2)
  );

create view types(inId, inIdLink) as (
  values (1, 10),
         (2, 2)
);

  with recursive query(inIdParent, inIdType, level, done) as
    (select v.inIdParent, v.inIdType, 1, cast(null as integer)
     from view v
     where v.inIdChild = 123 /*idChild*/
     union all
     select v.inIdParent, v.inIdType, rec.Level + 1,
            (select case when v.inIdParent = 456 and tt.inIdLink in (1, 2) then tt.inIdLink end from types tt where tt.inId = v.inIdType)
     from query rec
     inner join view v on v.inIdChild = rec.inIdParent
     where done is null
    )
    select *
    from query v;
По explain analyze делает 3 loops. если убирать done, то 4.
Данные нужно предоставлять как в примере выше, если чего-то не хватает.
...
Рейтинг: 0 / 0
23.10.2024, 13:15
| Ответить | Цитировать | Написать  
with recursion
    #40138941
Nicolle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nicolle
Участник
Извиняюсь, поправила код, distinct можно убрать:
Код: PL/pgSQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
with recursive query(inIdParent, inIdType, level) as
    (select distinct v.inIdParent, v.inIdType, 1
     from view v
     where v.inIdChild = 123 /*idChild*/
     union
     select v.inIdParent, v.inIdType, rec.Level + 1
     from (select distinct rec.inIdParent, rec.Level from query rec) rec
     inner join view v on v.inIdChild = rec.inIdParent
    )
    select *
    from query v
    inner join types tt on tt.inId = v.inIdType
    where v.inIdParent = 456--/*IdRoot*/
      and tt.inIdLink in (1/*IdLink1*/, 2/*IdLink2*/);
Если в ваш пример добавить для объекта 123 еще одного родителя (например с ид 500, который удовлетворяет условиям - то будет работать неверно)
Код: PL/pgSQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
--drop view view
create view view(inIdChild, inIdParent, inIdType) as (
  values (123, 100, 1),
         (100, 200, 2),
         (200, 456, 2),
         (456, 789, 2),
         (123, 500, 1)
  );

create view types(inId, inIdLink) as (
  values (1, 10),
         (2, 2)
);

  with recursive query(inIdParent, inIdType, level, done) as
    (select v.inIdParent, v.inIdType, 1, cast(null as integer)
     from view v
     where v.inIdChild = 123 /*idChild*/
     union all
     select v.inIdParent, v.inIdType, rec.Level + 1,
            (select case when v.inIdParent = 500 and tt.inIdLink in (1, 2) then tt.inIdLink end from types tt where tt.inId = v.inIdType)
     from query rec
     inner join view v on v.inIdChild = rec.inIdParent
     where done is null
    )
    select *
    from query v;
Результат - https://onecompiler.com/postgresql/42w23w2up
...
Рейтинг: 0 / 0
Вчера, 08:09
| Ответить | Цитировать | Написать | Ответы  
with recursion
    #40138946
Фотография SeaGate
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SeaGate
Участник
Nicolle [игнорируется] 
Код
1.
 (123, 500, 1)
не удовлетворяет условиям, т.к. inIdType=1 соответствует types.inIdLink = 10, а по условиям нужны inIdLink in (1,2).
Здесь нужно проверять условие на первой итерации для этого случая.
Код: SQL
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
create view view(inIdChild, inIdParent, inIdType) as (
  values (123, 100, 1),
         (100, 200, 2),
         (200, 456, 2),
         (456, 789, 2),
         (123, 500, 2)
  );

create view types(inId, inIdLink) as (
  values (1, 10),
         (2, 2)
);

  with recursive query(inIdParent, inIdType, level, done) as
    (select v.inIdParent, v.inIdType, 1, (select case when v.inIdParent = 500 and tt.inIdLink in (1, 2) then tt.inIdLink end from types tt where tt.inId = v.inIdType)
     from view v
     where v.inIdChild = 123 /*idChild*/
     union all
     select v.inIdParent, v.inIdType, rec.Level + 1,
            (select case when v.inIdParent = 500 and tt.inIdLink in (1, 2) then tt.inIdLink end from types tt where tt.inId = v.inIdType)
     from query rec
     inner join view v on v.inIdChild = rec.inIdParent
     where done is null
    )
    select *
    from query v;
Работает для 500 и 789. https://onecompiler.com/postgresql/42w2mp3mu
...
Рейтинг: 0 / 0
Вчера, 12:31
| Ответить | Цитировать | Написать | Ответы  
with recursion
    #40138951
Nicolle
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Nicolle
Участник
SeaGate [игнорируется] 

Понятно. Но при этом получается, что рекурсия все равно строит полностью все дерево вверх. А мне бы хотелось при первом нахождении нужного условия выходить из рекурсии. Получается рекурсия в данном случае проигрывает циклу или я не умею ее готовить.) В цикле если мы находим нужного родителя мы дальше не ищем - сразу выходим.
...
Рейтинг: 0 / 0
Сегодня, 09:38
| Ответить | Цитировать | Написать  
5 сообщений из 5, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / with recursion
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru, Чат Кролика       Пашэ: Рисунки, Чат       Фотоальбом: Участники, Встречи
Закрыть
Start [/forum/topic.php?fid=53&msg=40138946&tid=2187056]:
 0ms
get settings:
 4ms
get forum list:
 6ms
check forum access:
 1ms
check topic access:
 1ms
track hit:
 36ms
get topic data:
 2ms
get forum data:
 1ms
get page messages:
 87ms
get tp. blocked users:
 0ms
others: 284ms
total:  422ms
созд. / загр.: 422ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]