Customize data table search by implementing regex logic

Question

I have a data table where I establish a customized search logic (with AND and OR), by translating the search string into a regex expression using the function searchStringToRegexString. The following search strings are then converted to:

'term1 AND term2' => (?=.*term1)(?=.*term2) 

'term1 OR term2' => (?=.*term1)|(?=.*term2) 

'term1 term2' => (?=.*term1 term2)

'term1 term2 OR term3 AND term4' => (?=.*term1 term2)|(?=.*term3)(?=.*term4)

While the customized search works as intended for the column searches, I encounter the following issues with the global search:

(A.) The global search for "ford OR dog" works fine. However, "ma AND cat" shows zero results, which is incorrect.

(B.) Additionally, when switching to column search, e.g., to the column 'pet', and entering a search "cat", the displayed global search string changes from "ma AND cat" to "(?=.*ma)(?=.*cat)".

To resolve the problem, I unbound the assigned event handlers. However, the global search does not seem to interpret the regex string correctly.

If someone could please give me some advice on how to resolve the regex search for the global search.

library(DT)
library(shiny)


ui = fluidPage(
   shiny::tags$script("
      function searchStringToRegexString(search_str) {
         let term_before = false;
         let regex_str = '(?=.*';
         let parts = search_str.trim().split(' ');  // array with terms split by spaces
         for (let part of parts) {
            if ((part === 'AND') || (part === 'OR')) {
               regex_str += (part === 'AND') ? ')(?=.*' : ')|(?=.*';  // 'term1 AND term2' => (?=.*term1)(?=.*term2), 'term1 OR term2' => (?=.*term1)|(?=.*term2)
               term_before = false;
            } else {
               regex_str += (term_before === true) ? (' ' + part) : part;  // 'term1 term2' => (?=.*term1 term2)
               term_before = true;
            }
         }
         regex_str += ')';
         regex_str = regex_str.replace('|(?=.*)', '').replace('(?=.*)', '');  // remove empty ANDs and ORs
         console.log('search_str:', search_str, ', regex_str:', regex_str);
         return regex_str;
      }
   "),
   fluidRow(
      column(width = 12,
             DTOutput("dtable")
      )
   )
)


server = function(input, output, session) {
   data = data.frame(
      car = c("Mazda", "Mazda RX4", "Mazda RX4 Wag", "Ford", "Mercedes"),
      pet = c("dog", "dog", "cat", "cat", "cat")
   )

   output$dtable = renderDT({
      datatable(
         data,
         filter = list(position = "top", clear = TRUE, plain = FALSE),
         options = list(
            searchDelay = 1500,
            dom = "ft",
            columnDefs = list(list(targets = "_all", className = "dt-center")),
            fixedHeader = FALSE,
            initComplete = JS("
               function(settings) {
                  let glo_search_handler = $('.dataTables_filter input');
                  let instance = settings.oInstance;
                  let col_search_handler = instance.parent().find('.form-group input');
                  let table = instance.api();

                  col_search_handler.unbind();  // unbind the default datatable search handlers
                  glo_search_handler.unbind();

                  glo_search_handler.on('keyup.globalSearch', function(e) {  // global search handler
                     e.preventDefault(); // prevent the default form submit behavior
                     let glo_search_str = $(this).val().trim();
                     if (glo_search_str === '') {
                        table.search('').draw();
                     } else {
                        let glo_regex_str = searchStringToRegexString(glo_search_str);
                        table.search(glo_regex_str, true, false, true).draw();  // search term, regex, smart, caseInsensitive
                     }
                  });

                  col_search_handler.on('keyup.columnSearch', function(e) {  // custom column search handler
                     e.preventDefault(); // prevent the default form submit behavior
                     let col_search_str = $(this).val().trim();
                     let index = 1 + col_search_handler.index(this);
                     let column = table.column(index);
                     if (col_search_str === '') {
                        column.search('').draw();
                     } else {
                        let col_regex_str = searchStringToRegexString(col_search_str);
                        column.search(col_regex_str, true, false, true).draw();
                     }
                  });
               }
            ")
         )
      )
   }, server = TRUE)
}

shinyApp(ui = ui, server = server)

Answer 1

While it yields a technically correct regex when you translate your 'and' string to (?=.*term1)(?=.*term2), it won't work with the implementation of the server side DT search. However, it will work if you have smart search enabled (automatically in your example) and insert a space between the brackets:

(?=.*term1) (?=.*term2)

The other problem with the changing global search string has to do with the draw() and can be circumvented by temporarily saving the value and reassigning it after draw():

let currentGlobalSearchString = $('.dataTables_filter input').val();
column.search(col_regex_str, true, false, true).draw();
$('.dataTables_filter input').val(currentGlobalSearchString);