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
30
31
32
33
34
|
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,
(
SELECT json_agg(
json_build_object(
'id', i.id,
'stageName', i.stage_name,
'planDate', i.plan_date,
'actualDate', i.actual_date,
'createdAt', i.created_at,
'updatedAt', i.updated_at
)
)
FROM issue_stages i
WHERE i.document_id = d.id
) AS stage_list,
d.created_at,
d.updated_at
FROM documents d
);
|
