Postgres counter with self-referencing condition

Question

Given the following structure

CREATE TABLE products ( id integer NOT NULL, subcategory_id integer, stack_id integer, ) CREATE TABLE subcategories ( id integer NOT NULL, name character varying(255) ) 

Where products.stack_id is a self-reflective attitude towards products.

I'm basically trying to count subcategories connecting products to

 products.subcategory_id = subcategories.id 

but limiting the count to once in a separate stack group.

sample subcategory table

 id name 1 subcategory_1 2 subcategory_2 3 subcategory_3 

product sample table

 id subcategory_id stack_id 1 1 NULL 2 1 1 3 2 1 4 3 1 5 2 NULL 6 2 5 7 2 5 8 2 NULL 9 3 8 10 3 8 

selection of the desired result

 id name total 1 subcategory_1 1 (row 1) 2 subcategory_2 3 (row 1 + row 5 + row 8) 3 subcategory_3 2 (row 1 + 8) 

Output explanation

Subcategory id 1
If I made a simple connection with the products, I would get the products (1, 2). I only need the number of separate parent objects (stack_id is null), therefore 1 count and 2 references 1 that have already been counted, therefore do not increase the count.

Subcategory id 2
Will join (3, 5, 6, 7, 8). 3 stack_id is 1, so it counts 1. products 5, 6 and 7 of link 5, so it counts 1. product 8 counts 1.

Subcategory 3
Join (4, 9, 10). 4 links 1 and 9 and 10 both links 8.

Update

Additional, possibly confusing columns removed, sample data and output added

Answer 1

If the maximum link depth is one level, then this simple query does the job:

 select subcategory_id, name, count(*) from ( select distinct subcategory_id, coalesce(stack_id, id) stack_id from products ) sub join subcategories s on s.id = sub.subcategory_id group by 1, 2 order by 1, 2; subcategory_id | name | count ----------------+---------------+------- 1 | subcategory_1 | 1 2 | subcategory_2 | 3 3 | subcategory_3 | 2 (3 rows) 

This recursive query also works correctly on links deeper than one level:

 with recursive pr(id, subcategory_id, stack_id, stack) as ( select id, subcategory_id, stack_id, array[id] from products union select pr.id, pr.subcategory_id, products.stack_id, pr.stack_id || pr.stack from pr join products on pr.stack_id = products.id ) select distinct on (id) id, subcategory_id, stack from pr order by id, array_length(stack, 1) desc id | subcategory_id | stack ----+----------------+-------- 1 | 1 | {1} 2 | 1 | {1,2} 3 | 2 | {1,3} 4 | 3 | {1,4} 5 | 2 | {5} 6 | 2 | {5,6} 7 | 2 | {5,7} 8 | 2 | {8} 9 | 3 | {8,9} 10 | 3 | {8,10} (10 rows) 

Attach the subcategories to the dataset above:

 select subcategory_id, name, count(*) from ( select distinct subcategory_id, stack[1] from ( with recursive pr(id, subcategory_id, stack_id, stack) as ( select id, subcategory_id, stack_id, array[id] from products union select pr.id, pr.subcategory_id, products.stack_id, pr.stack_id || pr.stack from pr join products on pr.stack_id = products.id ) select distinct on (id) id, subcategory_id, stack from pr order by id, array_length(stack, 1) desc ) sub ) sub join subcategories s on s.id = sub.subcategory_id group by 1, 2 order by 1, 2 subcategory_id | name | count ----------------+---------------+------- 1 | subcategory_1 | 1 2 | subcategory_2 | 3 3 | subcategory_3 | 2 (3 rows)