How to iteratively fetch data in reactable in R?

Question

I'm trying to interface with a database within a shiny app in R. The gist of the issue is that the table in question has many rows and I'd prefer not to pull the entire table down to save on data transmission times. Instead, I'd like to iteratively pull data based on user input - specifically some combination of pagination controls with other shiny inputs to drill down the data (not shown).

My initial thought was to use reactable::getReactableState("table", "page") and use this to construct a SQL query. Something like:

library(bslib)
library(shiny)
library(reactable)
library(data.table)
library(RSQLite)

shiny::shinyApp(
    ui = bslib::page_navbar(
        bslib::nav_panel(
            title = "Reactable",
            bslib::card(
                reactable::reactableOutput("table")
            )
        )
    ),
    server = function(input, output, session) {
        # Connect to database
        con <- dbConnect(RSQLite::SQLite(), ":memory:")

        # Create table
        DBI::dbExecute(
            con,
            "CREATE TABLE TestTable(TestID INTEGER PRIMARY KEY AUTOINCREMENT, TestValue INTEGER)"
        )

        # Write toy data
        dt <- data.table(TestValue = runif(n = 50000, min = 1, max = 10) |> round())
        dbWriteTable(con, "TestTable", dt, append = TRUE, row.names = FALSE)

        # Function to fetch data for the current page
        fetch_data <- function(page, page_size, total_rows) {
            offset <- (page - 1) * page_size
            query <- paste0(
                "SELECT * FROM TestTable ORDER BY TestID LIMIT ", page_size, " OFFSET ", offset
            )
            y <- dbGetQuery(con, query) |> data.table::as.data.table()

            # toy example of injecting NA rows
            x <- 1:total_rows
            x <- data.table::data.table(
                TestID = x[!x %in% y$TestID]
            )
            y <- rbind(x, y, fill = TRUE)
            y <- y[order(TestID)]

            return(y)
        }

        # reactive values to hold data + pagination informatino
        vals <- shiny::reactiveValues(
            data = NULL,
            page_size = 10,
            page = 1,
            total_rows = NULL
        )

        # Fetch the total number of rows
        vals$total_rows <- dbGetQuery(con, "SELECT COUNT(*) AS Total FROM TestTable")$Total

        # initial data pull
        vals$data <- fetch_data(1, 10, dbGetQuery(con, "SELECT COUNT(*) AS Total FROM TestTable")$Total)

        # when the page changes, fetch new data
        shiny::observeEvent(
            reactable::getReactableState("table", "page"),
            {
                vals$page <- reactable::getReactableState("table", "page")
                vals$data <- fetch_data(vals$page, vals$page_size, vals$total_rows)
                reactable::updateReactable("table", data = vals$data)
                reactable::updateReactable("table", page = vals$page)
            }
        )

        # render
        output$table <- reactable::renderReactable({
            reactable::reactable(
                fetch_data(1, 10, dbGetQuery(con, "SELECT COUNT(*) AS Total FROM TestTable")$Total),
                sortable = FALSE,
                pagination = TRUE
            )
        })

        # Drop table on stop
        shiny::onStop(function() {
            dbExecute(con, "DROP TABLE TestTable")
            dbDisconnect(con)
        })
    }
)

As I see it, there are a couple key issues:

1. The only way I could figure out how to get the pagination UI to render without passing actual data was to inject NA rows as shown in fetch_data. This is not ideal, but only passing the reactable 10 rows (preferred) means that, while pagination = TRUE, the pagination UI never shows up unless you supply more rows.

2. More importantly, the observeEvent() on reactable::getReactableState("table", "page") meant that:

If a user clicked page 3, for example, the script would correctly fetch the corresponding rows, update the data, etc. However, once the new data was populated, the table would have to re-render which triggers the observeEvent() on reactable::getReactableState("table", "page") once again with page = 1. Once that happens, it would pull the data corresponding the first page and re-render the table - overwriting the correct page (3) and correct data. If the reactable::renderReactable() does not render a reactiveVal and instead reactable::updateReactable(data = x) is used, it creates an infinite loop.

My solution was to basically mimic the pagination controls that reactable creates with a uiOutput() and renderUI() which was a pain. I intend to post it as a solution, but it feels hacky and I'm hoping there's some way to implement something like this that is more straightforward.

How to iteratively fetch data in reactable in R?

Answers (1)

Related Questions