I've got a workflow that runs daily to purge old records from a table - is there a way to output into the logs how many were deleted by the query?
All I'm seeing is start/end/success messages, how would I best drill into what the query did?
[Tue Nov 08 2022 07:00:18 GMT+0000] --- Running query: startTrigger ---
[Tue Nov 08 2022 07:00:18 GMT+0000] Evaluating JS query: startTrigger
[Tue Nov 08 2022 07:00:18 GMT+0000] --- Successfully finished running query: startTrigger ---
[Tue Nov 08 2022 07:00:18 GMT+0000] --- Running query: query1 ---
[Tue Nov 08 2022 07:00:19 GMT+0000] --- Successfully finished running query: query1 ---
[Tue Nov 08 2022 07:00:19 GMT+0000] --- Successfully finished running the workflow ---
In general, we’ve used the RETURNING clause in queries to have that kind of information returned to Retool.
Not sure about Workflows specifically, but I think doing this would result in the count being the output deleteQuery.data which you could use:
DELETE from table WHERE column = value RETURNING *;
I've tried it with a RETURNING clause and it works in the preview but the logs don't show anything other than start/finish entries.
So if it runs overnight, I can't see the next morning what it's done - unless I write out somewhere as part of the workflow, but that's going to start getting quite messy because I'll then need a job to clean out those logs
I can add a JS query from the DB one and console.log it's results, but if the workflow gets super complex that won't be ideal long term.
Hmm, gotcha. Asking the workflows team to see if they have any insights here!
Ah, yeah this sounds expected. The returning just sets the
query1.data property, so if you want it to go to the logs you’ll want to keep console logging it just in the way you suggested (JS block). We would suggest that as the way to do it. Otherwise, if you want to avoid those blocks, then you can also maybe make your SQL query a function (on the left hand side) and then create a single JS block on the canvas that runs it and logs it:
const results = await function1() console.log('Returned the following', results)
It’ll do the same thing but with fewer blocks on the canvas
That's awesome, thanks for confirming
I've had little (ie no) success with functions, unfortunately, but will persevere before raising a message here about it
Raise a message anytime! Even if it seems simple or you figure it out right after. It’s always helpful for us to know what our users get stumped by, and for other users to see the solution in case they run into the same thing.