Looking at tutorial I can not replicate those querys to Postgresql without serious editing. But, I simply want to create a hierarchical model tree that look like Amazon. What's your general solution on that can work better and easy to maintain than Nested Set Model with update lock? On 1/5/17 2:51 AM, Vitaly Burovoy wrote: > On 1/4/17, Gwork <nnj@xxxxxxxxxx> wrote: >> On 1/5/17 2:22 AM, Vitaly Burovoy wrote: >>> On 1/4/17, Vitaly Burovoy <vitaly.burovoy@xxxxxxxxx> wrote: >>>> On 1/4/17, Gwork <nnj@xxxxxxxxxx> 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. >>>> Hello, Gwork, >>>> >>>> HAVING works fine, it is just confusing because of naming. HAVING >>>> works with column names from sources (which is "sub_tree.depth" in >>>> your example), not with names of final columns (because they get >>>> aliases later). >>>> >>>> You can check it adding depth to your SELECT part: >>>> SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth >>>> ,array_agg(depth) >>>> FROM nested_category AS node, >>>> ... >>>> >>>> and you can see that values there are not bigger than 1. >>>> >>>> You must use the same expression in HAVING clause as in SELECT one to >>>> get what you want: >>>> HAVING (COUNT(parent.name) - (sub_tree.depth + 1)) <= 1 >>>> >>>> but the result will not have "FLASH" because it has "2" even in your >>>> example. >>>> +----------------------+-------+ >>>> | name | depth | >>>> +----------------------+-------+ >>>> | PORTABLE ELECTRONICS | 0 | >>>> | MP3 PLAYERS | 1 | >>>> | CD PLAYERS | 1 | >>>> | 2 WAY RADIOS | 1 | >>>> +----------------------+-------+ >>>> (4 rows) >>> I'm sorry, forgot to mention: If you want to deal with hierarchical >>> data, Postgres has better solution - recursive query[1]. When you >>> understand principles, it will be much easier for you to write queries >>> instead of mentioned in the article. >>> >>> For example, "Retrieving a Single Path" from "Adjacency model" can be >>> written as: >>> WITH RECURSIVE >>> sel(name, parent, depth) AS ( >>> SELECT name, parent, 0 FROM category WHERE name='FLASH' >>> UNION ALL >>> SELECT c.name, c.parent, depth + 1 FROM category c, sel WHERE >>> c.category_id=sel.parent >>> ) >>> SELECT name FROM sel >>> ORDER BY depth DESC; >>> >>> which gives the same result and not depends on "parent.lft" which >>> don't have to increase. >>> >>> Moreover, you don't need to lock a table when you change data and you >>> can even add a constraint to keep consistency: >>> ALTER TABLE category ADD FOREIGN KEY (parent) REFERENCES >>> category(category_id) ON UPDATE CASCADE ON DELETE RESTRICT; >>> >>> [1]https://www.postgresql.org/docs/current/static/queries-with.html >>> >> Hi Vitaly, >> >> Your first solution worked great! >> >> I'll like try your second suggestion, I feel is gonna be a better solution >> very important to eliminate lock while updating table. >> >> I'll keep you posted if I have any further issue relating to the query. >> >> Thank you for helping out. > Feel free to ask, but do not forget to add the mailing list in CC (via > "Reply to all"). > Other people (new users) also can be interested in ways to solve issues. > > P.S. Moved from -bugs[2] to -general. > > [2]https://www.postgresql.org/message-id/flat/7582ea1e-6146-fd8d-b564-c2fe251210b2%40riseup.net -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
