categories.sql

/* build recursive category */
create table category (
    id uuid primary key default uuid_generate_v4(),
    name text not null,
    parent uuid,
    foreign key (parent) references category(id)
);

insert into category values (default, 'food', null);
insert into category values (default, 'snacks', (select id from category where name = 'food'));
insert into category values (default, 'chips', (select id from category where name = 'snacks'));
insert into category values (default, 'electronics', null);

/* build product */
create table product (
    id uuid primary key default uuid_generate_v4(),
    name text not null,
    category uuid,
    foreign key (category) references category(id)
);

insert into product values (default, 'ryans bbq chips', (select id from category where name = 'chips'));

/* query if product is in any of the parent categories */
with recursive category_tree(id, name, path) as (
    select id, name, ARRAY[id] as path from category where parent is null
    union all
    select category.id, category.name, category.id || category_tree.path from category
    inner join category_tree on category_tree.id = category.parent
)
select product.*, category_tree.path as category_path from product
left join category_tree on product.category = category_tree.id
where (select id from category where name = 'food') = any(category_tree.path);

/* create a recursive view! */
create recursive view category_tree(id, name, path) as (
    select id, name, ARRAY[id] as path from category where parent is null
    union all
    select category.id, category.name, category.id || category_tree.path from category
    inner join category_tree on category_tree.id = category.parent
);

/* allow uuid to be gin indexed */
CREATE OPERATOR CLASS _uuid_ops DEFAULT 
FOR TYPE _uuid USING gin AS 
OPERATOR 1 &&(anyarray, anyarray), 
OPERATOR 2 @>(anyarray, anyarray), 
OPERATOR 3 <@(anyarray, anyarray), 
OPERATOR 4 =(anyarray, anyarray), 
FUNCTION 1 uuid_cmp(uuid, uuid), 
FUNCTION 2 ginarrayextract(anyarray, internal, internal), 
FUNCTION 3 ginqueryarrayextract(anyarray, internal, smallint, internal, internal, internal, internal), 
FUNCTION 4 ginarrayconsistent(internal, smallint, anyarray, integer, internal, internal, internal, internal), 
STORAGE uuid;

/* materialized & index it for speed!*/
create materialized view category_tree_mat as select * from category_tree;

create index on category_tree_mat(id);
create index on category_tree_mat using gin (path);
create index on category_tree_mat(name);

explain select * from category_tree where (select id from category where name = 'food') = any(path);
explain select * from category_tree_mat where (select id from category where name = 'food') = any(path);