Как стать автором
Поиск
Написать публикацию
Обновить

Комментарии 24

Вот блин! Вспомнил вариант своей древовидной базы, даже имена колонок одинаковые. Но в итоге был опыт отказа от таких конструкций, поскольку проект был приостановлен, и, месяц спустя, долго вспоминали что к чему, а дальнейшая разработка и вовсе стала колом. Короч, дерево можно использовать, но аккуратно, максимально просто, без наворотов. Пусть лучше пишется отдельный ключ поиска в дополнение к данным в конкретном месте проги - это куда оптимальнее и читабельнее, чем подгонять универсальность под всю базу, исходя из частного случая, который может и не возникнуть, ха-ха.

Всего голосов 1: ↑1 и ↓0+1

И еще пара других вариантов. Но тут мы рассматриваем вариант, как жить в уже сложившейся структуре БД, для которой такая выборка - лишь "одна из".

Комментарий пока не оценивали0

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

Комментарий пока не оценивали0

Наглядность и производительность SQL, увы, редко ходят вместе.

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

И тут в игру вступают рассуждения вида "дешевле платить по $1K разработчику за поддержку сложного решения следующие 10 лет, чем вложить $100K в наращивание железа для работы простого в разработке, но неэффективного".

Комментарий пока не оценивали0

Новичок не поймет твоё решение, и ему придется переделывать. В этом вся соль. Мы на практике избегаем неклассического функционала SQL, дописываем скриптом обработку. Ну, тут, конечно, всё упирается в нагрузку. Как пишут 1Сники - меняйте архитектуру решения, ха-ха, когда их ругают за тормозную работу их же проги.

Всего голосов 2: ↑1 и ↓10

Новичок не поймет твоё решение, и ему придется переделывать. В этом вся соль.

А зачем вообще подпускать новичка к непростому коду? Еще более очевидно, что позволять "непонявшему новичку" что-то переделывать - чисто организационный fail.

Всего голосов 2: ↑2 и ↓0+2

Мы тебя троллим, ведь это непрактично. Сколько раз уже видел, как чуваки перепиливают деревА в плоскую табличку, и, разумеется, свалка становится еще больше.

Комментарий пока не оценивали0

Есть ощущение, что репрезентативное именование заметно помогло бы читабельности запроса.

Комментарий пока не оценивали0

Вроде как не особо...

альтернативное именование
WITH RECURSIVE rec_outer AS(
  SELECT
    recO   -- тут будет лежать сразу цельная запись, чтобы не перечитывать ее повторно
  , 0 iter -- счетчик найденных записей
  FROM
    hier recO
  WHERE
    id = 10000 -- последняя прочитанная на предыдущей итерации запись
UNION ALL
  SELECT
    X.recI
  , iter + 1
  FROM
    rec_outer RO
  , LATERAL (
      -- первый потомок
      (
        SELECT
          recI
        FROM
          hier recI
        WHERE
          pid = (RO.recO).id
        ORDER BY
          ord
        LIMIT 1
      )
      UNION ALL
      -- ближайший сосед
      (
        SELECT
          recI
        FROM
          hier recI
        WHERE
          pid = (RO.recO).pid AND
          ord > (RO.recO).ord
        ORDER BY
          ord
        LIMIT 1
      )
      UNION ALL
      -- ближайший сосед ближайшего предка
      (
        WITH RECURSIVE rec_inner AS(
          SELECT
            RO.recO prnt    -- берем текущую запись в качестве "предка"
          , NULL::hier nbgh -- "соседа" у нее заведомо нет
        UNION ALL
          SELECT
            recI
          , (
              -- сработает только один из блоков с противоположными условиями
              (
                SELECT
                  nbgh_w_prnt
                FROM
                  hier nbgh_w_prnt
                WHERE
                  (RI.prnt).pid IS NOT NULL AND -- мы еще не в корне
                  pid = (RI.prnt).pid AND
                  ord > (RI.prnt).ord
                ORDER BY
                  pid, ord -- подгоняем под индекс
                LIMIT 1
              )
              UNION ALL
              (
                SELECT
                  nbgh_no_prnt
                FROM
                  hier nbgh_no_prnt
                WHERE
                  (RI.prnt).pid IS NULL AND     -- уже в корне
                  pid IS NULL AND
                  ord > (RI.prnt).ord
                ORDER BY
                  pid, ord -- подгоняем под индекс
                LIMIT 1
              )
              LIMIT 1
            )
          FROM
            rec_inner RI
          INNER JOIN
            hier recI
              ON recI.id = (RI.prnt).pid -- рекурсивный обход "вверх" по иерархии
        )
        SELECT
          nbgh
        FROM
          rec_inner
        WHERE
          nbgh IS DISTINCT FROM NULL
        LIMIT 1
      )
      LIMIT 1
    ) X
  WHERE
    X.recI IS DISTINCT FROM NULL AND
    iter < 20 -- количество записей на странице
)
SELECT
  (recO).*  -- разворачиваем поля записи
FROM
  rec_outer
WHERE
  iter > 0; -- убираем "стартовую" запись

Комментарий пока не оценивали0
Сначала строим структуру под бесконечную вложенность — потом страдаем от наркомании в запросах. Решается вынесением в колонки ид каждого уровня, или обработкой уже после материализации. Да звучит не гибко, но выигрыш настолько огромный, что я рекомендовал бы всегда пробовать ограничить вложенность и распихать каждый уровень в свою колонку. В реальной жизни неограниченная вложенность нужна крайне редко (почти никогда), и там где она все же есть обычно выгодно пользоваться не реляционными хранилищами.
Комментарий пока не оценивали0

В реальной жизни неограниченная вложенность нужна крайне редко (почти никогда)

Расскажите это одному из наших клиентов с 60 уровнями вложенности в справочнике сотрудников. )) Понятно, что и самих сотрудников там не сотня.

Всего голосов 1: ↑1 и ↓0+1

Но их и не миллионы, можно или все материализовывать или вынести часть уровней в колонки. Всяко лучше той дичи которую приходится городить натягивая сову на глобус, т.е. выражая запросы по дереву в sql

Всего голосов 2: ↑1 и ↓10

Тогда дичь придет с другой стороны, и для работы с разными уровнями иерархии придется либо делать автогенерируемые запросы, что сразу отсекает возможность использования prepared statement, либо костылить еще похлеще, сразу сворачивая все известные поля "уровней" в массив.

Тогда уж проще по-честному использовать Materialized Path без лишних ограничений, но не факт, что жить с ним будет сильно удобнее.

Всего голосов 1: ↑1 и ↓0+1

И чуть дополню. Обычно если ктото настаивает на большой вложенности, то возможно она проистекает из визуализации, а не из реальной структуры нужной в рассчетах запросов. Например 60 уровней сотрудников это 3-4 уровня на стороне бухгалтерии. 1 холдинг - 2 компания - 3 подразделение - 4 отдел

Комментарий пока не оценивали0

Обычно даже не из визуализации, а из непонимания, как решить какую-то задачу иначе.

Например, хочется выделить начальника отдела, и создается структура с лишним узлом:

-- Отдел
   -- ФИО начальник
   -- Сотрудники отдела
      -- ФИО подчиненный 1
      -- ФИО подчиненный 2

Как минимум, можно ведь сказать, что начальник в списке всегда первый, и сэкономить уровень иерархии:

-- Отдел
   -- ФИО начальник (*)
   -- ФИО подчиненный 1
   -- ФИО подчиненный 2

Но иногда это бывает изменить "долго и дорого", особенно при всяких интеграционных проектах, когда нет возможности повлиять на исходную систему.

Комментарий пока не оценивали0

Но сотрудников и не нанимают-увольняют сотнями в секунду, эта таблица изменяется крайне редко. А потому её можно переделать в Nested Sets и вовсе забыть о рекурсивных запросах.

Комментарий пока не оценивали0

Если взять какой-нибудь условный Газпром с 500K сотрудников и текучкой на уровне хотя бы 15%/год, то это получится больше 300 человек/день - а Nested Sets очень не любят изменения структуры дерева.

Комментарий пока не оценивали0

Я построил web-систему на nodejs с нуля (в терминах явы) и база (mysql) используется для хранения деревьев. Несколько лет перебирал варианты в поисках наилучшего и нашел - только тупо в лоб.

Комментарий пока не оценивали0

Можно вынести группирующие узлы в отдельную таблицу (отделы/группы) и применить Nested Sets для них.


Или просто резервировать по 1000 слотов для каждого отдела.

Комментарий пока не оценивали0

Хочу сказать, что ваша статья кое-кому помогла (вернее, уже цикл статей) и вы не зря старались. Спасибо!

Комментарий пока не оценивали0

Для чего в запросе удаления дублирующихся строк сначала разбивать массив на последовательность строк, затем снова собирать это в массив?

Массив можно сразу передать в ANY, а чтобы Postgres не считал подзапрос полседовательностью массивов, в которой нужно найти ctid, результат подзапроса можно кастануть:

DELETE FROM
  hier
WHERE
  ctid = ANY((
    SELECT
      (array_agg(ctid))[2:]
    FROM
      hier
    GROUP BY
      pid, ord
    HAVING
      count(*) > 1
  )::tid[]);

Комментарий пока не оценивали0

В таком виде запрос перестает работать при наличии хотя бы двух дублирующихся пар:

CREATE TABLE hier_test AS
SELECT
  *
FROM
  (
    VALUES
      (1, 1)
    , (1, 1)
    , (2, 2)
    , (2, 2)
  ) T(pid, ord);

DELETE FROM
  hier_test
WHERE
  ctid = ANY((
    SELECT
      (array_agg(ctid))[2:]
    FROM
      hier_test
    GROUP BY
      pid, ord
    HAVING
      count(*) > 1
  )::tid[]);
-- ERROR:  more than one row returned by a subquery used as an expression

Комментарий пока не оценивали0
Зарегистрируйтесь на Хабре, чтобы оставить комментарий

Ваш аккаунт

Разделы

Информация

Услуги

Техническая поддержка
© 2006–2024, Habr