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 referential relationship back to products.
I'm basically trying to do a count of subcategories join products on
products.subcategory_id = subcategories.id
but limiting the count to once per distinct stack group.
sample subcategories table
id name
1 subcategory_1
2 subcategory_2
3 subcategory_3
sample products 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
sample desired output
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)
Explanation of output
Subcategory id 1
If I did a simple join with products i'd get products (1, 2). I only want the number of distinct parent objects (stack_id is null) so 1 counts and 2 references 1 which was already counted so does not increase the count.
Subcategory id 2
Join would be (3, 5, 6, 7, 8). 3's stack_id is 1 so it counts 1. products 5, 6, and 7 reference 5 so that counts 1. product 8 counts 1.
Subcategory 3
Join is (4, 9, 10). 4 references 1, and 9 and 10 both reference 8.
Update
Removed extra possibly confusing columns, added sample data and output
If the maximum depth of references 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 works properly also on references 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)
Join subcategories with the above dataset:
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With