Строим Nested Set дерево без рекурсии

[qw1]

Из статьи узнал о конструкции HAVING без GROUP BY

[zerkms]

Но тут фишка в том, что конструкция `HAVING MIN(t.rgt)` работает не так, как автор считает. И ему просто повезло, что записи с минимальным t.rgt идут первыми.

Потому лучше увиденную конструкцию забыть и не вспоминать :-)

ps: а пользоваться ORDER BY + LIMIT 1

pps: в подавляющем большинстве популярных субд (постгре, sql server, оракл) запрос автора будет считаться некорректным и даже не выполнится.

[homm]

Спасибо. Теперь многие не только узнал о существовании такой конструкции, но и то, что лучше о ней не знать :)

[resurection]

Nested sets не единственное и не всегда самое лучшее решение.
www.slideshare.net/billkarwin/sql-antipatterns-strike-back (см. 77 слайд, а лучше с 48, а ещё лучше с самого начала и до конца)

[garex]

Никто и не утверждает, что лучшее и единственное. Касательно таблички на 77 слайде мы видим, что объединив AL с NS мы получаем вообще нечто идеальное, только обновление ужасное. Closure Table я не учел, но про него уже была на хабре дискуссия @ habrahabr.ru/post/67722/ и я склоняюсь к тому, что держать всех родителей на каждую запись — ужас тихий, особенно на глубоких случаях. Но опять же статья не про выбор способа, а про переход с одного из них на другой.

[boolive]

На хабре о Closure Table тоже писали habrahabr.ru/post/138947/

[zerkms]

А как, и почему работает:

> HAVING MIN(t.rgt)

?

Объясните, пожалуйста.

[garex]

Вы же уже отписали выше — как order by с limit 1 :)

Это изврат нестандартный: dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_only_full_group_by

SET @@SESSION.sql_mode = 'ONLY_FULL_GROUP_BY';

И оно ломается.

Лучше поправить на order by limit 1.

[zerkms]

Тут дело в другом. вместо MIN(t.rgt) вы могли написать хоть AVG(42) и оно работало бы точно так же :-)

Т.е. я говорил не о том, что оно совершенно не дружит со стандартами, а о том, что в случае использования конструкции HAVING любая_агрегирущюая_функция(с_любым_аргументом_кроме_NULL) всегда будет возвращаться первая строка резалт сета.

Т.е. с MAX(t.rgt) вернётся тот же самый результат.

[garex]

Да-да — я это уже понял щас. В процессе изобретения у меня для поиска левейшего элемента разные варианты запросов были — этот скорее всего пережил прочих :)

ps: Я уже заменил на order с limit`ом

[imater]

У меня mySQL 5.5.9, выдает ошибку:

Ошибка
SQL-запрос:
forever: LOOP-- Находим элемент с минимальной правой границей — самый левый в дереве
SET @parent_id := NULL;
Ответ MySQL:
#1064 — You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'forever: LOOP
— Находим элемент с минимальной п' at line 1

Что может быть? Может я зря запускаю этот запрос в phpMyAdmin?

[garex]

У меня 5.5.22-0ubuntu1. Скорее всего phpMyAdmin DELIMITER`ы не уважает.

Лучше тестить на sqlfiddle.com/#!2/7e58b/3 либо в инструменте другом, который с разделителями дружит.
Либо отдельно в phpMyAdmin создать функцию, а потом её вызвать.
Народ, кто с phpMyAdmin`ом дружит, подскажите чё каво?

[imater]

Взял код из sqlfiddle и поставил разделителями //
тогда заработало. Очень удобная функция, чтобы избавить PHP от мучений.

[AYShestakov]

А как бы это можно было бы на Postgres Sql сделать?
У большинства хостеров есть Posgres — и как правило, mysql и postgres совместимы в основном.

[garex]

Х.з. — скорее всего также большей частью. Но т.к. постгрес более расово верная бд — там вообще м.б. так не надо делать и рекурсивными запросами/функциями можно обойтись.

Но для прикола можно на SQLfiddle попробовать сменить тип БД и поиграться. Я в постгре пока не очень в тонкостях. Кто хорошо — подскажите.

[StanEgo]

Справедливости ради, то что ТС назвал AL есть ли частный и самый простой случай AL. Closure tables — это его более продвинутая версия. Причем, при грамотном приготовлении она является гораздо более эффективной, чем MP или NS. Нет такого раздутого требования и ограничений у путей и нет такого дикого оверхеда при изменениях как в NS, когда изменение одного узла может повлечь апдейт практически всей таблицы.

[garex]

Справедливости ради, то что ТС назвал AL есть лишь частный и самый простой случай AL.

Единственно мне в этом варианте не нравится «жёппа», когда у нас уровень вложенности большой и элементов на листьях много. Но хотя это только при вставке. Удаление же у нас само за собой подчищает?

ps: автор коммента имел в виду «есть лишь»

[StanEgo]

Вероятность, что вставляется сразу сто уровней редка. Но даже в таком случае, поскольку AL лежит в отдельной таблице, это минимальное число IO-операций. По сути даже вставка этих ста уровней поместилась бы в одну страницу БД. Если же представить себе материализованные пути, где в каждой записи надо резервировать место под эти 100 уровней, то вот это и называется «жёппа». И да, удаление работает.

[akalend]

храню каталог методом AL, вытягиваю весь каталог по SELECT * и сторю дерево на клиенте. Быстро и дешево.
В случае с NS — проблемы во вставками и передвижками веток. геморно…

[ohifck]

У каждого способа свои плюсы. У нас на работе деревья в 500кк узлов и надо быстро собирать аналитику по узлам — только NS дал более менее приемлемую скорость.

С другой стороны по AL очень просто выводить деревья (особенно если у тебя есть PostgreSQL, Oracle или ещё что-то соизмеримого калибра с их синтаксисом WITH… AS или START WITH… CONNECT BY ...)