Re: Postgresql query HAVING do not work

On 1/4/17, Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> wrote:
> On 1/4/17, Gwork <nnj(at)riseup(dot)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

--
Best regards,
Vitaly Burovoy