Добавить рекурсивную проверку к SQL запросу на сущестование предков

Question

Надо посчитать общий размер всех актуальных версии документов.

В таблице personal_document хранятся версии документов, документ у которого самая свежая modification date и available = true считается последней актуальной версией.

Надо добавить проверку, что у всех его предков-папок (лежат в таблице personal_category) available = true, если хоть у одной папке в цепочке предков available = false, значит эта папка была удалена и размер этого документа нам не нужен (даже если у него самого стоит available = true).

Запрос на подсчет размера всех актуальных версий документа без проверки предков. Не могу сообразить как сюда вкорячить рекурсивную проверку предков-папок.

SELECT SUM(active_size) AS total_active_size FROM (
                SELECT SUM(pd.size) AS active_size FROM personal_document pd 
                JOIN (
                SELECT uid, MAX(modification_date) AS max_date FROM personal_document
                WHERE creator_uid = '0c27e6ef-b615-4328-91d6-a94d1f5d799a'
                AND available IS TRUE
                GROUP BY uid) max_dates ON (pd.uid = max_dates.uid AND pd.modification_date = max_date)
                GROUP BY pd.uid) as total

Запрос должен вернуть 0, но сейчас возвращает 24 (размер актуальной версии документа, без проверки что какой либо из его родителей удален).

CREATE TABLE public.personal_document
(
    uid uuid NOT NULL,
    creation_date timestamp without time zone NOT NULL,
    modification_date timestamp without time zone NOT NULL DEFAULT timezone('Asia/Yekaterinburg'::text, CURRENT_TIMESTAMP),
    tenant_uid uuid NOT NULL,
    creator_uid uuid NOT NULL,
    description character varying(1024) COLLATE pg_catalog."default",
    ext character varying(64) COLLATE pg_catalog."default" NOT NULL,
    name character varying(191) COLLATE pg_catalog."default" NOT NULL,
    size bigint NOT NULL,
    storage_link character varying(2048) COLLATE pg_catalog."default" NOT NULL,
    parent_uid uuid,
    tags uuid,
    available boolean NOT NULL DEFAULT true,
    CONSTRAINT personal_document_parent_uid_fkey FOREIGN KEY (parent_uid)
        REFERENCES public.personal_category (uid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

CREATE TABLE public.personal_category
(
    uid uuid NOT NULL,
    creation_date timestamp without time zone NOT NULL DEFAULT timezone('Asia/Yekaterinburg'::text, CURRENT_TIMESTAMP),
    modification_date timestamp without time zone NOT NULL DEFAULT timezone('Asia/Yekaterinburg'::text, CURRENT_TIMESTAMP),
    tenant_uid uuid NOT NULL,
    creator_uid uuid NOT NULL,
    description character varying(1024) COLLATE pg_catalog."default",
    name character varying(255) COLLATE pg_catalog."default" NOT NULL,
    parent_uid uuid,
    available boolean NOT NULL DEFAULT true,
    CONSTRAINT personal_category_pkey PRIMARY KEY (uid),
    CONSTRAINT personal_category_parent_uid_fkey FOREIGN KEY (parent_uid)
        REFERENCES public.personal_category (uid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

INSERT INTO public.personal_category(
    uid, creation_date, modification_date, tenant_uid, creator_uid, description, name, parent_uid, available)
    VALUES ('fe818920-472d-409d-8b7d-74ae7c1082b5', '2020-02-19 12:00:23.629485', '2020-02-19 12:00:24.60576', 'a9c8662e-f4bc-4e8e-882d-310bf8e86198', '0c27e6ef-b615-4328-91d6-a94d1f5d799a', 'description 1', 'category name 1', null, true)
INSERT INTO public.personal_category(
    uid, creation_date, modification_date, tenant_uid, creator_uid, description, name, parent_uid, available)
    VALUES ('fe818920-472d-409d-8b7d-74ae7c1082b4', '2020-02-19 12:00:23.629485', '2020-02-19 12:00:24.60576', 'a9c8662e-f4bc-4e8e-882d-310bf8e86198', '0c27e6ef-b615-4328-91d6-a94d1f5d799a', 'description 1', 'category name 1', 'fe818920-472d-409d-8b7d-74ae7c1082b5', false);

INSERT INTO public.personal_document(
    uid, creation_date, modification_date, tenant_uid, creator_uid, description, ext, name, size, storage_link, parent_uid, tags, available)
    VALUES ('5f58fe01-1226-45ee-b2b7-b8ebd3eede0c', '2020-02-19 12:00:24.117', '2020-02-19 12:00:24.148247', 'a9c8662e-f4bc-4e8e-882d-310bf8e86198', '0c27e6ef-b615-4328-91d6-a94d1f5d799a', null, 'txt', 'name1', 24, 'hfhghg', 'fe818920-472d-409d-8b7d-74ae7c1082b4', null, true);

    INSERT INTO public.personal_document(
    uid, creation_date, modification_date, tenant_uid, creator_uid, description, ext, name, size, storage_link, parent_uid, tags, available)
    VALUES ('e6da3de1-b7f8-45c4-bd38-66015ec01e24', '2020-02-19 12:00:24.103', '2020-02-19 12:00:24.348853', 'a9c8662e-f4bc-4e8e-882d-310bf8e86198', '0c27e6ef-b615-4328-91d6-a94d1f5d799a', null, 'txt', 'name2', 14, 'jfjdsfb', 'fe818920-472d-409d-8b7d-74ae7c1082b4', null, false);

    INSERT INTO public.personal_document(
    uid, creation_date, modification_date, tenant_uid, creator_uid, description, ext, name, size, storage_link, parent_uid, tags, available)
    VALUES ('5f58fe01-1226-45ee-b2b7-b8ebd3eede0c', '2020-02-19 12:00:24.117', '2020-02-19 12:00:24.507633', 'a9c8662e-f4bc-4e8e-882d-310bf8e86198', '0c27e6ef-b615-4328-91d6-a94d1f5d799a', null, 'txt', 'name3', 24, 'fjghfj', 'fe818920-472d-409d-8b7d-74ae7c1082b4', null, true);

Answer 1

WITH Recursive
DOC as(
    SELECT parent_uid, size
      FROM (
        SELECT *, row_number() over(partition by uid order by modification_date desc) rn
          FROM personal_document
         WHERE creator_uid = '0c27e6ef-b615-4328-91d6-a94d1f5d799a'
           AND available IS TRUE
     ) x 
    WHERE rn=1
),
REC as(
  select uid, available::int available, uid curr
    from personal_category
   where uid in(select uid from DOC)
 UNION ALL
  select REC.uid, C.available::int, C.parent_uid
    from personal_category C, REC where C.uid=REC.curr and REC.available=1
)
select sum(size)
  from DOC
  join (select uid
          from REC
         group by uid having min(available)=1
       ) x
    on DOC.parent_uid=x.uid

Подзапрос DOC получает то же, что ваш изначальный запрос, только немного другим способом (нумерует документы с одним uid в обратном порядке по дате и берет первые записи). Он дает нам uid требуемых категорий и размер самого документа. REC собственно рекурсивный запрос по категориям, который начиная со всех категорий, которые встречались в DOC, собирает статусы их родителей. Итоговый запрос берет минимальные статусы (приведенные к int) по результатам рекурсии. Таким образом он получает только те категории, где у всех родителей были только 1. Остается приклеить к нему изначальную выборку DOC и получить размеры

БЛОГ НА HUSL

Добавить рекурсивную проверку к SQL запросу на сущестование предков

Ответы (1 шт):