Provide an example of customizing the filter argument of DT::dataTableAjax

[yihui]

e.g. using SQL to process the data instead of R

[grahamrp]

I've just started a dtdatasources package to provide different funcFilter implementations and examples. Right now it's a work in progress, with just a basic SQLite example. Feedback and contributions are welcome!

[ShixiangWang]

The search and filter box is not working in dtdatasources.

[JerePlum99]

@yihui @grahamrp Can the funcFilter param can be a reactive itself?

I'm attempting to use the dtdatasources framework with some minor adjustments that allow me to pass in a lazy query (generated with reactive selectInputs) and then paginate through that result.

I've been able to get it to work on a lzy_tbl but am running into an issue once I introduce reactivity - I'm not sure if I'm simply passing it into DT::renderDT wrong or if it's something else.

I can add the fully modified dtdatasources code as a reprex but given it's bit lengthy I figured I'd ask about the funcFilter reactivity first.

Here's my non-reactive example:

test_ajax_app <- function() {
  
  # Setup example database and table. Add mtcars rownames as a primary key.
  con <- pool::dbPool(
    RSQLite::SQLite()
  )
  onStop(function() {
    pool::poolClose(con)
  })
  
  dbWriteTable(con, "mtcars", cbind(car = rownames(mtcars), mtcars))
  
  ui <- fluidPage(
    DTOutput("tbl"),
    verbatimTextOutput("debug")
  )
  
  server <- function(input, output, session) {
    
    # Create an initial dataframe. This only needs to contain column names, and
    # need not have any rows.
    initial_df <- dbGetQuery(con, "SELECT * FROM mtcars LIMIT 0;")
    
    mtcars_query <- dplyr::tbl(con, "mtcars") |>
      dplyr::filter(cyl %in% c(6,4))
    
    # Create a funcFilter function describing how to get data for a datatable.
    # The filter factory
    mtcars_filter <- sql_filter_factory$sql_filter_factory(
      # Use the sqlite connection created above
      con = con,
      query_fun = sqlite$query_sqlite,
      query = mtcars_query,
      id_field = "car" # Field used to identify a row when using input$tbl_rows_selected
    )
    
    output$tbl <- renderDT(
      initial_df,
      server = TRUE,  # Must be TRUE to perform processing in R, not in the browser
      rownames = FALSE,  # Must be FALSE
      funcFilter = mtcars_filter  # Provide the sqlite function filter
    )
    
    output$debug <- renderPrint({
      input$tbl_rows_selected
    })
  }
  
  shinyApp(ui, server)  
}

And here's the reactive version that's not working:

test_ajax_app <- function() {
  # Setup example database and table. Add mtcars rownames as a primary key.
  con <- pool::dbPool(
    RSQLite::SQLite()
  )
  
  onStop(function() {
    pool::poolClose(con)
  })
  
  dbWriteTable(con, "mtcars", cbind(car = rownames(mtcars), mtcars))
  
  ui <- fluidPage(
    selectInput("cyl", "Cylinder", choices = c(4, 6, 8)),
    DTOutput("tbl"),
    verbatimTextOutput("debug")
  )
  
  server <- function(input, output, session) {
    # Create an initial dataframe. This only needs to contain column names, and
    # need not have any rows.
    initial_df <- dbGetQuery(con, "SELECT * FROM mtcars LIMIT 0;")
    
    mtcars_filter <- reactive({
      mtcars_query <- dplyr::tbl(con, "mtcars") |>
        dplyr::filter(cyl == as.integer(input$cyl))
      
      sql_filter_factory$sql_filter_factory(
        # Use the sqlite connection created above
        con = con,
        query_fun = sqlite$query_sqlite,
        query = mtcars_query,
        id_field = "car" # Field used to identify a row when using input$tbl_rows_selected
      )
    })
    
    output$tbl <- renderDT(
      initial_df,
      server = TRUE, # Must be TRUE to perform processing in R, not in the browser
      rownames = FALSE, # Must be FALSE
      funcFilter = mtcars_filter() # Provide the reactive filter function
    )
    
    output$debug <- renderPrint({
      input$tbl_rows_selected
    })
  }
  
  shinyApp(ui, server)
}