Postgres count with self referential join 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 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

Sample data and desired results would really help communicate what you want to do. — Gordon Linoff
– Gordon Linoff, Commented Oct 3, 2015 at 3:01

klin · Accepted Answer · 2015-10-03 21:37:55Z

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)

Thank you for such a detailed response. I've tried out both of your approaches and they work perfectly!

Collectives™ on Stack Overflow

Postgres count with self referential join condition

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related