Calling a Query within a Transformer?

Queries and Resources
,
1

I have a Resource query (let's call it OrdersQuery) that uses internal API to pull my database's Orders table and create a Retools table called ordersTable. On ordersTable, I want to add a custom column "customer_name" that uses each row's "customer_id" field as a foreign key to pull data from my database's Customer table. What is the best way to do this?

I have a script in the backend that allows for filtering the Customer table by customer_id and returning the customer's data, and the route is customers/<customer_id>. On Retool so far I've tried the following:

  1. I created another Resource query (CustomersQuery) with the API route ending in customers/{{ordersTable.selectedRow.data.customer_id}}, but this causes the "customer_name" field to show the same value for ALL of the rows, which is incorrect. I've tried changing "selectedRow" to "currentRow" which I saw while Googling, but that doesn't exist. I also just tried changing the API route to customers/{{ordersTable.data.customer_id}} but that throws the error message I put in the backend for when an 'undefined' customer_id is passed in.
  2. I changed CustomersQuery's API route to be customers/{{OrdersQuery.data[i].customer_id}}, hoping that for each row in OrdersQuery's data, it would pass in the customer_id for each row, but that doesn't work either.
  3. Keeping what I did in #2, I enabled OrdersQuery's transformer, added a for loop using i to index through, and wrote this in the loop: data[i]["customer_name"] = {{CustomersQuery.data.name}}. This created the "customer_name" column but threw the error message I put in the backend for when a null customer_id is passed in.

Am I getting close with #3? Is there a way to directly input each row's customer_id into CustomersQuery within the for loop?

2

Hi @christine, welcome to the community.

Not exactly what you are looking for but based on what you have done, it seems you want to do a loop to run your customer's API endpoint. I would approach this differently as you want to display Orders x Customers data. Does your customer API endpoint pull all the customer data?

If yes, you can essentially use Query JSON with SQL resource to something like:

SELECT *
FROM {{ ordersQuery.data }} o
JOIN {{ customersQuery.data }} c ON o.customer_id = c.id

And then you can use the data output from here to use in your orders table and not use a custom column.

If no, you have a complex route to loop on your displayed orders' customer id and run your api endpoint for customers. You might want to use promises here.