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@riseup.net> wrote:
>> On 1/5/17 2:22 AM, Vitaly Burovoy wrote:
>>> On 1/4/17, Vitaly Burovoy <vitaly.burovoy@gmail.com> wrote:
>>>> On 1/4/17, 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.
>>>> 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
