How to get all the categories and sub categories in PostgreSQL?

Question

I am creating a small ecommerce demo app using .net core 5 with EF core and postgres 10.
I am trying to get all the categories and sub categories on my home page.

Here is my category hierarchy:

Men (Parent category)

• Topwear (Sub Category)
  • Formal Shirts (leaf level category)
  • T-Shirts (leaf level category)

Women

• India & Fusion Wear

  • Kurtas & Suits
  • Sarees

• FootWear

  • Flat
  • Heels

Here is my create table and insert script:

CREATE TABLE category
(
    id                uuid      DEFAULT uuid_generate_v4() PRIMARY KEY NOT NULL,
    parent_id         uuid                                             NULL,
    name              character varying(255)                           NOT NULL,
    short_description character varying(255)                           NOT NULL,
    activate_on       date                                             NULL,
    deactivate_on     date                                             NULL,
    url_segment       character varying(255)                           NOT NULL,
    meta_keywords     character varying                                NOT NULL,
    meta_description  character varying                                NOT NULL,
    sort_order        integer                                          NOT NULL,
    created_on        timestamp DEFAULT CURRENT_TIMESTAMP,
    created_by        character varying(100),
    modified_on       timestamp DEFAULT CURRENT_TIMESTAMP,
    modified_by       character varying(100),
    is_active         boolean
);



INSERT INTO public.category (id, parent_id, name, short_description, activate_on, deactivate_on, url_segment,
                             meta_keywords, meta_description, sort_order, created_on, created_by, modified_on,
                             modified_by, is_active)
VALUES ('0ca0c27c-f45b-4768-a3cb-958bfbf6df26', '6dfdf5e4-673b-4134-b4a9-3a0b14b50e6a', 'T-Shirts', 'T-Shirts',
        '2021-02-21', NULL, 'men-tshirts',
        't shirt for mens india, t shirt for men online, polo t shirts, sport t shirt, black t shirt, blue t shirt, white t shirt',
        'Men''s T-shirts - Buy T-shirts for men online in India. Choose from a wide range of polo, round, V neck & more Men''s T-shirts in various designs at Myntra.',
        1, '2021-02-21 13:17:15.652506', 'glenn', NULL, '', TRUE);
INSERT INTO public.category (id, parent_id, name, short_description, activate_on, deactivate_on, url_segment,
                             meta_keywords, meta_description, sort_order, created_on, created_by, modified_on,
                             modified_by, is_active)
VALUES ('6927198d-9240-43b0-853a-0507cad3b529', '6dfdf5e4-673b-4134-b4a9-3a0b14b50e6a', 'Formal Shirts',
        'Formal Shirts', '2021-02-21', NULL, 'men-formal-shirts',
        'Men''s formal shirts, formal shirts for men, slim fit formal shirts for men, latest formal shirts for men, formal shirts for men online, formal shirts for men India',
        'Formal Shirts for Men - Shop for trendy men''s formal shirts online in India at Myntra. ✯Free Shipping ✯Easy returns and exchanges ✯COD',
        2, '2021-02-21 13:21:16.125333', 'glenn', NULL, '', TRUE);
INSERT INTO public.category (id, parent_id, name, short_description, activate_on, deactivate_on, url_segment,
                             meta_keywords, meta_description, sort_order, created_on, created_by, modified_on,
                             modified_by, is_active)
VALUES ('0d3f9e43-12a2-4f8c-a008-a45cbf15fedd', NULL, 'Women', 'Women', '2021-02-21', NULL, 'women',
        'women online shopping, online shopping for women, women clothing, women dresses online',
        'Online Shopping for Women. Shop Online from a wide range of womens clothing, shoes, Ladies bags & more in India @ Myntra ✯Free Shipping ✯COD ✯Easy returns and exchanges.',
        0, '2021-02-21 13:26:04.447031', 'glenn', NULL, '', TRUE);
INSERT INTO public.category (id, parent_id, name, short_description, activate_on, deactivate_on, url_segment,
                             meta_keywords, meta_description, sort_order, created_on, created_by, modified_on,
                             modified_by, is_active)
VALUES ('6dfdf5e4-673b-4134-b4a9-3a0b14b50e6a', 'eb81c712-0643-45a2-b597-129a1f9892e2', 'Topwear', 'Topwear',
        '2021-02-21', NULL, 'top-wear', 'mens topwear', 'mens topwear', 1, '2021-02-21 13:12:30.518814', 'glenn',
        NULL, '', TRUE);
INSERT INTO public.category (id, parent_id, name, short_description, activate_on, deactivate_on, url_segment,
                             meta_keywords, meta_description, sort_order, created_on, created_by, modified_on,
                             modified_by, is_active)
VALUES ('eb81c712-0643-45a2-b597-129a1f9892e2', NULL, 'Men', 'Men', '2021-02-21', NULL, 'men',
        'Men Topwear, Men Topwear in India, buy Men Topwear online in india, online store for Men Topwear, Shop online for Men Topwear, online shopping for Men Topwear, Online shopping for Men Topwear in India, reviews and prices',
        'Men Topwear Online. Shop for Men Topwear in India ? Buy latest range of Men Topwear at Myntra ? Free Shipping ? COD ? Easy returns and exchanges',
        0, '2021-02-21 13:10:29.204689', 'glenn', NULL, '', TRUE);
INSERT INTO public.category (id, parent_id, name, short_description, activate_on, deactivate_on, url_segment,
                             meta_keywords, meta_description, sort_order, created_on, created_by, modified_on,
                             modified_by, is_active)
VALUES ('731b01bd-fab7-475e-adb2-6322eb9eac68', '0d3f9e43-12a2-4f8c-a008-a45cbf15fedd', 'India & Fusion Wear',
        'India & Fusion Wear', '2021-02-21', NULL, 'fusion-wear',
        'indo western fusion dresses, fusion kurtis, fusion saree, fusion dress, designer fusion wear, ethnic fusion wear, fusion wear online, indo western fusion, fusion outfits',
        'Buy Latest Collection of Women Fusion Wear online in India. Choose From Kurtas, Designer Sarees, Dresses, Lehenga Choli & more of brands like Mitera, Anouk, Biba & more at Myntra Fashion store ✯ COD ✯ Discounts',
        1, '2021-02-21 13:32:52.616545', 'glenn', NULL, '', TRUE);
INSERT INTO public.category (id, parent_id, name, short_description, activate_on, deactivate_on, url_segment,
                             meta_keywords, meta_description, sort_order, created_on, created_by, modified_on,
                             modified_by, is_active)
VALUES ('3715c7fc-3c0e-4ee4-8fa0-dfd48e8e3a3a', '731b01bd-fab7-475e-adb2-6322eb9eac68', 'Sarees', 'Sarees',
        '2021-02-21', NULL, 'saree',
        'saree, sarees, women saree, new saree collection, fancy saree, online saree shopping, saree blouse designs,latest saree,new saree design,latest saree design, sari online',
        'Saree - Pick from the designer range of Saris online for ladies & girls. Buy formal, partywear, casual Sarees in various prints, patterns, types & more at Myntra store.',
        1, '2021-02-21 13:35:26.166935', 'glenn', NULL, '', TRUE);
INSERT INTO public.category (id, parent_id, name, short_description, activate_on, deactivate_on, url_segment,
                             meta_keywords, meta_description, sort_order, created_on, created_by, modified_on,
                             modified_by, is_active)
VALUES ('34c0d8ef-19a0-4f3a-8439-6d0510cfba04', '731b01bd-fab7-475e-adb2-6322eb9eac68', 'Kurtas & Suits',
        'Kurtas & Suits', '2021-02-21', NULL, 'women-kurtas-kurtis-suits',
        'kurtis online, designer kurti, ladies designers kurtis, designers kurtis, designer kurtis online, designers kurtis, suits online',
        'Women Kurtis & Kurtas Suits - Buy Designer Kurtas & Kurtis for Ladies online. Pick from Huge range kurtis, kurtas sets & suits for Women at Myntra ✯ COD ✯ Easy returns and exchanges',
        2, '2021-02-21 13:36:59.067196', 'glenn', NULL, '', TRUE);

I want to bring all the categories from my database in such a way that it is easy to bind on UI side like the above category hierarchy.

Can anybody help me with the query?

Answer 1

---------- Revised
The determination of Parent, Sub, Leaf is relative simple:

1. Row Child id is null then Parent.
2. Row child id is not null and row has child row then Sub.
3. Otherwise leaf.

The only difficultly is 2 above.
Now this can be flushed out a couple ways:

• With the same basic recursive CTE apply both lead and lag functions in the main query then check for nulls. But with a hierarchy this gets messy in a hurry. So lets reject that idea.
• With slight modification to the CTE the non-recursive select is Parents. In the recursive select see if the current has a child row then Sub Category.

Modifying the CTE is not terribly bad, but select for it being a parent is still somewhat nasty looking. So the following abstracts this into a simple SQL function that returns the appropriate type (given that the id is known to be a child).

create or replace 
function category_sub_or_leaf(id_in uuid)
  returns text
  language sql 
  strict 
as $$ 
    select case when exists (select null
                               from category
                               where parent_id = id_in 
                            )   
                then '(Sub Category)'
                else '(leaf level category)'
             end  ;
$$;

And the resulting query as:

with recursive heir (id, parent_id, name, short_description, sort_order, root_path, lev, cat_level) as
    ( select  id, parent_id, name, short_description, sort_order,(name)::text, 1, '(Parent Category)'
        from category 
       where parent_id is null
      union all 
      select c.id, c.parent_id, c.name, c.short_description, c.sort_order, root_path || '>'||(c.name)::text, lev+1    
           , category_sub_or_leaf(c.id) 
        from category c
        join heir     h 
          on c.parent_id = h.id
    ) --select * from heir;                                                              
select  (rpad(' ', 4*(lev-1),' ') || short_description) || ' ' || cat_level   description
  from heir
 order by root_path,lev,sort_order;

See revised demo: Demo also flushes out Women's Foot Ware to provide and extra layer in Sub Category. I.E 2 layers of sub category. But as I did not want that much typing I modified table to not require anything but the necessary columns.

---------- Orig
What you are looking for is a recursive cte. Here the first (non-recursive term) select gets the rows which do not have parent_id defined. Then the recursive term (after union) looks back to the previous row and retrieves the child rows. (A poor description at best.) The query builds the path back to the first parent and the current depth. These generated columns are then used to provide order and indentation of the results. See Demo.

with recursive heir as 
     ( select  id, parent_id, name, short_description, (name)::text path, 1 lev
         from category 
        where parent_id is null
       union all 
       select c.id, c.parent_id, c.name, c.short_description, path ||'>'||(c.name)::text, lev+1 
         from category c
         join heir     h 
           on c.parent_id = h.id
     ) 
select (rpad(' ', 4*(lev-1),' ') || short_description) short_description
  from heir
 order by path, lev ;

Note: Demo built with Postgres 13 so function gen_random_uuid() substituted for uuid_generate_v4().