From 907f8e563b648b426f93ee162f9f703013f9ad50 Mon Sep 17 00:00:00 2001
From: Mitsuo Tokumori <rtokumori@pucp.edu.pe>
Date: Sun, 12 Nov 2023 17:44:28 -0500
Subject: QOF change: use SQL view for student data display

---
 ustayml/schema.sql | 16 +++++++++++++++-
 1 file changed, 15 insertions(+), 1 deletion(-)

(limited to 'ustayml/schema.sql')

diff --git a/ustayml/schema.sql b/ustayml/schema.sql
index e7e3e68..a160605 100644
--- a/ustayml/schema.sql
+++ b/ustayml/schema.sql
@@ -75,7 +75,21 @@ CREATE TABLE student (
 -- REFERENCES administrative_division (id)
 -- ;
 
--- Sample data  (later move to tests)
+-- VIEWS
+
+create view view_student as
+select stu.*, adm.subdivision3 as "district", puc.name as "pucp_unit",
+	des.name as "est_desertion_risk_class"
+from student stu 
+	left join administrative_division adm 
+		on stu.administrative_division_id = adm.id
+	left join pucp_unit as puc
+		on stu.pucp_unit_id = puc.id
+	left join desertion_risk_class as des
+		on stu.est_desertion_risk_class_id = des.id
+;
+
+-- SAMPLE DATA  (LATER MOVE TO TESTS)
 
 INSERT INTO user_role ("id", "name") VALUES
 	 (1, 'admin'),
-- 
cgit v1.2.3

