Searching multiple columns of Datatable within Shiny

Question

I am currently trying to make an R shiny app where the user can search across multiple columns in each row. This function works using the datatable function from the DT package outside of the shiny environment. Using the iris dataset as an example, I want to search for all rows containing the values; 5.1, 3.5, and 1.4. If I type the following string in the search box of the interactive datatable window "5.1 3.5 1.4" rows 1 and 18 are displayed.

library(DT)
head(iris)

#   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
# 1          5.1         3.5          1.4         0.2  setosa
# 2          4.9         3.0          1.4         0.2  setosa
# 3          4.7         3.2          1.3         0.2  setosa
# 4          4.6         3.1          1.5         0.2  setosa


datatable(iris)

The problem is when I try and do the same thing within a shiny environment I receive the message

No matching records found.

For example:

if (interactive()) {
  library(shiny)
  shinyApp(
    ui = fluidPage(fluidRow(column(12, DT::dataTableOutput('tbl')))),
    server = function(input, output) {
      output$tbl = DT::renderDataTable(
        iris, options = list(lengthChange = FALSE)
      )
    }
  )
}

Does anyone have a work around, or could tell me what I'm doing wrong?

Answer 1

Update: I have implemented the smart filtering in the server-side processing mode and it is enabled by default. With DT >= 0.2.29, it should work out of the box:

devtools::install_github('rstudio/DT')
library(shiny)
shinyApp(
  ui = fluidPage(fluidRow(column(12, DT::dataTableOutput('tbl')))),
  server = function(input, output) {
    output$tbl = DT::renderDataTable(
      iris, options = list(search = list(search = '5.1 3.5 1.4'))
    )
  }
)

---

You can ignore the old answer below.

You can enable regular expressions in searching (see more examples in the DT documentation).

library(shiny)
shinyApp(
  ui = fluidPage(fluidRow(column(12, DT::dataTableOutput('tbl')))),
  server = function(input, output) {
    output$tbl = DT::renderDataTable(
      iris, options = list(search = list(regex = TRUE))
    )
  }
)

In the above example, I used the regular expression 5.1|3.5|1.4. Please note that it means "find the values 5.1, 3.5, or 1.4 in any columns". If you need to find 5.1 in the first column, 3.5 in the second column, and 1.4 in third column, there is no way to do it in the mode of server-side processing with a single search string (a single regular expression cannot express this). You have to use either the client-side processing (i.e., server = FALSE, as you have discovered), or sort the columns to find the combination you need:

or use column filters to filter individual columns:

library(shiny)
shinyApp(
  ui = fluidPage(fluidRow(column(12, DT::dataTableOutput('tbl')))),
  server = function(input, output) {
    output$tbl = DT::renderDataTable(
      iris, filter = 'top'
    )
  }
)

Answer 2

To anyone else with the same issue you need to server=FALSE to the renderDataTable function.

if (interactive()) {
  library(shiny)
  shinyApp(
    ui = fluidPage(fluidRow(column(12, DT::dataTableOutput('tbl')))),
    server = function(input, output) {
      output$tbl = DT::renderDataTable(
        iris, options = list(lengthChange = FALSE), server = FALSE
      )
    }
  )
}