Join Retool-Table with API-Query

💬 App Building
,
1

Hi,

I have two datasources: one is a Retool-Database containing some keys which are referenced in other tables. Amongst others it contains these columns:

image
image424×568 27.8 KB

Now I would like to join this table with the results of an external API call by the key "project_id". The call yields something like:

image
image722×650 66.6 KB

The final goal is to show the Project Name (which comes from the API-call) based on the Project ID (that I store in the Retool-Database).

image
image610×288 8.97 KB

I have tried something like in the query image that is linked to the table:

SELECT 
  * 
FROM 
  "Template_Hours" e
JOIN
  {{ queryProjects.data.projects }} f
ON
  e.project_id = f.id
WHERE 
  e.template_id = {{ selectTemplate.value }}

However, I get a syntax error:

image

I suspect that the problem is hidden in the format of the two datasources but cannot get a grip on it. Maybe I am doing something fundamentally wrong?
I also tried a SQL query image but there I am not able to reference the Retool-Database?

Any help is highly appreciated.

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
3

Hi @MiguelOrtiz,
this works like a charm. Many thanks!
Regards,
Martin.

1 Like