How to perform literal search in Shiny DataTable column filter?

Question

This one has stumped me, and although I've looked into DataTables specific solutions (jQuery DataTables - Filter column by exact match), I haven't yet identified how to achieve a JavaScript solution.

I need a literal match for column filters in my DataTable. In the example that follows, that means returning just Mazda RX4 and not Mazda RX4 Wag. Ideally this would apply to any DT search field. Here's an MRE with the formatting I'm using for this specific table:

library(shiny)
library(DT)

ui <- fluidPage(

    fluidRow(
      dataTableOutput("dt_tab")
    )
)

server <- function(input, output) {

  output$dt_tab <- DT::renderDataTable({
    
    mtcars$car <- rownames(mtcars)

    DT::datatable(mtcars,
                  style = "bootstrap",
                  class = "display compact stripe cell-border wrap",
                  selection = "single",
                  callback=DT::JS('$(\'div.has-feedback input[type="search"]\').attr( "placeholder", "Search" )'),
                  rownames = FALSE,
                  filter = 'top',
                  extensions = 'Scroller',
                  options=list(columnDefs = list(list(visible=FALSE, targets=c(0))),
                               search = list(regex = FALSE, caseInsensitive = FALSE),
                               lengthChange = F,
                               scrollX = T,
                               scrollY = '60vh',
                               scroller = T,
                               paging = T,
                               initComplete  = JS(sprintf('function() {
                                   this.api().table().scroller.toPosition(%s);
                                   }', 0)),
                               dom = '<"top"if>rt<"bottom"lp><"clear">',
                               deferRender = TRUE,
                               language = list(searchPlaceholder = "across all columns")
                  )
    )
    
  }, server = TRUE)
  
}

shinyApp(ui = ui, server = server)

I know that a similar solution was posted here: Search Exact Match R datatable, but I can't seem to manage the right JavaScript.

Thanks for any help you can provide.

Answer 1

Is it ok with the search builder?

library(DT)

dat <- data.frame(
  car = c("Mazda", "Mazda RX4", "Mazda RX4 Wag", "Ford", "Mercedes"),
  day = Sys.Date() + 0:4
)

datatable(
  dat,
  extensions = c("SearchBuilder", "DateTime"),
  options = list(
    dom = "Qlfrtip",
    searchBuilder = TRUE
  )
)

Note: usage in Shiny

In order to use the search builder in a Shiny app, you have to set the option server = FALSE in the renderDT function. Server-side searching is not supported.

---

EDIT

Here is a way without the search builder. It has an inconvenient: you have to specify the index of the column in which you want to search. That's because DataTables supports exact regex search only on a column basis.

library(DT)

dat <- data.frame(
  car = c("Mazda", "Mazda RX4", "Mazda RX4 Wag", "Ford", "Mercedes"),
  day = Sys.Date() + 0:4
)

js <- c(
  "function(settings){",
  "  var instance = settings.oInstance;",
  "  var table = instance.api();",
  "  var input = instance.parent().find('.dataTables_filter input');",
  "  input.off('keyup search input').on('keyup', function(){",
  "    var keyword = '^' + input.val() + '$';",
  "    // we search in column 1",
  "    table.column(1).search(keyword, true, false).draw();",
  "  });",
  "}"
)

datatable(
  dat,
  options = list(initComplete = JS(js))
)

---

EDIT

Finally, here is how to search an exact match in the column-wise filter boxes:

library(DT)

dat <- data.frame(
  car = c("Mazda", "Mazda RX4", "Mazda RX4 Wag", "Ford", "Mercedes"),
  pet = c("dog", "dog", "cat", "cat", "cat"),
  day = Sys.Date() + 0:4
)

js <- c(
  "function(settings){",
  "  var instance = settings.oInstance;",
  "  var table = instance.api();",
  "  var $inputs = instance.parent().find('.form-group input');",
  "  $inputs.off('keyup search input').on('keyup', function(){",
  "    var keyword = '^' + $(this).val() + '$';",
  "    var index = 1 + $inputs.index(this);", # add 1 for the rownames column
  "    table.column(index).search(keyword, true, false).draw();",
  "  });",
  "}"
)

datatable(
  dat, filter = "top", 
  options = list(initComplete = JS(js))
)

---

EDIT

If you delete the search with the previous solution, then it is not cancelled, you have to click the cross and this is not convenient. Here is a solution which doesn't have this inconvenient:

js <- c(
  "function(settings) {",
  "  var instance = settings.oInstance;",
  "  var table = instance.api();",
  "  var $inputs = instance.parent().find('.form-group input');",
  "  $inputs.off('keyup search input').on('keyup', function() {",
  "    var value = $(this).val();",
  "    if(value !== '') {",
  "      var keyword = '^' + value + '$';",
  "      var index = 1 + $inputs.index(this);", # add one if row names
  "      var column = table.column(index);",
  "      column.search(keyword, true, false).draw();",
  "    }",
  "  });",
  "}"
)