Client side has more data compared to data extracted from server

Support - Assistance technique
1

Hi team, I have capture app program but i noticed that when i extract the data from the server, it has less data compared to the data on the front end on the data entry form?

2

Can you elaborate a bit what you mean by “less data” ?
Some data elements are not visible/filled ? some tracked entity are not server side ?

1 Like
3

Hi Stephan,
When i run the event report for my capture app program, am getting more data compared to the data am extracting from the postgresql database

4

Can you provide the sql you used if not the tables/views used in the query ?
Are they from the analytics tables or the programstageinstance ?

5

Here is my sql:
drop table if exists data_view;

create temp table data_view as
select r.name province,d.name district,u.name facility,
max(case when v.dataelementid = 141536 then replace(cast(de.value::json → ‘value’ as varchar),‘"’,‘’) end) diag_typ1,
max(case when v.dataelementid = 141535 then replace(cast(de.value::json → ‘value’ as varchar),‘"’,‘’) end) lab_test1,
max(case when v.dataelementid = 141537 then replace(cast(de.value::json → ‘value’ as varchar),‘"’,‘’) end) temp1,
max(case when v.dataelementid = 141544 then replace(cast(de.value::json → ‘value’ as varchar),‘"’,‘’) end) temp2,

i.created date_entered
from programstageinstance i
jOIN json_each_text(i.eventdatavalues::json) de on true
join dataelement v on v.uid = de.key
join organisationunit u on i.organisationunitid = u.organisationunitid
join organisationunit d ON d.organisationunitid = u.parentid
join organisationunit r ON r.organisationunitid = d.parentid
where programinstanceid = 144 and u.name in (‘clinic_1’,‘clinic_2’,)
group by i.programinstanceid,i.created,r.name,d.name,u.name;

select * from data_view

6

your query doesn’t seem to do exactly the same thing as the event report.
the group by is probably why you don’t get the same numbers.

1 record of programstageinstance = 1 event
but your group by I’m not sut it preserve all the events (I would group by programstageinstance.uid which is the event uid) .

if the json_each_text and group by was to extract only the 4 data elements
you can probably rewrite your query with the “json navigation” notation
in my example query cP5B2xL8SI6 is a data element uid

select program.name, 
programstageinstance.uid as event_uid, 
programstageinstance.eventdatavalues->'cP5B2xL8SI6'->>'value' as de_cP5B2xL8SI6,
programstageinstance.eventdatavalues->'UCE7agQJYCh'->>'value' as de_UCE7agQJYCh 
from programstageinstance 
join programinstance ON programinstance.programinstanceid = programstageinstance.programinstanceid 
join program on program.programid = programinstance.programid 
where program.uid = 'yJz5vXky1dA'

the query will output the event with the 2 values (null if not present in the event)

+-------+-------------+----------------+----------------+
| name  | event_uid   | de_cp5b2xl8si6 | de_uce7agqjych |
|-------+-------------+----------------+----------------|
| Test  | aIQYdVkCivU | <null>         | <null>         |
| Test  | v6ziRHremmS | <null>         | <null>         |
| Test  | zE6Xc3vpYAL | true           | false          |
| Test  | ofmhVjBZcMq | true           | false          |
| Test  | pVmg7tnY3qT | <null>         | <null>         |
| Test  | SpWMZ0spflx | <null>         | <null>         |

Note that

  • You’ll need to add the joins for orgunits (there’s a _orgunitstructure table that might simplify a bit your query, but it relies a bit on analytics/resource tables)
  • ideally you need to handle the soft deleted flag on all the tables, and probably need to join the trackedentityinstance (I’m not sure the deleted flag are propagated to the programstageinstance)
  • I would try to avoid queries with "id"s and use only uids. In a future version of dhis a db migration might impact your query, having to lookup all the ids again won’t be fun. dhis will try to keep the uid intact in a migration.
7

Thank you. This really helped!

1 Like
8

Note to avoid direct access to the db you might be interested by the SqlViews api