Add ability to filter OData subtables

[matthew-white]

Filing an issue in relation to this forum post by @mathieubossaert: https://forum.getodk.org/t/propagate-submission-date-to-child-tables/34349

Right now it's possible to filter the primary OData table using the $filter query parameter. However, it's not possible to filter subtables:

central-backend/lib/util/db.js

Lines 235 to 245 in f16636d

	static fromODataRequest(params, query) {
	const result = { extended: true };
	if ((params.table === 'Submissions') && (query.$skip != null))
	result.offset = parseInt(query.$skip, 10);
	if ((params.table === 'Submissions') && (query.$top != null))
	result.limit = parseInt(query.$top, 10);
	if ((params.table === 'Submissions') && (query.$filter != null))
	result.filter = query.$filter;
	return new QueryOptions(result);
	}

What's the best way in OData to enable subtable filtering? In terms of OData syntax, is a subtable allowed to reference fields in the primary table?

[matthew-white]

Linking to a new forum topic about filtering subtables: https://forum.getodk.org/t/using-filter-with-repeat-tables-to-download-odata/36245

[lognaturel]

I just got an ask for filtering a repeat table by __system/reviewState.

[sadiqkhoja]

I did some research on how to implement this, and we have few options here:

1) Addressing subset of a collection

OData spec

We can provide ability to filter parent table and then navigate to the children like:

/v1/projects/1/forms/withrepeat.svc/Submissions/$filter(@criteria)/children?$@criteria = __system/submissionDate gt 2020-02-01

I wasn't able to test this approach on the reference service provided by odata.org

2) Add __system to each child table

Right now we add only __submission-id in the child table, we can add all the system properties in the OData response of repeat table, then users would make request like:

/v1/projects/1/forms/withrepeat.svc/Submissions.children?$filter = __system/submissionDate gt 2020-02-01

3) Use $root

OData spec

We can implement $root keyword for the filters and don't add system properties in the response of the child table

/v1/projects/1/forms/withrepeat.svc/Submissions.children?$filter = $root/Submissions/__system/submissionDate gt 2020-02-01

---

However, if the objective here is just to build ETL i.e. periodically pull the delta from the Central and push it to analytical system then we already have a sleek way to do that including filtering subtables: combine $expand and $filter

Example: /v1/projects/1/forms/withrepeat.svc/Submissions?$expand=*&$filter=__system/submissionDate gt 2020-02-01'

The response will be filtered by submissionData, and you will have all subtable expanded

[matthew-white]

if the objective here is just to build ETL … then we already have a sleek way to do that including filtering subtables: combine $expand and $filter

I agree that that combination is super helpful and probably meets many users' needs. In the second forum topic above, that option was mentioned, but Odil said that they want to download each table separately for some reason. My hunch is that there's a valid use case in this area. @lognaturel, for the case that you mentioned, do you think $expand + $filter would be enough?

In terms of the three options above, option 3 with $root seems very elegant to me!

[lognaturel]

do you think $expand + $filter would be enough

I think being able to filter each table independently would be preferable. Sometimes each table goes through a different process or some tables are completely unnecessary for some part of analysis. In those cases, it can be really annoying to have to deal with all data, especially if there are large numbers of submissions. For a concrete example, sometimes the form design pulls values from the top level into repeats so that the top level is unnecessary. In that case it’s a lot more convenient to only pull the repeat and not have to deal with the top level at all.

option 3 with $root seems very elegant to me!

I agree!