Background

I've recently been working on a couple of database interfaces in R Shiny. One interesting conundrum is what to do with "big" data. For example, if I have a table of 50,000 rows, it might not be optimal to pull all of that data down at one time as the user may not need or want all of that data. Additionally, doing things this way often results in unnecessary data transfer which can slow the server down.

In exploring my options, it seems that the two main ways to handle this are with "pagination" UI or "infinite scroll" UI. Pagination UI is the default for pretty much all of the table-rendering packages I've used. Since converting to the reactable package from DT, both pagination and infinite-scroll options have turned out to be headaches.

Pagination is the more traditional approach and is (probably) the better method for database interfaces. I posted about how to make reactable's pagination play nice with iteratively fetching data on Stack Overflow just today. I did receive a helpful solution from one handsome devil, but I hope the question generates some other, more elegant, solutions in the future.

Out of curiosity, I also wanted to try to figure out infinite scrolling. With a little javascript know-how, I was able to figure it out!

Solution

One of the first key things to do is to wrap your reactable::reactableOutput() inside of a div() with an associated id. We will need this id to set up the javascript event listeners. The style argument that specifies "overflow-y: auto;" is incredibly important as it allows us to monitor the container's scroll values.

# example div
shiny::tags$div(
    id = "react-table-container",
    reactable::reactableOutput("table"),
    style = "height:100%; overflow-y: auto;"
)

Next, we need to add in the javascript function that monitors the scrolling and can talk to Shiny.

document.addEventListener('DOMContentLoaded', function () {
    const container = document.getElementById('react-table-container');
    if (container) {
        container.addEventListener('scroll', function () {
            const scrollTop = container.scrollTop;
            const scrollHeight = container.scrollHeight;
            const clientHeight = container.clientHeight;
            // Send scroll event to Shiny
            Shiny.setInputValue('table_scroll', { scrollTop, scrollHeight, clientHeight }, { priority: 'event' });
        });
    }
});

You'll note that if you gave your div() a different id, you would need to replace "react-table-container" with that id. Additionally, if you plan on changing the input name of "table_scroll", you'll need to modify that here as well.

Most of the work is done! Now we need to generate some toy data and set up how the iterative fetch should work.

# Simulated data source
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "TestTable", data.frame(
    id = 1:1000,
    value = sample(LETTERS, 1000, replace = TRUE)
))

# Reactive storage for table data
dt <- reactiveValues(data = NULL)

# get total rows
dt$rows <- DBI::dbGetQuery(con, "SELECT COUNT(*) AS Total FROM TestTable")$Total

# Function to fetch data in chunks
fetch_data <- function(last_id = 0, n = 50) {
    query <- sprintf("SELECT * FROM TestTable WHERE id > %d ORDER BY id LIMIT %d", last_id, n)
    dbGetQuery(con, query)
}

# Load initial data
dt$data <- fetch_data()

As you can see, we're planning on fetching 50 rows at a time and we need to supply an initial data load upon app start. Our reactable is rendered with the "pagination = FALSE" flag to ensure that infinite scroll can work.

# Render the table
output$table <- renderReactable({
    reactable(
        dt$data,
        pagination = FALSE, # Disable default pagination for infinite scrolling
        highlight = TRUE,
        sortable = FALSE
    )
})

For the scrolling logic, we can use "input$table_scroll" (which is populated with our javascript function) to compare where the user is at in the table compared to the height of the table, which should give us a good indication of when we need to fetch more data. Once that limit has been hit, we can fetch the next 50 rows and "rbind()" those rows to what we already have in our app.

# Observe scroll events
observeEvent(input$table_scroll, {
    scroll_info <- input$table_scroll
    # Example: Check if the user scrolled to the bottom
    if (!is.null(scroll_info)) {
        if (scroll_info$scrollTop + scroll_info$clientHeight >= scroll_info$scrollHeight) {
            current_data <- dt$data
            last_id <- if (nrow(current_data) > 0) max(current_data$id) else 0
            new_data <- fetch_data(last_id)
            if (nrow(new_data) > 0) {
                dt$data <- rbind(current_data, new_data)
            }
            shiny::showNotification(
                glue::glue("{nrow(dt$data)}/{dt$rows} rows pulled."),
                type = "message",
                duration = 3
            )
        }
    }
})

And that's pretty much it! As one last nicety, we drop the fake table and close the connection on app stop.

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

In Action

For the full script, please see https://gist.github.com/grcatlin/2250d487887e353cadc377f4b5f5477e

To see this app in action, you can run it locally by installing the dependencies and running:

# packages needed
pkgs <- c("bslib", "DBI", "reactable", "RSQLite", "shiny")
if (any(!pkgs %in% installed.packages()[, 1])) {
    pkgs <- pkgs[!pkgs %in% installed.packages()[, 1]]
    install.packages(pkgs, repos = "https://cloud.r-project.org")
}

# run gist
shiny::runGist("2250d487887e353cadc377f4b5f5477e")

Infinite Scrolling in reactables in R Shiny

Background

Solution

In Action