Question 202688 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL show resurcivly only paths that lead to specific leaves

I have two tables. One with a parent child hierarchy and one with the details of some leaves somewhere in the paths.

create table category (
category_id integer, 
category_name text, 
parent_category integer, 
level integer);

insert into category values
(1, 'cat1', null, 1), 
(2, 'cat2', null, 1), 
(3, 'cat3', 1, 2), 
(4, 'cat4', 1, 2), 
(5, 'cat5', 1, 2), 
(6, 'cat6', 4, 3), 
(7, 'cat7', 4, 3),
(8, 'cat8', 3, 3),
(9, 'cat9', 3, 3),
(10, 'cat10', 5, 3),
(11, 'cat11', 5, 3);

create table photo(
  id integer,
  value text
);

insert into photo values
  (6,'photo'),
  (9,'photo'),
  (11,'photo');

I am looking for a way to return the hierarchy of but only the paths which have a photo in the second table.

WITH RECURSIVE cte AS (
   SELECT category_id, category_name, parent_category, 1 AS level
   FROM   category
where level = 1
   UNION  ALL
   SELECT c.category_id, c.category_name, c.parent_category, ct.level + 1
   FROM   cte ct
   JOIN   category c ON c.parent_category = ct.category_id
   )
SELECT category_id,category_name,parent_category,value
FROM cte
  full join photo on cte.category_id = photo.id

The above query returns the whole hierarchy including possibly a photo:
(* the rows I do not want)

|category_id | category_name | parent_category | value |
|------------|---------------|-----------------|-------|
|          1 | cat1          |                 |       |
|          2 | cat2          |                 |       |*
|          3 | cat3          |               1 |       |
|          4 | cat4          |               1 |       |
|          5 | cat5          |               1 |       |
|          6 | cat6          |               4 | photo |
|          7 | cat7          |               4 |       |*
|          8 | cat8          |               3 |       |*
|          9 | cat9          |               3 | photo |
|         10 | cat10         |               5 |       |*
|         11 | cat11         |               5 | photo |

How do I only return the hierarchy path where the ‘leaves’ got a photo:

|category_id | category_name | parent_category | value |
|------------|---------------|-----------------|-------|
|          1 | cat1          |                 |       |
|          3 | cat3          |               1 |       |
|          4 | cat4          |               1 |       |
|          5 | cat5          |               1 |       |
|          6 | cat6          |               4 | photo |
|          9 | cat9          |               3 | photo |
|         11 | cat11         |               5 | photo |

I created a db fiddle for it: https://dbfiddle.uk/uoYXPzJ7

Initial thought where to use some sort of indexing like gist and ltree to help in my aid?

Tags:

2

Answers


  1. 0

    Start with only the categories that have a photo, then recurse upwards to their parents. Use UNION instead of UNION ALL to remove duplicates (i.e. categories that are the parent of multipe child categories):

    WITH RECURSIVE cte AS (
  SELECT category_id, category_name, parent_category, value
  FROM category
  JOIN photo ON category.category_id = photo.id
UNION
  SELECT p.category_id, p.category_name, p.parent_category, null
  FROM category p
  JOIN cte on p.category_id = cte.parent_category
)
SELECT *
FROM cte

    (updated fiddle)

    Login or Signup to reply.
  2. 0

    The recursive CTE must remove duplicate parent nodes by the use of UNION instead of UNION ALL.

    You can do:

    with recursive 
n as (
  select c.category_id, c.category_name, c.parent_category, p.value 
  from category c 
  join photo p on p.id = c.category_id
 union
  select c.category_id, c.category_name, c.parent_category, null
  from n
  join category c on c.category_id = n.parent_category
)
select * from n order by category_id

    Result:

     category_id  category_name  parent_category  value 
 ------------ -------------- ---------------- ----- 
 1            cat1           null             null  
 3            cat3           1                null  
 4            cat4           1                null  
 5            cat5           1                null  
 6            cat6           4                photo 
 9            cat9           3                photo 
 11           cat11          5                photo

    See fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search