Join Retool-Table with API-Query

💬 App Building
,
2

Hey there @mprobst,

If your final goal is to show the projec tName within the table component, then my suggested approach would be the following:

  • Add a custom column to your table, and selceter project_id as data source(
  • In the Mapped value setting, make a look up for your project, with something like {{ api_query.data.projects.find (project=> project.id === item).name }}
  • Change api_query for your api query's name. Item will refer to the project_id which you selected as your column's data source, if you don't select a data source then you can do {{ api_query.data.projects.find (project=> project.id === currentSourceRow.project_id).name }}

This will return the project name for each row.

If you would like a full query with all of your projects data then you can write a js query like the following:

// Assume api_query.data and sql_query.data are arrays of objects
const apiData = api_query.data;
const sqlData = formatDataAsArray (sql_query.data);

// Create a lookup map for quick project_id -> sql object
const sqlMap = new Map(sqlData.map(item => [item.project_id, item]));

// Merge arrays by project_id
const merged = apiData.map(apiItem => {
  const sqlItem = sqlMap.get(apiItem.project_id) || {};
  // Spread properties from both objects; sqlItem properties overwrite apiItem if duplicated
  return { ...apiItem, ...sqlItem };
});

return merged;

I have added formatDataAsArray to your sql data as it is needed for this kind of operation.

Hope this helps!

2 Likes