Enterprise Edition
Version info
5.3.12
I have a script in Python that updates links to other records(connects entries in table Projects with entries in table Company which is in BIG DATA) that works fine
search is made like this:
sql = f"“”
SELECT _id, {COMPANY_CN_TEXT_COL}
FROM {COMPANY_TABLE}
WHERE {COMPANY_CN_TEXT_COL} IN ({in_list})
“”"
results = base.query(sql) or
and then in BIG DATA (Company table) write a link to the corresponding Project
comp_link_id = base.get_column_link_id(COMPANY_TABLE, COMPANY_LINK_COL) # get only affected company rows fresh affected_ids_csv = ",".join([f"'{i}'" for i in matched_ids]) sql_back = f"SELECT `_id`, `{COMPANY_LINK_COL}` FROM `{COMPANY_TABLE}` WHERE `_id` IN ({affected_ids_csv})" back_rows = base.query(sql_back) or [] row_ids = [] other_map = {} for r in back_rows: cid = r.get('_id') back_links = r.get(COMPANY_LINK_COL, []) or [] back_ids = [lnk.get('row_id') for lnk in back_links if isinstance(lnk, dict) and lnk.get('row_id')] if project_row_id not in back_ids: back_ids.append(project_row_id) row_ids.append(cid) other_map[cid] = [str(x) for x in back_ids] if row_ids: base.batch_update_links( link_id=comp_link_id, table_name=COMPANY_TABLE, other_table_name=PROJECTS_TABLE, row_id_list=row_ids, other_rows_ids_map=other_map )
The link in BIG DATA view is normaly visible. The only problem is, that the script is slow, and I wanted to make the same with javascript: Again SQL:
// — SQL: match Company by exact Customer number —
const sql = SELECT \_id`, `${COMPANY_CN_TEXT_COL}`
FROM `${COMPANY_TABLE}`
WHERE `${COMPANY_CN_TEXT_COL}` IN (${buildInList(rawCodes)})
`;
const results = await base.query(sql) || ;
// map Customer number -> _id
const foundMap = {};
for (const r of results) {
if (r && r._id != null && r[COMPANY_CN_TEXT_COL] != null) {
foundMap[String(r[COMPANY_CN_TEXT_COL])] = String(r._id);
}
}
const matchedIds = [];
const foundVals = [];
const missingVals = [];
for (const c of rawCodes) {
const rid = foundMap[c];
if (rid && !matchedIds.includes(rid)) {
matchedIds.push(rid);
foundVals.push(c);
} else if (!rid) {
missingVals.push(c);
}
}
if (!matchedIds.length) {
await setStatus(projectRowId, `⚠️ Ni ujemanj v Company za: ${missingVals.join(', ')}`);
return;
}
But linking Company in BIG DATA does not show a link:
if (matchedIds.length) {
const idsCsv = buildInList(matchedIds);
const sqlBack = `
SELECT \`_id\`, \`${COMPANY_LINK_COL}\`
FROM \`${COMPANY_TABLE}\`
WHERE \`_id\` IN (${idsCsv})
`;
const backRows = await base.query(sqlBack) || [];
const compLinkId = await base.getColumnLinkId(COMPANY_TABLE, COMPANY_LINK_COL);
for (const r of backRows) {
if (!r || !r._id) continue;
const companyId = String(r._id);
const backIds = extractLinkRowIds(r[COMPANY_LINK_COL]);
if (!backIds.includes(projectRowId)) {
const mergedBack = backIds.concat([projectRowId]);
// update for this single company row
await base.updateLinks(
compLinkId,
COMPANY_TABLE,
PROJECTS_TABLE,
companyId,
mergedBack
);
}
}
}
Any suggestions what could be wrong?
.
