1
2
3
4
5
6
7
8
9
10
11
|
CREATE VIEW "public"."legal_works_detail_view" AS (select "legal_works"."id", "legal_works"."category", "legal_works"."status", "legal_works"."company_id", "legal_works"."vendor_code", "legal_works"."vendor_name", "legal_works"."is_urgent", "legal_works"."request_date", "legal_works"."consultation_date", "legal_works"."expected_answer_date", "legal_works"."legal_completion_date", "legal_works"."reviewer", "legal_works"."legal_responder", "legal_works"."has_attachment", "legal_works"."created_at", "legal_works"."updated_at", "legal_work_requests"."review_department", "legal_work_requests"."inquiry_type", "legal_work_requests"."title", "legal_work_requests"."request_content", "legal_work_requests"."is_public", "legal_work_requests"."contract_project_name", "legal_work_requests"."contract_type", "legal_work_requests"."contract_amount", (
SELECT response_content
FROM legal_work_responses lwr_latest
WHERE lwr_latest.legal_work_id = "legal_works"."id"
ORDER BY lwr_latest.created_at DESC
LIMIT 1
) as "response_content", (
SELECT COUNT(*)::integer
FROM legal_work_attachments lwa
WHERE lwa.legal_work_id = "legal_works"."id"
) as "attachment_count" from "legal_works" left join "legal_work_requests" on "legal_works"."id" = "legal_work_requests"."legal_work_id" left join "vendors" on "legal_works"."company_id" = "vendors"."id");
|
