dhis2-devs team mailing list archive

[Lars Helge Øverland <lars@xxxxxxxxx>]

Hi Lorill,

good question - unfortunately, at the moment no.

This feature would clearly be useful though.

I have been thinking for a while that we should create a new API version of
the SQL view criteria/filtering that matches the syntax we have for the Web
API object filtering:

http://dhis2.github.io/dhis2-docs/master/en/developer/html/ch01s08.html

That way you could make filters e.g. like this:

/api/sqlViews/UuRVaRmfHwa/data.csv?filter=foo:eq:A2Kgu7zMgJr&bar:in:[
azikLil2Nl1,bzikLil2Nl1]


Perhaps we could reuse some of the components we have for the Web API
filter here.

*Morten* - what do you think?


regards,

Lars







On Wed, May 4, 2016 at 12:57 AM, Lorill Crees <lcrees@xxxxxxxxxx> wrote:

> Hi,
>
> We're attempting to pull data from DHIS 2 via DHIS 2 SQL Views / Queries
> and want to be able to use "IN" clauses to filter the data. Is there a way
> to do this?
>
> For example we want to create a SQL View or query to get tracked entity
> data values, but only for specific data elements. The number of data
> elements that will be used in a filter will vary.
>
> The documentation here doesn't mention anything:
> http://dhis2.github.io/dhis2-docs/master/en/developer/html/ch01s30.html
>
> Attempting to create a query with a variable like this doesn't seem to
> work (it seems to not allow quoting and commas):
>
> select * from foo where bar in (${bars});
>
> Alternately, is it possible to add criteria to a sql view where the
> criteria are appended as "OR"s?
>
> view: select * from foo;
>
>
> /api/sqlViews/UuRVaRmfHwa/data.csv?criteria=bar:azikLil2Nl1&criteria=bar:A2Kgu7zMgJr
> (select * from foo where bar= azikLil2Nl1 OR bar = A2Kgu7zMgJr;)
>
> This one seems more complicated because we would want a mixture of "AND"s
> and "OR"s for our particular view.
>
> Please let us know thoughts on how we can get this to work for our use
> case.
>
> Thanks!
>
> Lorill
>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>
>


-- 
Lars Helge Øverland
Lead developer, DHIS 2
University of Oslo
Skype: larshelgeoverland
lars@xxxxxxxxx
http://www.dhis2.org <https://www.dhis2.org/>