db/migrations_backup/0057_fancy_serpent_society.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

DROP VIEW "public"."document_stages_view";--> statement-breakpoint
CREATE VIEW "public"."document_stages_view" AS (
  SELECT
    d.id AS document_id,
    d.doc_number,
    d.title,
    d.status,
    d.issued_date,
    d.contract_id,

    (
      SELECT COUNT(*)
      FROM issue_stages
      WHERE document_id = d.id
    ) AS stage_count,

    COALESCE( 
      (
        SELECT json_agg(i.stage_name)
        FROM issue_stages i
        WHERE i.document_id = d.id
      ), 
      '[]'
    ) AS stage_list,

    d.created_at,
    d.updated_at
  FROM documents d
);