dhis2-devs team mailing list archive

[noreply@xxxxxxxxxxxxx]

------------------------------------------------------------
revno: 18972
committer: Lars Helge Overland <larshelge@xxxxxxxxx>
branch nick: dhis2
timestamp: Tue 2015-04-21 21:26:43 +0200
message:
  Event query, deriving main result set from three joined subqueries on event, datavalue, comment, in order to allow for proper paging on the event subquery.
modified:
  dhis-2/dhis-services/dhis-service-dxf2/src/main/java/org/hisp/dhis/dxf2/events/event/JdbcEventStore.java


--
lp:dhis2
https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk

Your team DHIS 2 developers is subscribed to branch lp:dhis2.
To unsubscribe from this branch go to https://code.launchpad.net/~dhis2-devs-core/dhis2/trunk/+edit-subscription

=== modified file 'dhis-2/dhis-services/dhis-service-dxf2/src/main/java/org/hisp/dhis/dxf2/events/event/JdbcEventStore.java'
--- dhis-2/dhis-services/dhis-service-dxf2/src/main/java/org/hisp/dhis/dxf2/events/event/JdbcEventStore.java	2015-04-21 19:00:43 +0000
+++ dhis-2/dhis-services/dhis-service-dxf2/src/main/java/org/hisp/dhis/dxf2/events/event/JdbcEventStore.java	2015-04-21 19:26:43 +0000
@@ -185,22 +185,42 @@
         return events;
     }
 
+    /**
+     * Query is based on three sub queries on event, data value and comment, which 
+     * are joined using program stage instance id. The purpose of the separate
+     * queries is to be able to page properly on events.
+     */
     private String buildSql( EventSearchParams params, List<OrganisationUnit> organisationUnits )
     {
+        String sql = "select * from (";
+        
+        sql += getEventQuery( params, organisationUnits );
+        
+        sql += ") as event left join (";
+        
+        sql += getDataValueQuery();
+        
+        sql += ") as dv on event.psi_id=dv.pdv_id left join (";
+        
+        sql += getCommentQuery();
+        
+        sql += ") as cm on event.psi_id=cm.psic_id";
+        
+        return sql;
+    }
+    
+    private String getEventQuery( EventSearchParams params, List<OrganisationUnit> organisationUnits )
+    {
         List<Integer> orgUnitIds = getIdList( organisationUnits );
 
         SqlHelper hlp = new SqlHelper();
 
         String sql =
-            "select psi.uid as psi_uid, psi.status as psi_status, psi.executiondate as psi_executiondate, psi.duedate as psi_duedate, psi.completeduser as psi_completeduser, " +
+            "select psi.programstageinstanceid as psi_id, psi.uid as psi_uid, psi.status as psi_status, psi.executiondate as psi_executiondate, psi.duedate as psi_duedate, psi.completeduser as psi_completeduser, " +
             "psi.longitude as psi_longitude, psi.latitude as psi_latitude, psi.created as psi_created, psi.lastupdated as psi_lastupdated, " +
             "pi.uid as pi_uid, pi.status as pi_status, pi.followup as pi_followup, p.uid as p_uid, p.code as p_code, " +
             "p.type as p_type, ps.uid as ps_uid, ps.code as ps_code, ps.capturecoordinates as ps_capturecoordinates, " +
-            "ou.uid as ou_uid, ou.code as ou_code, ou.name as ou_name, tei.uid as tei_uid, " +
-            "psinote.trackedentitycommentid as psinote_id, psinote.commenttext as psinote_value, " +
-            "psinote.createddate as psinote_storeddate, psinote.creator as psinote_storedby, " +
-            "pdv.value as pdv_value, pdv.storedby as pdv_storedby, pdv.providedelsewhere as pdv_providedelsewhere, " +
-            "de.uid as de_uid, de.code as de_code " +
+            "ou.uid as ou_uid, ou.code as ou_code, ou.name as ou_name, tei.uid as tei_uid " +
             "from programstageinstance psi " +
             "inner join programinstance pi on pi.programinstanceid=psi.programinstanceid " +
             "inner join program p on p.programid=pi.programid " +
@@ -215,13 +235,7 @@
         {
             sql += "left join organisationunit ou on (tei.organisationunitid=ou.organisationunitid) ";
         }
-        
-        sql +=
-            "left join programstageinstancecomments psic on psi.programstageinstanceid=psic.programstageinstanceid " +
-            "left join trackedentitycomment psinote on psic.trackedentitycommentid=psinote.trackedentitycommentid " +
-            "left join trackedentitydatavalue pdv on psi.programstageinstanceid=pdv.programstageinstanceid " +
-            "left join dataelement de on pdv.dataelementid=de.dataelementid ";
-        
+                
         if ( params.getTrackedEntityInstance() != null )
         {
             sql += hlp.whereAnd() + " tei.trackedentityinstanceid=" + params.getTrackedEntityInstance().getId() + " ";
@@ -323,4 +337,26 @@
 
         return sql;
     }
+
+    private String getDataValueQuery()
+    {
+        String sql =
+            "select pdv.programstageinstanceid as pdv_id, pdv.value as pdv_value, pdv.storedby as pdv_storedby, pdv.providedelsewhere as pdv_providedelsewhere, " +
+            "de.uid as de_uid, de.code as de_code " +
+            "from trackedentitydatavalue pdv " +
+            "inner join dataelement de on pdv.dataelementid=de.dataelementid ";
+        
+        return sql;
+    }
+    
+    private String getCommentQuery()
+    {
+        String sql =
+            "select psic.programstageinstanceid as psic_id, psinote.trackedentitycommentid as psinote_id, psinote.commenttext as psinote_value, " +
+            "psinote.createddate as psinote_storeddate, psinote.creator as psinote_storedby " +
+            "from programstageinstancecomments psic " +
+            "inner join trackedentitycomment psinote on psic.trackedentitycommentid=psinote.trackedentitycommentid ";
+        
+        return sql;
+    }
 }