Database info for SQL queries
MATRIX
1.1. Matrix table
All the submissions made in a matrix question are stored in a different table.
To find this table, you need the workflow id and the tag of the matrix question.
The table is called w_<workflow_id>_<matrix_tag>
For example, let’s take this workflow:
In this workflow, we can find this matrix attached to the list Products (list id : jxbkia8qlhu3)
The matrix table is w_akrb7ybm39pmge8k_sku
In this matrix table, you will find the following fields:
_id : row id
_submission_id: the corresponding id in the workflow table
_object_id: the corresponding id in the list
1.2. Workflow - Matrix
If you need to do a left join between the workflow table and the matrix table, you need to map the workflow id to the matrix submission id:
w_<workflow_id>_<matrix_tag>._submission_id = w_<workflow_id>._id
In our example, the mapping would be: w_akrb7ybm39pmge8k_sku._submission_id=w_akrb7ybm39pmge8k._id
1.2. Matrix - List
If you need to do a left join between the list attached to the matrix and the matrix table, you need to map the list id to the matrix object id:
l_<list_id>._id = w_<workflow_id>_<matrix_tag>._object_idIn our example, the mapping would be: l_jxbkia8qlhu3._id= w_akrb7ybm39pmge8k_sku._object_id
LISTS
1.1. Scope : teams
If a list is in scope teams or single team, it means only the teams that are the owners of the items can see this item.
If you need to do a left join between the list and the team, you need to use the mapping table items_teams.
In this table you will find:
id : mapping id
item_id: corresponding id in the list
team_id: corresponding team id
If you have the list id : jxbkia8qlhu3,
You will map this list to the teams using two left join:
FROM l_jxbkia8qlhu3
LEFT JOIN items_teams ON items_teams.item_id=l_jxbkia8qlhu3._id
LEFT JOIN teams ON teams.id = items_teams.team_id
1.2. Scope: mobile users
If a list is in scope mobile user or single mobile user, it means only the mobile users who are the owners of the items can see this item.
If you need to do a left join between the list and the mobile users, you need to use the mapping table items_mobile_users.
In this table you will find:
id : mapping id
item_id: corresponding id in the list
mobile_user_id: corresponding mobile user id
If you have the list id : jxbkia8qlhu3,
You will map this list to the mobile users using two left join:
FROM l_jxbkia8qlhu3
LEFT JOIN items_teams ON items_teams.item_id=l_jxbkia8qlhu3._id
LEFT JOIN mobile_users ON mobile_users.id = items_teams.mobile_user_id
1.2. Creation details
_created_at: creation timestamp (local time)
_created_by: phone number if the item has been created by a mobile user, email if the item has been created by a web user
_created_by_id: mobile user id if the item has been created by a mobile user, web user id if the item has been created by a web user