Menus

menus.py

create table test_menu
(
    id   serial PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL
);

insert into test_menu (name)
values ('Барское меню'),
       ('Купеческое меню'),
       ('Холопское меню');

create table test_sub_menu
(
    id      serial PRIMARY KEY,
    menu_id int                not null,
    name    VARCHAR(50) UNIQUE NOT NULL,
    constraint fk_menu
        FOREIGN KEY (menu_id)
            REFERENCES test_menu (id)
);

insert into test_sub_menu (menu_id, name)
values (1, 'Барские холодные блюда'),
       (1, 'Барские горячие блюда'),
       (1, 'Барские напитки'),
       (2, 'Купеческие холодные блюда'),
       (2, 'Купеческие горячие блюда'),
       (2, 'Купеческие напитки'),
       (3, 'Холопские холодные блюда'),
       (3, 'Холопские горячие блюда'),
       (3, 'Холопские напитки');

create table test_dishes
(
    id          serial PRIMARY KEY,
    sub_menu_id int                 not null,
    dish_name   VARCHAR(500) UNIQUE NOT NULL,
    dish_price  float               NOT NULL,
    constraint fk_menu
        FOREIGN KEY (sub_menu_id)
            REFERENCES test_sub_menu (id)
);

insert into test_dishes (sub_menu_id, dish_name, dish_price)
values (1, 'Русский салат с фермерской копченой уткой, щучьей икрой и домашним майонезом из раков', 570),
       (1, 'Лосось слабой соли, печеная тыква и свежие томаты в имбирной заправке', 590),
       (1, 'Домашний паштет с хрустящим хлебом Бриош, вяленой свеклой и вареньем из морошки', 400),
       (1, 'Бисквит', 100),

       (2, 'Запеченая форель со сливочным рататуем и пшеном, с соусом из артишоков ', 990),
       (2, 'Жареные ломтики телятины в листьях салата', 590),
       (2, 'Кулебяка с лососем и судаком, красной икрой и теплым сливочным соусом', 590),

       (3, 'Heidsieck & Co Monopole', 520),
       (3, 'Белое Шабли', 400),
       (3, 'Понто Кане', 315),

       (4, 'Сельдь домашнего пряного посола с горячим картофелем в бородинской крошке с горчично-медовым соусом и маринованным луком с брусникой', 340),
       (4, 'Щучья икра с тартаром из красного лука, ржаными гренками и сметаной', 420),
       (4, 'Чудской судак в маринаде из щавеля', 370),

       (5, 'Домашние голубцы с мясом кролика и полбой', 250),
       (5, 'Утиная грудка в медовой глазури с кремом из тыквы и сморчками', 299),
       (5, 'Пельмени с гусем, в тесте на молоке, с топленым маслом, сливочным соусом, сметаной и гусиным бульоном', 330),
       (5, 'Уха из стерляди', 120),

       (6, 'Водка Красноголовка', 20),
       (6, 'Водка Белоголовка', 60),

       (7, 'Салат с себежским угрем и редькой', 59),
       (7, 'Килька пряного посола с маринованным луком и гренками из бородинского хлеба', 44),
       (7, 'Соленое сало с муссом из хрена', 32),

       (8, 'Грибочки в сметане томлёные, поданные в горшочке из сдобного теста', 45),
       (8, 'Пирожки с зелёным луком и яйцом', 10),
       (8, 'Серые щи заквашенные с ржаной мукой', 7),

       (9, 'Молоко', 2),
       (9, 'Самогонка', 5),
       (9, 'Квас', 2),
       (9, 'Медовуха', 7),
       (9, 'Вода', 1);

select name,
       c1 as "Количество под-меню",
       c2 as "Количество блюд"
from test_menu tm

         left join (select menu_id,
                           count(*) as c1
                    from test_sub_menu
                    group by menu_id
                    order by menu_id) tsm on tsm.menu_id = tm.id

         left join (select menu_id,
                           count(*) as c2
                    from test_dishes
                             join test_sub_menu tsm
                                  on tsm.id = test_dishes.sub_menu_id
                    group by menu_id) tsd on tsd.menu_id = tm.id;