Get changes of a temporary state in a table without recordupdates or changeset

I am trying to find the best way to push changes made to a temporary state back to a database.

My process:

  1. Initialize a temporary state with the results of a query
  2. Populate a table with that temporary state
  3. Make edits to the temp state via button presses
  4. Periodically push the changes in the temp state to the database

I am running into a problem in that, the way I am making changes to the table (temp state) is via button presses. I am using the setIn() function to change the temp state values based on UI button presses. And since I am editing values programmatically and not within the table, the changes are not in recordUpdates() or changeSet(). If I were to edit directly from the table cell, the change would be captured, but since I am setting the state value with a button it is not.

What is the best method for identifying the changes to the state and saving these out to a database?

image

image

image

Since the changes are made in the temporary state, can't you update the db with the entire temporary state value using additionalScope?
https://docs.retool.com/docs/scripting-retool#resolve-and-reject

I'm struggling to wrap my head around how that would look in SQL mode.

  • For passing as additionalScope, you can do additionalScope: {component: JSON.stringify(table1)} as a value there, and then JSON.parse(component) inside of the target query.

Is this along the right path?

@ScottR is right, use the Bulk Update query and send the whole temp state to it. I assume if are managing the whole table in temp state you aren't talking thousands of records.

I have tried something similar but still used the .recordUpdates as my bulk update source, then re-queried to sync the server and local.

I'm curious as to why you are going this route and all the extra work it entails - is it to get around the performance issues with repainting the table after updating? Is there big difference in your db structure and the fields in the table component? Or something else?

@bradlymathews @ScottR Thanks to you both.

I'll need to do this via code (SQL mode or js script) since the destination db table is different depending on the user. right now i have a stored procedure which creates this table and gives it a unique name. Roughly how would this look in SQL mode?

A lot of what i am doing is getting around BigQuery and retool limitations. My app's sole purpose is to be able to quickly go row to row updating values. streaming directly off the database was too time consuming, as was making table edits inside the table itself. It's much faster to go through rows via button clicks on the tempstate. So going one way to get around a limitation caused another hurdle to appear. so on and so forth.

@bradlymathews @ScottR

I might just recreate the entire table instead of detecting changes. Definitley getting later in the day and feel like I'm missing something simple.

create or replace table `gis-postgres-testing.poi.mytesttable` as (select {{ tempState.value }})

So going one way to get around a limitation caused another hurdle to appear. so on and so forth.

I am way too familiar with this pattern!

From your description I am only seeing the elephant's butt and do not know what the rest of the beast looks like, so its hard to give you specific advice. But some general advice - if things start to get too complicated to deal with, take a step back and look at your foundations and architecture. I go this direction because you mentioned a different table for each user. This is a bit of an architectural red flag. There is almost certainly a different way to do it that simplifies things and makes some of your problems moot.

Again, I am only seeing a small piece of the elephant's arse, so I may be missing the mark, but there it is!

1 Like

appreciate that very much. it is easy to get swept away chasing one's tail.

the reason for the different tables is concurrent edits on the same BQ table seem to cause quite a bit of a slowdown.

so the idea was to generate a separate table based on the user and have them push edits to that. then post process the changes back to the master at an interval in a separate post-process.

definitely getting complicated that way.

could just pull from BQ, then write to postgres or something, then these problems would be become moot.

Yeah, if you are intervalling the changes back to BQ anyway, then use a single Postgres table to cache the changes for all users.

I have not worked with BQ at all so am unaware of its peccadilloes.

1 Like

Yeah I agree and think that makes the most sense. Now the question is... how to get the temporary state changes to postgres via additional scope? can this all be done in the postres resource query GUI or, does it need to be a separate query to trigger passing additional scope as a parameter?

If the temp state is in a form that is ready to pass to the query (an array of objects with keys the same as the table's column names) then you can use the Array of Records to Update.

If it is not quite ready you can use a transformer to make it ready and use the transformer as the source for the Array of Records.

If you need to perform some other logic first then is a JS query to do that and then trigger your bulk query using additionalScope passing the array to update.