fd.sql

CREATE OR REPLACE VIEW form_details AS 
 SELECT fd.id, fd.template_version_id, fd.agency_id, fd.user_id, fd.name, 
    fd.template, fd.deleted, fd.finalised, fd.archived, fd.private, fd.generate, 
    fd.created, fd.updated, fd.archive_name, fd.parent_id, fd.signed, 
    fd.template_cost, fd.template_instruction_pages, fd.template_id, 
    fd.template_name, fd.template_orientation, 
        CASE
            WHEN array_upper(fd.avids, 1) = 1 AND fd.avids[1] IS NULL THEN ARRAY[]::integer[]
            ELSE fd.avids
        END AS template_version_ids
   FROM ( SELECT forms.id, forms.template_version_id, forms.agency_id, 
            forms.user_id, forms.name, forms.template, forms.deleted, 
            forms.finalised, forms.archived, forms.private, forms.generate, 
            forms.created, forms.updated, forms.archive_name, forms.parent_id, 
            forms.signed, template_versions.cost AS template_cost, 
            template_versions.instruction_pages AS template_instruction_pages, 
            templates.id AS template_id, templates.name AS template_name, 
            templates.orientation AS template_orientation, 
            array_agg(DISTINCT form_annexure_versions.template_version_id) AS avids
           FROM forms
      LEFT JOIN template_versions ON template_versions.id = forms.template_version_id
   LEFT JOIN templates ON templates.id = template_versions.template_id
   LEFT JOIN form_annexures form_annexure_versions ON forms.id = form_annexure_versions.form_id
  WHERE forms.deleted = false
  GROUP BY forms.id, template_versions.cost, template_versions.instruction_pages, templates.id) fd;