dhis2-devs team mailing list archive

Thread
Date

Re: Events API response inconsistency issue when paginated

To: DHIS 2 Developers list <dhis2-devs@xxxxxxxxxxxxxxxxxxx>
From: jhansirk <jhansirk@xxxxxxxxxxxxxxxx>
Date: Mon, 16 Oct 2017 11:52:39 +0530
Cc: Thomas RODRIGUEZ <thomas.rodriguez@xxxxxxxxxxxxx>, twmsf <twmsf@xxxxxxxxxxxxxxxx>, Megan McGuire <megan.mcguire@xxxxxxxxxxxxxxx>, Marta Vila <Marta.Vila@xxxxxxxxxxxx>
In-reply-to: <CAP4+UEB=JyWE97MGTXKPUV_kL6Be2vG9YVpqz1N9FqwyTseXuw@mail.gmail.com>

Hi DHIS Team,

Did you get a chance to look at this pull request <https://github.com/dhis2/dhis2-core/pull/1186>. Let us know if there is any issue.

Thanks,
Jhansi
> On 06-Oct-2017, at 12:38 PM, Mani Madhoolika Bulusu <mbulusu@xxxxxxxxxxxxxxxx> wrote:
> 
> Hello DHIS Team,
> 
> We are experiencing few issues with the events API when paginated in DHIS 2.26:
> 
> Some events are redundantly returned in multiple pages, inspite of applying order. Because of this we are seeing few events are missing after downloading all the pages which is not the case without pagination. 
> Events are ordered only within the page, instead ordering should be applied on all the pages and the results should be page by page.
> Analysing it further more we found the root cause of the issue to be with the SQL query used for events API which is missing an ORDER BY clause to an inner query.
> 
> As most of us know, when using Limit, it is important to also use an ORDER BY clause that constraints the result rows into a unique order. In the Events query, LIMIT and ORDER BY are used when paginated. Because of applying the LIMIT on a inner query and ORDER BY on the entire outer query, the inner part of the query is working as if there is no order and is giving inconsistent results.
> 
> To fix this, we have also added an ORDER BY clause to the inner query where LIMIT is applied. We have sent a pull request <https://github.com/dhis2/dhis2-core/pull/1186> with this fix. Please let us know if there is any issue.
> 
> Thanks,
> Jhansi/Madhoolika