Bulk update - ListView - Postgres - Pagination

Queries and Resources
1

Hi!
I have a ListView and a Pagination control which I am populating with a limit/offset query, 20 items per page. I'm creating a "checklist" type page for myself (A list of movies, checkbox for if I've seen them or not, green/red background, etc.). It all works wonderfully, but I can't really figure out how to persist the checkbox changes back to the database.

I have managed to do so on the actual checkbox.change action (call the SQL query directly then), but that isn't the best UX, so I'd like to be able to somehow store the changes made with a primary key and the new value, then call a bulk update.

I was able to get this far:

update seen set seen = data_table.seen
from
(select unnest({{listView1.rowKeys}}::integer[]) as id_movie, 
        unnest({{ checkbox1.value }}::boolean[]) as seen) as data_table
where seen.id_movie = data_table.id_movie;

However, what fails there is that the "checkbox1.value" does not give me the array of all values of all checkboxes within the ListView. If I could just get that array, then I believe the postgres unnest function will just solve this for me.

The other way I tried was to figure out some way of pushing the <id_movie,seen> tuple into a set or array every time I click on the checkbox, and then use those arrays in the unnest function, but I couldn't figure out how to use an array in LocalStorage.

I also thought, maybe I could update the actual dataset returned from the query and use that in the unnest function, but didn't make any headway there.

What would be the smartest way forward here? Ideally the user browses through pages of checkboxes, click, click, click, and then hits save when she's done and all the updates get pushed down. If it's easier, it's fine to push the entire dataset down, even the unchanged stuff.

2

Try checkbox1[i].value

3

Thanks for the response. So checkbox1[i].value doesn't return an array, neither does checkbox1.value. Both of them return just a single 'true' value.

4

Need more info - screenshots, more specific information....

5

Not sure exactly where you need more information, but I'll give it a shot.

Here's what the app looks like

image
image1027×582 159 KB

If I click on a movie, I toggle the checkbox. The component structure is like this
image

This view is read from a database (a very simple table which maps a user ID to a movie ID and has a boolean "seen". The "seen" value is mapped to the checkbox.

I would like to persist changes to the state of the checkbox back into the database.

I have figured out how to do so on the Change handler of the checkbox, however the UX of that is too heavy. So I would like to have a save button somewhere which persists all changes made in the UI, down into the database in a bulk update.

As I am using Postgres, this is possible with the elegant unnest function, and for that I simply need two arrays:

Array 1 - An array of movie ids
Array 2 - an array of updated checkbox states

I can easily find the array of movie ids, that does not change and so is simply in the datasource. However, getting the array of checkbox states is a challenge.

How can I retrieve the array of checkbox states from a ListView containing a checkbox?

6

Can you drill further into the ListView so as to see where the checkbox element lives?

7

You can see here that I added a checkbox to the list view and looking at the state I see:

Screenshot 2023-10-23 at 9.00.22 AM
Screenshot 2023-10-23 at 9.00.22 AM1465×520 83.4 KB

1 Like
8

Yep, I see that too. If I reference the checkbox object directly it contains an array of objects (in addition to other things), like this
image

I'd like to reference the value of each checkbox as an array of booleans.

9

OK so the way I have been able to do something like this in other apps is to build a temp array call it pendingCheckBoxUpdates - don't set it to any initial value then as you select each check box, you do something like the following:
Upon each checkbox selected, trigger an event handler to call a JS query containing the following:

if(pendingCheckBoxUpdates.value === "") {
const myCheckBoxValues = [{"checkbox":""}];
pendingCheckBoxUpdates.setValue(myCheckBoxValues)
myCheckBoxValues.setIn([0, "checkbox"],yourCheckBoxFieldName[i].value); } else {
pendingCheckBoxUpdates.setValue(pendingCheckBoxUpdates.value.concat("checkbox":yourCheckBoxFieldName[i].value));
}
return formatDataAsArray(pendingCheckBoxUpdates.value)
1 Like
10

Thanks, yeah I figured I'd have to do something like this. I was hoping there was some kind of built in solution, as this seems like a common use case. I appreciate the code!

Doesn't this have to refer to localstorage somehow? Or do the variables stay in scope in between calls?

11

Oh I see you want the selections to follow the user... so yes you could push it into localStorage or you could add an array field in the db to store the ones selected....

1 Like
12

Thanks. I did some version of this and it seems to work. Here's what I ended up with:

On the Change event of the Pagination control, I added two event handlers.
One with a script:

let checkArray = [20]
let idArray = [20]
for (let i = 0; i < 20; i++) {
  checkArray[i] = checkbox1[i].value
  idArray[i] = ChecklistListView.rowKeys[i]
}
localStorage.setValue("CheckState", checkArray);
localStorage.setValue("IDState", idArray);

A bit brute force, but basically it just extracts the two arrays I need.

The second event handler on the same event is the sql query:

update seen set seen = data_table.seen
from
(select unnest({{localStorage.values.IDState}}::integer[]) as id_movie, 
        unnest({{ localStorage.values.CheckState}}::boolean[]) as seen) as data_table
where seen.id_movie = data_table.id_movie;

Seems to work! Thanks for the hints :slight_smile:

1 Like
13

Glad you found a solution. Always happy to help.

1 Like