Datatable Search Function Does Not Filter The Table On Multiple Values

Jasmine_Badrih

Hi!

I'm building an excel filter with datatable. I have collected the values of the table rows and pushed it into the filter dropdown.

Datatable code:

datatable = $("#datatable").DataTable({
      searching: true,
      columns: [
        { title: "itemID", defaultContent: "" },
        { title: "Name", defaultContent: "" },
        { title: "Age", defaultContent: "" },
        { title: "Country", defaultContent: "" },
        { title: "E-mail", defaultContent: "" },
        { title: "Address", defaultContent: "" },
        { title: "Fax", defaultContent: "" },
        { title: "Employee ID", defaultContent: "" },
        { title: "Occupation", defaultContent: "" },
        { title: "Phone", defaultContent: "" },
        { title: "", defaultContent: "" }
      ],
      // Initialize the datatable header.
      initComplete: function () {
        var table = this.api();
        var headers = $(this[0]).find("thead tr").children();
        // For each header, append an input so it can be used for filtering the table.
        $(headers).each(
          column =>
            (table
              .column(column)
              // Append the filter div and the arrow down icon.
              .header().innerHTML += `<i class="arrow down"></i><div class="filter"></div>`)
        );
      }
    });

On click of the arrow to open the dropdown filter:

var thObject = $(this).closest("th");
var filterGrid = $(thObject).find(".filter");
filterGrid.empty();
filterGrid.append(
    '<div><input id="search" type="text" placeholder="Search"></div><div><input id="all" type="checkbox" checked>Select All</div>'
  );
//   Loop through all the datatable rows.
  datatable.rows().every(function (rowIdx, tableLoop, rowLoop) {
    // Get current td value of this column.
    var currentTd = this.data()[$(thObject).index()];
    // Get the tr tag of this row.
    var row = this.table().rows().context[0].aoData[rowIdx].nTr;
    var div = document.createElement("div");
    // filterValues is a local variable to store all the filter values and to avoid duplication.
    if (filterValues.indexOf(currentTd) == -1) {
      div.classList.add("grid-item");
      // if the row is visible, then the checkbox is checked.
      var str = $(row).is(":visible") ? "checked" : "";
      // For this div, append an input field of type checkbox, set its attribute to "str" (checked or not), with the value of the td.
      div.innerHTML = '<input type="checkbox" ' + str + " >" + currentTd;
      // filterGrid is a local variable, which is the div of the filter in the header.
      filterGrid.append(div);
      filterValues.push(currentTd);
    }
  });
  filterGrid.append(
    '<div><input id="close" type="button" value="Close"/><input id="ok" type="button" value="Ok"/></div>'
  );
filterGrid.show();

Here is the code on click on the okay button after selecting values to filter the datatable:

var $okBtn = filterGrid.find("#ok");
var checkedValues = [];
  $okBtn.click(function () {
    // checkedValues is a local variable to store only the checkboxes that has been checked from the dropdown fiter.
    // Empty the array.
    checkedValues = [];
    // filterGrid is the dropdown jquery object.
    filterGrid
      // find all the checked checkboxes in the filterGrid.
      // ".grid-item" is a class of div that contains a checkbox and a td's value of the current datatable column. 
      .find(".grid-item input[type='checkbox']:checked")
      // The result is an array.
      // For each index in this array, push it to checkedValues array (store the values).
      .each(function (index, checkbox) {
        checkedValues.push($(checkbox).parent().text());
      });

    // Show relative data in one page.
    datatable
      // In datatable, search in this specific column by the index of the thObject (the header element) to search in the right tds.
      .column($(thObject).index())
      // Call search function (datatable built in function) to search in the table for all the selected values.
      // Search function allows strings, so call the checkedValues array, join all the values together(exmp. "name1|name2|name3") to allow multi search.
      // Draw the new table.
      // "^"-   Start of string or start of line depending on multiline mode.
      // "$"-   End of string or end of line.
      .search("^(" + checkedValues.join("|") + ")$", true, false, true)
      .draw();

    // Hide the dropdown filter.
    filterGrid.hide();
    return false;
  });

After filtering the table couple of times, it stops filtering the table. I'm pretty sure that it is something wrong in the search datatable function, But I can't understand what is the exact issue (there are no error messages).

I have posted the question on stack overflow.

I would be glad if someone can help.

Thank you!

kthorngren

There is nothing obvious from the code snippets. Please post a link to your page or a running test case showing the search issue. This will allow us to help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

Jasmine_Badrih

Hi,
thanks for the quick reply.
Here is a link to my js fiddle.
Jasmine.

kthorngren

I tried your example. Did a few searches and it seems to work. Please provide the steps needed to replicate the issue.

My suggestion is to use two header rows and have the sorting function in one row and the search in another. The -orderCellsTop option controls where the sorting will be. See this example:
http://live.datatables.net/saqozowe/2/edit

Looks like you are loading the yadcf library but a quick scan it doesn't look like its being used. If you are using it and the problem is with yadcf then you will need to get support from the developer here:
https://github.com/vedmack/yadcf

Kevin

Jasmine_Badrih

I don't want to use yadcf.
These are the steps:
On load of the table-
click on the arrow down of the item id column > select couple of values > click "ok" > click on the arrow down of the name column > choose couple of values > click "ok".
result: table is not filtering.
Jasmine.

kthorngren

Seems like the table is filtering to me. But I think maybe I see your question. Follow these steps:

1: Uncheck 8 in the Item ID column
2: Check the name8 option in the Name

Is the problem you are seeing that the row with name8 is not being shown?

The column searches are an AND search so if one column searches filters out a row a column search in another column won't display the row. A search plugin can be created to perform an OR search if this is what you are looking for.

Kevin

Jasmine_Badrih

Now I get it. Thanks alot!
Jasmine.