Provide an example of customizing the filter argument of DT::dataTableAjax
yihui opened this issue · 3 comments
e.g. using SQL to process the data instead of R
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!
The search and filter box is not working in dtdatasources.
@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)
}