I have a Shiny app that reads some tables from a database and lets the user edit them. There is anomalous behavior at one point that I'm hoping someone can explain. Apologies for the necessarily long post.
The app has two reactive lists, defined as follows.
database <- reactiveValues() # holds the list of tables
display <- reactiveValues() # holds the display versions of the tables
Each database table XYZ is read from the database and added to the first list as a dataframe (database$XYZ). A slightly modified version is then added to the second list (display$XYZ). For instance, 0-1 integer columns in the database table are converted to true/false logical values in the display table.
Table XYZ is displayed using renderDT and DTOutput with row selections limited to one row. There is a button to delete the selected row and an event observer to react to a button click. The misadventure occurs inside the event observer.
Every table has an integer primary key field called "UID". The key portion of the event observer is as follows.
record <- database$Committee[input$display_committee_rows_selected, ]
uid <- record[1, "UID"]
dbExecute(con, paste0("DELETE FROM Committee WHERE UID = ", uid))
database$Committee <- dbGetQuery(con, "SELECT * FROM Committee")
display$Committee <- displayCommittee(database)
The first line gets the selected row, the second extracts the value of the primary key, the third deletes the row in the database, the fourth reloads the database in its entirety and the fifth calls a function that does the display formatting for the revised dataframe.
Let's say that on initial load the dataframe has 17 rows, with keys 42, ..., 45 in the last four rows. The user selects row 14 (key 42) and deletes it. The displayed table updates to 16 rows, with keys 43-45 in rows 14-16, and the database shows the deletion occurred. So far, so good.
Now the user selects either row 15 (key 44) and clicks the delete button again. Inside the event observer, the selected row is correct (15), but the UID, which should be 44, is shown as 43 (which was the UID of rows 15 before the first row deletion). The value of record (the selected row in the table) does show key 43, meaning it's the 15th row of the original table, not the updated table. The record with key 43 is indeed deleted from the database, and the display table updates accordingly.
It gets weirder. If I change the first line of code above to
record <- display$Committee[input$display_committee_rows_selected, ]
(i.e., get the record and thus the UID from the display version of the table rather than the database version), the code works correctly ... even though the display version is computed from the database version.
The code is working with that change, but I would really like to understand what is going on.