REST API does not expose column identifier within reference table #460
Comments
|
Hi @Vinschni. In Grist, references are always to the row id of the target table. They are not as flexible as foreign keys in that respect. |
|
Hi @paulfitz, just gave me hints. I made a minimal example to understand that response and attached it (Untitled document.zip) In short: "Table 1" contains a reference column to table "stones" column "stoneName".
RECEIVED DOC RESPONSE --> Click me{
"docFD": 0,
"clientId": "5d6c66cfb0ad8788",
"doc": {
"_grist_DocInfo": [
"TableData",
"_grist_DocInfo",
[
1
],
{
"docId": [
""
],
"peers": [
""
],
"basketId": [
""
],
"schemaVersion": [
36
],
"timezone": [
"Europe/Berlin"
],
"documentSettings": [
"{\"locale\":\"de-DE\",\"engine\":\"python3\"}"
]
}
],
"_grist_Tables": [
"TableData",
"_grist_Tables",
[
1,
2
],
{
"tableId": [
"Table1",
"Stones"
],
"primaryViewId": [
1,
2
],
"summarySourceTable": [
0,
0
],
"onDemand": [
false,
false
],
"rawViewSectionRef": [
2,
4
]
}
],
"_grist_Tables_column": [
"TableData",
"_grist_Tables_column",
[
1,
2,
3,
4,
5,
6,
7,
8,
9
],
{
"parentId": [
1,
1,
1,
1,
2,
2,
2,
2,
1
],
"parentPos": [
1,
2,
3,
4,
5,
6,
7,
8,
9
],
"colId": [
"manualSort",
"A",
"B",
"C",
"manualSort",
"stoneName",
"B",
"C",
"gristHelper_Display"
],
"type": [
"ManualSortPos",
"Ref:Stones",
"Any",
"Any",
"ManualSortPos",
"Text",
"Any",
"Any",
"Any"
],
"widgetOptions": [
"",
"",
"",
"",
"",
"",
"",
"",
""
],
"isFormula": [
false,
false,
true,
true,
false,
false,
true,
true,
true
],
"formula": [
"",
"",
"",
"",
"",
"",
"",
"",
"$A.stoneName"
],
"label": [
"manualSort",
"A",
"B",
"C",
"manualSort",
"stoneName",
"B",
"C",
"gristHelper_Display"
],
"description": [
"",
"",
"",
"",
"",
"",
"",
"",
""
],
"untieColIdFromLabel": [
false,
false,
false,
false,
false,
false,
false,
false,
false
],
"summarySourceCol": [
0,
0,
0,
0,
0,
0,
0,
0,
0
],
"displayCol": [
0,
9,
0,
0,
0,
0,
0,
0,
0
],
"visibleCol": [
0,
6,
0,
0,
0,
0,
0,
0,
0
],
"rules": [
null,
null,
null,
null,
null,
null,
null,
null,
null
],
"recalcWhen": [
0,
0,
0,
0,
0,
0,
0,
0,
0
],
"recalcDeps": [
null,
null,
null,
null,
null,
null,
null,
null,
null
]
}
],
"_grist_Imports": [
"TableData",
"_grist_Imports",
[],
{
"tableRef": [],
"origFileName": [],
"parseFormula": [],
"delimiter": [],
"doublequote": [],
"escapechar": [],
"quotechar": [],
"skipinitialspace": [],
"encoding": [],
"hasHeaders": []
}
],
"_grist_External_database": [
"TableData",
"_grist_External_database",
[],
{
"host": [],
"port": [],
"username": [],
"dialect": [],
"database": [],
"storage": []
}
],
"_grist_External_table": [
"TableData",
"_grist_External_table",
[],
{
"tableRef": [],
"databaseRef": [],
"tableName": []
}
],
"_grist_TableViews": [
"TableData",
"_grist_TableViews",
[],
{
"tableRef": [],
"viewRef": []
}
],
"_grist_TabItems": [
"TableData",
"_grist_TabItems",
[],
{
"tableRef": [],
"viewRef": []
}
],
"_grist_TabBar": [
"TableData",
"_grist_TabBar",
[
1,
2
],
{
"viewRef": [
1,
2
],
"tabPos": [
1,
2
]
}
],
"_grist_Pages": [
"TableData",
"_grist_Pages",
[
1,
2
],
{
"viewRef": [
1,
2
],
"indentation": [
0,
0
],
"pagePos": [
1,
2
]
}
],
"_grist_Views": [
"TableData",
"_grist_Views",
[
1,
2
],
{
"name": [
"Table1",
"stones"
],
"type": [
"raw_data",
"raw_data"
],
"layoutSpec": [
"",
""
]
}
],
"_grist_Views_section": [
"TableData",
"_grist_Views_section",
[
1,
2,
3,
4
],
{
"tableRef": [
1,
1,
2,
2
],
"parentId": [
1,
0,
2,
0
],
"parentKey": [
"record",
"record",
"record",
"record"
],
"title": [
"",
"",
"",
"stones"
],
"defaultWidth": [
100,
100,
100,
100
],
"borderWidth": [
1,
1,
1,
1
],
"theme": [
"",
"",
"",
""
],
"options": [
"",
"",
"",
""
],
"chartType": [
"",
"",
"",
""
],
"layoutSpec": [
"",
"",
"",
""
],
"filterSpec": [
"",
"",
"",
""
],
"sortColRefs": [
"[]",
"",
"[]",
""
],
"linkSrcSectionRef": [
0,
0,
0,
0
],
"linkSrcColRef": [
0,
0,
0,
0
],
"linkTargetColRef": [
0,
0,
0,
0
],
"embedId": [
"",
"",
"",
""
],
"rules": [
null,
null,
null,
null
]
}
],
"_grist_Views_section_field": [
"TableData",
"_grist_Views_section_field",
[
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12
],
{
"parentId": [
1,
1,
1,
2,
2,
2,
3,
3,
3,
4,
4,
4
],
"parentPos": [
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12
],
"colRef": [
2,
3,
4,
2,
3,
4,
6,
7,
8,
6,
7,
8
],
"width": [
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
],
"widgetOptions": [
"",
"",
"",
"",
"",
"",
"",
"",
"",
"",
"",
""
],
"displayCol": [
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
],
"visibleCol": [
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0
],
"filter": [
"",
"",
"",
"",
"",
"",
"",
"",
"",
"",
"",
""
],
"rules": [
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null
]
}
],
"_grist_Validations": [
"TableData",
"_grist_Validations",
[],
{
"formula": [],
"name": [],
"tableRef": []
}
],
"_grist_REPL_Hist": [
"TableData",
"_grist_REPL_Hist",
[],
{
"code": [],
"outputText": [],
"errorText": []
}
],
"_grist_Attachments": [
"TableData",
"_grist_Attachments",
[],
{
"fileIdent": [],
"fileName": [],
"fileType": [],
"fileSize": [],
"imageHeight": [],
"imageWidth": [],
"timeDeleted": [],
"timeUploaded": []
}
],
"_grist_Triggers": [
"TableData",
"_grist_Triggers",
[],
{
"tableRef": [],
"eventTypes": [],
"isReadyColRef": [],
"actions": []
}
],
"_grist_ACLRules": [
"TableData",
"_grist_ACLRules",
[
1
],
{
"resource": [
1
],
"permissions": [
63
],
"principals": [
"[1]"
],
"aclFormula": [
""
],
"aclColumn": [
0
],
"aclFormulaParsed": [
""
],
"permissionsText": [
""
],
"rulePos": [
null
],
"userAttributes": [
""
],
"memo": [
""
]
}
],
"_grist_ACLResources": [
"TableData",
"_grist_ACLResources",
[
1
],
{
"tableId": [
""
],
"colIds": [
""
]
}
],
"_grist_ACLPrincipals": [
"TableData",
"_grist_ACLPrincipals",
[
1,
2,
3,
4
],
{
"type": [
"group",
"group",
"group",
"group"
],
"userEmail": [
"",
"",
"",
""
],
"userName": [
"",
"",
"",
""
],
"groupName": [
"Owners",
"Admins",
"Editors",
"Viewers"
],
"instanceId": [
"",
"",
"",
""
]
}
],
"_grist_ACLMemberships": [
"TableData",
"_grist_ACLMemberships",
[],
{
"parent": [],
"child": []
}
],
"_grist_Filters": [
"TableData",
"_grist_Filters",
[],
{
"viewSectionRef": [],
"colRef": [],
"filter": [],
"pinned": []
}
],
"_grist_Cells": [
"TableData",
"_grist_Cells",
[],
{
"tableRef": [],
"colRef": [],
"rowId": [],
"root": [],
"parentId": [],
"type": [],
"content": [],
"userRef": []
}
]
},
"log": [
{
"actionNum": 1,
"actionHash": "a9c64aa147c516f0ca3c11da5bfcaf4c392b0e04935a7cfda069fd062409a4bc",
"fromSelf": false,
"linkId": 0,
"otherId": 0,
"rowIdHint": 0,
"isUndo": false
},
{
"actionNum": 2,
"actionHash": "8d7ea60144c1db2f888176269f29471a6a26d637e438bef4214d788b94fce347",
"fromSelf": true,
"linkId": 0,
"otherId": 0,
"rowIdHint": 0,
"isUndo": false
},
{
"actionNum": 3,
"actionHash": "ccb5dbe4b6120dc1fdcae43827ac98390e315d1244181025e5a7caf6c94870bd",
"fromSelf": true,
"linkId": 0,
"otherId": 0,
"rowIdHint": 0,
"isUndo": false
},
{
"actionNum": 4,
"actionHash": "854943c0d48cce6b6bd39436e7918f7e8dbaeaa628fe650fa394ced015e8b986",
"fromSelf": true,
"linkId": 0,
"otherId": 0,
"rowIdHint": 1,
"isUndo": false
},
{
"actionNum": 5,
"actionHash": "e9737cb86fefdd1994fc63f3ef37774d4b2726035c2638199ade8d215642f6bb",
"fromSelf": true,
"linkId": 0,
"otherId": 0,
"rowIdHint": 0,
"isUndo": false
},
{
"actionNum": 6,
"actionHash": "da2c09fcfe25340213841d59672058ac53c737c6483fc69ad74ba022027298c7",
"fromSelf": true,
"linkId": 0,
"otherId": 0,
"rowIdHint": 0,
"isUndo": false
},
{
"actionNum": 7,
"actionHash": "8d377ee2d217e218460875e4956749fab0d90e1011dbac586063279cf49ca27b",
"fromSelf": true,
"linkId": 0,
"otherId": 0,
"rowIdHint": 0,
"isUndo": false
},
{
"actionNum": 8,
"actionHash": "5fe4558ff365a835cb94d6e81cb357aaeece0890b0f53efa868e5659ffddf974",
"fromSelf": true,
"linkId": 0,
"otherId": 0,
"rowIdHint": 1,
"isUndo": false
}
],
"recoveryMode": false,
"docUsage": {
"dataLimitStatus": null,
"rowCount": {
"1": 1,
"2": 1,
"total": 2
},
"dataSizeBytes": 1277,
"attachmentsSizeBytes": 0
}
} |
|
Oh sorry, I misunderstood. I think grist-core/sandbox/grist/schema.py Lines 81 to 86 in 7b38ac2 |
|
Dear @paulfitz, this works. Took more steps than i initially thought.
def fetchAndFollowRefsGristTable(grist,tableName):
"""Fetches and follows references in all columns of a grist table and replaces it with the data of the reference table
RefList reference values are resolved as comma seperated strings
Args:
grist (grist_api.grist_api.GristDocAPI): Grist api pbject
tableName (str): Name of the grist table to fetch
Returns:
dict, dict: Original fetched table, table with reference IDs in reference columns replaced with real data
"""
gristTableOriginal = grist.fetch_table(tableName)
# Transform into list of dict
gristTable = [{task._fields[i]: task[i] for i in range(len(task))} for task in gristTableOriginal]
# Follow references, otherwise we just get an integer for grist columns which are references
# This process is generic and can handle any number and name of reference columns and tables
# Get all columns of the table
gristCols = grist.call(url=f'tables/{tableName}/columns')['columns']
# Find out which are references and remember the reference table
gristRefTableName = {col['id']:col['fields']['type'] for col in gristCols if col['fields']['type'].startswith("Ref:") or col['fields']['type'].startswith("RefList:")}
# Remove reference beginning to directly be able to query reference table
gristRefTableName = {key:val.lstrip("Ref:").lstrip("List:") for key,val in gristRefTableName.items()}
gristRefColumns = {key: grist.call(url=f'tables/{val}/records')['records'] for key,val in gristRefTableName.items()}
# Query column definitions of reference columns
gristRefTableColumnDefs = {column: grist.call(url=f'tables/{refTable}/columns')['columns'] for column,refTable in gristRefTableName.items()}
# Match column definitions with the reference columns
gristRefFieldColumnDefs = {refColName: next((colDef for colDef in gristCols if colDef['id'] == refColName)) for refColName in list(gristRefTableName.keys())}
# Retreive id of referenced visible column
visibleColPerRefCol = {refColName: gristRefFieldColumnDefs[refColName]['fields']['visibleCol'] for refColName in list(gristRefTableName.keys())}
# Match the visible column with the column reference id to find the column id for each reference column
gristRefColumnName = {refColName: [colDef['id'] for colDef in gristRefTableColumnDefs[refColName] if colDef['fields']['colRef'] == visibleColPerRefCol[refColName]][0] for refColName in list(gristRefTableName.keys())}
# Replace reference values with name of value for all fields that are reference columns in grist
for idx, _ in enumerate(gristTable):
for refField in gristRefColumns.keys():
if type(gristTable[idx][refField]) == type([]):
# Dealing with a reference list, resolve all references as string and concat them with comma:
resolvedReferenceValueList = [sub for sub in gristRefColumns[refField] if sub['id'] in gristTable[idx][refField]]
if len(resolvedReferenceValueList) > 0:
gristTable[idx][refField] = ','.join(val['fields'][gristRefColumnName[refField]] for val in resolvedReferenceValueList)
elif type(gristTable[idx][refField]) == type(0):
# Dealing with single reference
resolvedReferenceValue = next((sub for sub in gristRefColumns[refField] if sub['id'] == gristTable[idx][refField]), None)
if resolvedReferenceValue != None:
gristTable[idx][refField] = resolvedReferenceValue['fields'][gristRefColumnName[refField]]
else:
raise Exception("Unknown reference field type, should never happen.")
return gristTableOriginal,gristTableUPDATE 2023-04-02:
|
|
Glad you got it working @Vinschni. It is silly that our /records endpoint doesn't deal with this, given that the download endpoints for csv and xlsx will. |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
The REST API does allow to follow a reference column to its original table as the
typecontains aRef:prefix, see here an example response for a columns request:{'id': 'Assignee', 'fields': {'colRef': 140, 'parentId': 11, 'parentPos': 121, 'type': ' Ref: People', 'widgetOptions': '{"widget":"Reference","alignment":"left","wrap":true}', 'isFormula': False, 'formula': '', 'label': 'Assignee', 'untieColIdFromLabel': False, 'summarySourceCol': 0, 'displayCol': 192, 'visibleCol': 2, 'rules': None, 'recalcWhen': 1, 'recalcDeps': None}}
--> But there is no way to know what column within the
Peopletable in this case theAssigneefield reference to.The API is not exposing that information, am I correct?
The text was updated successfully, but these errors were encountered: