dhis2-devs team mailing list archive

[Morten Olav Hansen <morten@xxxxxxxxx>]

@Lars: possibly, at least the parsing logic.. that said, most of the object
filter stuff does automatic translation of properties etc.. so it is only
allowed to work on exposed properties, which I think is not good enough for
SQLViews..

-- 
Morten Olav Hansen
Senior Engineer, DHIS 2
University of Oslo
http://www.dhis2.org

On Wed, May 4, 2016 at 8:30 PM, Lars Helge Øverland <lars@xxxxxxxxx> wrote:

> 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/>
>
>