1
2
3
4
5
6
7
8
9
10
|
DROP VIEW "public"."esg_evaluations_view";--> statement-breakpoint
CREATE VIEW "public"."esg_evaluations_view" AS (select "esg_evaluations"."id", "esg_evaluations"."serial_number", "esg_evaluations"."category", "esg_evaluations"."inspection_item", "esg_evaluations"."is_active", "esg_evaluations"."created_at", "esg_evaluations"."updated_at", count(distinct "esg_evaluation_items"."id") as "total_evaluation_items", count("esg_answer_options"."id") as "total_answer_options", coalesce(sum("esg_answer_options"."score"), 0) as "max_possible_score",
(
SELECT array_agg(evaluation_item order by order_index)
FROM esg_evaluation_items
WHERE esg_evaluation_id = "esg_evaluations"."id"
AND is_active = true
AND evaluation_item is not null
)
as "evaluation_items_list" from "esg_evaluations" left join "esg_evaluation_items" on "esg_evaluations"."id" = "esg_evaluation_items"."esg_evaluation_id" AND "esg_evaluation_items"."is_active" = true left join "esg_answer_options" on "esg_evaluation_items"."id" = "esg_answer_options"."esg_evaluation_item_id" AND "esg_answer_options"."is_active" = true group by "esg_evaluations"."id", "esg_evaluations"."serial_number", "esg_evaluations"."category", "esg_evaluations"."inspection_item", "esg_evaluations"."is_active", "esg_evaluations"."created_at", "esg_evaluations"."updated_at");
|
