CODEWITHSUNDEEP
postgresqlpsqlltree
JZ.
JZ.

Reputation: 21897

Return only substructure using ltree Postgres plugin

I have the following query and I'm using the postgres plugin ltree. I am trying to do something conceptually similar to cutting the tree along what you could imagine is the y-axis of the tree.

I can easily do this with the following query: 

testdb2=# SELECT * FROM test WHERE yaxis >= 3 ORDER BY yaxis;
              path              | yaxis | leaf 
--------------------------------+-------+------
 Top.Hobbies.Amateurs_Astronomy |     3 | t
 Top.Science.Astronomy          |     3 | 
 Top.Collections.Pictures       |     3 | 
 Top.Hobbies                    |     4 | 
 Top.Science                    |     4 | 
 Top.Collections                |     4 | 
 Top                            |     5 |

However I would like a tree query that does not return Top, Top.Hobbies, and Top.Science because there are nodes beneath these. I understand saying yaxis=3 would accomplish this but this set of data is a over simplification.

An important point is that these are not leaves. There is structure beneath. So I'm not looking for something that returns leaves.

This is the full set:

                     path                      | yaxis | leaf 
-----------------------------------------------+-------+------
 Top                                           |     5 | 
 Top.Science                                   |     4 | 
 Top.Science.Astronomy                         |     3 | 
 Top.Hobbies                                   |     4 | 
 Top.Collections                               |     4 | 
 Top.Collections.Pictures.Astronomy            |     2 | 
 Top.Collections.Pictures                      |     3 | 
 Top.Collections.Pictures.Astronomy.Stars      |     1 | t
 Top.Collections.Pictures.Astronomy.Galaxies   |     1 | t
 Top.Collections.Pictures.Astronomy.Astronauts |     1 | t
 Top.Hobbies.Amateurs_Astronomy                |     3 | t
 Top.Science.Astronomy.Astrophysics            |     2 | t
 Top.Science.Astronomy.Cosmology               |     2 | t

The values I would like to see are these:

              path              | yaxis | leaf 
--------------------------------+-------+------
 Top.Hobbies.Amateurs_Astronomy |     3 | t
 Top.Science.Astronomy          |     3 | 
 Top.Collections.Pictures       |     3 |

But, again, not using the value 3 exact match, because this demo data is an over simplification.

Upvotes: 1

Views: 137

Answers (1)

klin
klin

Reputation: 121834

Having the result of your first query, just find leaves in it:

with data(path) as (
--  select path from test where yaxis >= 3
    values
    ('Top.Hobbies.Amateurs_Astronomy'::ltree),
    ('Top.Science.Astronomy'),
    ('Top.Collections.Pictures'),
    ('Top.Hobbies'),
    ('Top.Science'),
    ('Top.Collections'),
    ('Top')
)
select *
from data d1
where not exists (
    select 1 
    from data d2
    where d1.path <> d2.path 
    and d1.path @> d2.path);

              path              
--------------------------------
 Top.Hobbies.Amateurs_Astronomy
 Top.Science.Astronomy
 Top.Collections.Pictures
(3 rows)

Upvotes: 1

Related Questions