Run query for every row of table and return values for each in array?

Queries and Resources
1

Hi,

I am trying to run a query for every row in a table or query.

Example being either for each user or for every hour.

I had seen this example:

Promising an array of query returns

const promises = table1.data.map((row) => {
return query1.trigger({
additionalScope: {
id: row.id,
},
});
});

return Promise.all(promises);

This is meant to run the query for each row in the table passing the ID in an an argument and return the results in an array?

Edit for a clearer example.

If I have 2 tables one for users and one for orders.

If the orders table has a user field and I had a query that returned the number of orders for a certain user name called getOrderCount, which was something simple like

Select count(order_id) from orders where user = {{user}}

and the code above was change to:

const promises = userTable.data.map((row) => {
return getOrderCount.trigger({
additionalScope: {
user : row.username,
},
});
});

return Promise.all(promises);

Where userTable has all the usernames listed from the user table.

Select username from users;

Does this return the array of counts? - return Promise.all(promises);

Or is this just an array showing the queries have all completed so it has to wait before it carries on?

How do I return the values from multiple calls to getOrderCount query as an array and use that as the data source for table?

So would then have a table with username and the order count for example.

This is a basic example the thing I am trying to do, I want to be able to call multiple queries per user and then populate a table with the results.

So it might have ordercount, averageorder price, latest order number, etc etc for each user in the user table.

Hope this makes more sense.

Thanks.

2

Queries are promises. If I understand you correct you can, in a run script or is query,

For(...) {
Query.trigger({
additionalScope : {"item" : arrayitem[I]},
OnSuccess : returnedQueryData => {do stuff with query data}
});
}

3

It should return an array of the counts!

If a JavaScript query returns a promise it will run until the promise resolves and then pass the fulfillment value to the query.data property. As @Shawn_Crocker mentioned, triggering a query returns a promise that resolves to whatever data the query returns. So, in this case, because of how Promise.all behaves, when you return Promise.all(promises) the JavaScript query will run until each triggered query has finished and then return an array of the results!

That might look something like:

[
  {"count":["8"]},
  {"count":["4"]},
  {"count":["2"]}
]

Which may not be the exact format you're looking for for the table so you can try using a .then statement to make sure the data you want is returned:

const promises = table1.data.map((row) => {
  return query1
    .trigger({
      additionalScope: {
        id: row.id,
      },
    })
    .then((data) => ({ id: row.id, count: data.count[0] }))
})

return Promise.all(promises)

Which returns:

[
  {"id":1,"count":"8"},
  {"id":5,"count":"4"},
  {"id":7,"count":"2"}
]

Great for the table!

This is only one way to do it though. There are a bunch of other ways as well, but hopefully this is illustrative!

4

Ahh. I see. So with promise.all, @Kabirdas, a js query will wait for all queries triggered to complete even when it's the same query triggered many times in a loop? I have been setting up an intricate system of incrementing a variable in the OnSuccess and then testing it to check if it is equal to the loop size. And then triggering some other query that needs the complete array that was built from the loop of query results. All because I couldn't find a way to effectively await for them all without awaiting each one individually.

5

Yes, exactly! You just need to either use await Promise.all() or return Promise.all().

6

Hi @Kabirdas,

Wonder if you can help me - I'm trying to adapt what you've explained here but I am stuck on something (that I think might be simple!).

My query is:

const promises = theTeam.data.map((row) => {
  return getTimeLogsFromJS.trigger({
      additionalScope: {
        accountId: row.jira_account_id, from: fromTo.value.start, to: fromTo.value.end,
      },
    })
    .then((data) => ({  results: data.results }))
})

return Promise.all(promises)

The result is of the structure shown below, however I just want to have an array of 'results'. The reason is that if I try to use this query as a source for a table as below, it says the result set is empty:

{{getTempoTimeLogsNEW.data[i].results]}}

The above is empty, but if I put 'data[1].results' temporarily, it gives me data, but just for the second element in the array.

Screenshot 2023-07-01 at 16.36.17
Screenshot 2023-07-01 at 16.36.171452×958 100 KB

Any help would be greatly appreciated.

Thanks!
Nick

7

For anyone facing a similar challenge - I found the answer on Stack Overflow here

Specifically, I created a JS Transformer that looks like this:

var arr = {{ getTempoTimeLogs.data }}

const res = arr.flatMap(({ results }) => results);

return (res)

This gives me a simple array of all logs which I can directly reference in as a table source as:

{{tfrm_tempoTimeLogs.value}}
1 Like
8

Thanks for posting your solution here @nickaus!

The syntax is sometimes slightly different depending on what's returned by the API. Retool supports await in JS queries too which means you can potentially avoid adding another transformer with something like this:

const results = await Promise.all(promises);
return results.flat();

Let me know if that works!

9

Hi @Kabirdas
Thanks for the tip - looks promising!
I will give it a try - diverted to other business atm.

Regards,
Nick