CODEWITHSUNDEEP
sqlpostgresql
Mateusz Urbański
Mateusz Urbański

Reputation: 7862

Count nested categories records

I have the following schema in my database:

CREATE TABLE categories (
    id bigint NOT NULL,
    name character varying NOT NULL,
    ancestry character varying
);

CREATE TABLE video_chats (
    id bigint NOT NULL,
    name character varying NOT NULL,
    category_id bigint NOT NULL
);

and I have some nested categories with video chats:

INSERT INTO categories (id, name, ancestry)
    VALUES (1, 'Test category', null);

INSERT INTO categories (id, name, ancestry)
    VALUES (2, 'Test category subcategory', '1');    

INSERT INTO categories (id, name, ancestry)
    VALUES (3, 'Test category subcategory', '1/2');

INSERT INTO video_chats (id, name, category_id)
    VALUES (1, 'Test Video 1', '2');    

INSERT INTO video_chats (id, name, category_id)
    VALUES (2, 'Test Video 2', '3');

Now I want to have sql query that counts video chats for category and its sub categories. It should return following results:

Here is the sqlfiddle with my schema:

http://sqlfiddle.com/#!17/f1cee/2

How can I do this in PostgreSQL?

Upvotes: 1

Views: 149

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51446

t=# with a as (select v.id,unnest(array_append(string_to_array(ancestry,'/')::bigint[],c.id)) cg from video_chats v join categories c on v.category_id = c.id)
select cg,count(1) from a group by cg order by cg;
 cg | count
----+-------
  1 |     2
  2 |     2
  3 |     1
(3 rows)

my results differ from expected:

counts video chats for category and its sub categories

so video chat id 1 belongs to category 2 and thus (because 2 is a subcategory of 1 to 1, it is vc1 categories: 2,1 video chat 2 belongs to category 3, and thus to 2 and thus to 1, so vc2 caterories: 3,2,1

In my query it is CTE body:

t=# select v.id,array_append(string_to_array(ancestry,'/')::bigint[],c.id) from video_chats v join categories c on v.category_id = c.id;        id | array_append
----+--------------
  1 | {1,2}
  2 | {1,2,3}
(2 rows)

and thus resulting to:

  • Test category(1) - 2 Video chats
  • Test category subcategory(1) - 2 Video chats
  • Test category subcategory subcategory(3) - 1 Video chat

Upvotes: 1

Related Questions