Modify query of ODK X Services

Hi All

I’m working in a project with a large dataset (more than 250K records ion one table) for the post i see is not possible to only get the records in a specific group, in the project we have more that 250 groups already set.

I like your help to see if is possible to modify ODK X services to add a where clause to the query that get the rows from the tables.

Or if anybody have a alternative solution, thanks in advance.

Hi @jgomezr!

With permission filters you can ensure folks only access some records: Data Permission Filters — ODK-X Docs
but the syncing does occur against the entire database.

In the odkData object there is an arbitrary query function that allows you to write more complex queries using a select statement.

WARNING: arbitrary query does not enforce the data permission filter (because of figuring out the complexity of the query would require new functionality to modify a random arbitrary query that already has a where clause with the additional where clauses needed for data permission filters).

https://docs.odk-x.org/injected-interfaces/#odkdata-js

Is possible to have like a local table, a table that don´t sync with the server?

Yes, this is supported. In odkData.js there are a few methods with LocalOnlyTable in the name, these methods interact with SQL tables that exist only on the device.

@linl33 this means in the properties tab of my form i can mark it as localonly?

No. You have to create these tables using odkData.js.

@linl33 and this tables can be used as Linked Tables to use their data in other tables?

Not using the built-in linked_table prompt type. But you can create a custom prompt type that queries your local tables. The async_assign prompt types would be a great starting point to build on for your custom prompt type.

https://docs.odk-x.org/xlsx-converter-using/#customizing-prompts

2 Likes

If anyone need more details on how to implement it i find a example in the ODK X github repository made by Clarlars, i hope this can help the implementations that manage huge amounts of data. :grinning:.

Thanks to @linl33 and all that replay this post.

2 Likes

I have an update on this topic, a couple of months ago working around this problem I found another solution to this problem, this time was to modify the ODK X Services app.

I took the source code from the official GitHub repository and made some modifications in a couple of methods located in “services_app \ src \ main \ java \ org \ opendatakit \ services \ sync \ service \ logic” to only synchronize the records assigned to a specific group, For those who want to delve into or experiment in the aforementioned folder, they will be able to find the ProcessRowDataPullServerUpdates.java class, there are the methods that are in charge of making the query and bringing the records to the device.

Any question or if somebody need help can contact me

3 Likes

@jgomezr this sounds like a useful feature. Since ODK-X is an open source project we accept (and depend on) code from community members. Would you mind making a pull request to share your code?

Your contribution might need some tweaking to handle a corner case, but by providing a PR we can branch the feature and start investigating additional functionality that needs to be added with goal of it becoming part of the next official release.

2 Likes

@W_Brunette I need to refactor a couple of things but more that happy to contribute to the community

2 Likes