How I can join ODK aggregate core tables

Hello, everyone I just need to know how different ODK aggreate core tables can be join with each other.
My understanding is they can be join by _URI of core tables with _PARENT_AURI for coreN tables.
like the following.

SELECT * FROM databaseName.TABLE_CORE as cr
inner join databaseName.TABLE_CORE2 as cr2
on cr2._PARENT_AURI=cr._URI

Can anyone please confirm this?
Also what is the usage of _TOP_LEVEL_AURI in coreN table?

Advance thanks for your help and explanation regarding this.

You might find this useful (https://github.com/opendatakit/opendatakit/wiki/Aggregate-Database-Structure), Although it might not answer your question completely, still it will give you enough info about the db structure.

Regards,
Saad

2 Likes

I have been working on a customized dashboard on top of Aggregate, so I can tell you that the original queries differ, based on what you want to do. For example, I need to pull the database name of every form, which I get like this:

SELECT PERSIST_AS_TABLE_NAME from odk_prod._form_data_model where ELEMENT_NAME = 'meta' and URI_SUBMISSION_DATA_MODEL = (SELECT URI_SUBMISSION_DATA_MODEL from odk_prod._form_info_submission_association where URI_MD5_FORM_ID = (SELECT _TOP_LEVEL_AURI FROM odk_prod._form_info_fileset where FORM_NAME = '".$row['FORM_NAME']."'))

I hope this helps.

1 Like