QOF change: use SQL view for student data display

1 files changed, 15 insertions, 1 deletions

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'),