SELECT
pages.id AS "ID",
pages.path AS "Endereço",
pages.title AS "Título",
pages."createdAt"::timestamp without time zone AS "Data de criação",
pages."updatedAt"::timestamp without time zone AS "Última edição",
CASE
WHEN pages."isPublished"::boolean = false
THEN 'Em fila de publicação'
ELSE 'Publicado'
END AS "Status",
CASE
WHEN "Tags - TagId".tag::text ~~ 'unidade%'::text
THEN "Tags - TagId".title
WHEN "Tags - TagId".tag::text !~~ 'unidade%'::text
THEN
(
SELECT
t.title
FROM
pages pg JOIN
"pageTags" pt ON pg.id = pt."pageId" JOIN
tags t ON pt."tagId" = t.id
WHERE
t.tag::text ~~ 'unidade%'::text AND
pg.id = pages.id
)
ELSE
NULL::character varying
END AS "Coordenação",
CASE
WHEN "Tags - TagId".tag::text ~~ 'documento%'::text
THEN "Tags - TagId".title
WHEN "Tags - TagId".tag::text !~~ 'documento%'::text
THEN
(
SELECT
t.title
FROM
pages pg JOIN
"pageTags" pt ON pg.id = pt."pageId" JOIN
tags t ON pt."tagId" = t.id
WHERE
t.tag::text ~~ 'documento%'::text AND
pg.id = pages.id
)
ELSE
NULL::character varying
END AS "Tipo de documento",
CASE
WHEN "Tags - TagId".tag::text ~~ 'objeto%'::text
THEN "Tags - TagId".title
WHEN "Tags - TagId".tag::text !~~ 'objeto%'::text
THEN
(
SELECT
t.title
FROM
pages pg JOIN
"pageTags" pt ON pg.id = pt."pageId" JOIN
tags t ON pt."tagId" = t.id
WHERE
t.tag::text ~~ 'objeto%'::text AND
pg.id = pages.id
)
ELSE
NULL::character varying
END AS "Objeto do documento"
FROM
pages JOIN "pageTags" "PageTags" ON pages.id = "PageTags"."pageId" JOIN
tags "Tags - TagId" ON "PageTags"."tagId" = "Tags - TagId".id
ORDER BY
pages."createdAt" ASC