Re: [BUGS] Postgresql query HAVING do not work
От | Merlin Moncure |
---|---|
Тема | Re: [BUGS] Postgresql query HAVING do not work |
Дата | |
Msg-id | CAHyXU0zP1b5sETtyK2GZ18UOWsURB7Dg0qhQ-18H8SKw8gGDOQ@mail.gmail.com обсуждение исходный текст |
Ответ на | [BUGS] Postgresql query HAVING do not work (Gwork <nnj@riseup.net>) |
Список | pgsql-bugs |
On Wed, Jan 4, 2017 at 7:23 PM, Gwork <nnj@riseup.net> wrote: > Version: Postgresql 9.5 > OS: Debian 8 jessie run on docker > > Following this tutorial The Nested Set Model on > http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ > > > Section: Depth of a Sub-Tree. > SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth > FROM nested_category AS node, > nested_category AS parent, > nested_category AS sub_parent, > ( > SELECT node.name, (COUNT(parent.name) - 1) AS depth > FROM nested_category AS node, > nested_category AS parent > WHERE node.lft BETWEEN parent.lft AND parent.rgt > AND node.name = 'PORTABLE ELECTRONICS' > GROUP BY node.name, node.lft > ORDER BY node.lft > )AS sub_tree > WHERE node.lft BETWEEN parent.lft AND parent.rgt > AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt > AND sub_parent.name = sub_tree.name > GROUP BY node.name, node.lft, sub_tree.depth > ORDER BY node.lft; > +----------------------+---------+ > | name | depth | > |----------------------+---------| > | PORTABLE ELECTRONICS | 0 | > | MP3 PLAYERS | 1 | > | FLASH | 2 | > | CD PLAYERS | 1 | > | 2 WAY RADIOS | 1 | > +----------------------+---------+ > > > Section: Find the Immediate Subordinates of a Node. > SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth > FROM nested_category AS node, > nested_category AS parent, > nested_category AS sub_parent, > ( > SELECT node.name, (COUNT(parent.name) - 1) AS depth > FROM nested_category AS node, > nested_category AS parent > WHERE node.lft BETWEEN parent.lft AND parent.rgt > AND node.name = 'PORTABLE ELECTRONICS' > GROUP BY node.name, node.lft > ORDER BY node.lft > )AS sub_tree > WHERE node.lft BETWEEN parent.lft AND parent.rgt > AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt > AND sub_parent.name = sub_tree.name > GROUP BY node.name, node.lft, sub_tree.depth > HAVING depth <= 1 > ORDER BY node.lft; > Adding 'HAVING depth <= 1' to the query still return the same results as > above instead of this: > +----------------------+---------+ > | name | depth | > |----------------------+---------| > | PORTABLE ELECTRONICS | 0 | > | MP3 PLAYERS | 1 | > | FLASH | 1 | > | CD PLAYERS | 1 | > | 2 WAY RADIOS | 1 | > +----------------------+---------+ > > I don't know if I'm doing anything wrong? > > Note: Edit the post query by adding node.lft, sub_tree.depth to the > GROUP BY. FYI, "The Nested Set Model" has terrible insertion performance. Any row inserted can cause the entire table to be updated. Not good. Materialized path approaches tend to be better in every sense. In postgres, "The Adjacency List Model" can be queried via WITH RECURSIVE. This mitigates a lot of the downsides that the OP mentions. I guess mysql does not have that feature? merlin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
В списке pgsql-bugs по дате отправления:
Предыдущее
От: Vitaly BurovoyДата:
Сообщение: Re: [BUGS][PATCH] BUG #14486: Inserting and selecting interval havedifferent constraints
Следующее
От: Tom LaneДата:
Сообщение: Re: [HACKERS] Re: [BUGS][PATCH] BUG #14486: Inserting and selecting interval have different constraints