I've got a Datatables table which retrieves data about classes registered in a school term on an change event. On the footer section, I have coded multi-select dropdowns to filter the data specific to every term on dropdown filter change. The following code block is used to implement the dropdown filters, as is explained in the official website.
this.api().columns([1,3,4,5,6]).every(function () {
var column = this;
var select = $('<select ><option value=""></option></select>')
.appendTo($(column.footer()).empty())
.on('change', function () {
var val = $.fn.dataTable.util.escapeRegex(
$(this).val()
);
column
.search(val ? '^' val '$' : '', true, false)
.draw();
});
column.data().unique().sort().each(function (d, j) {
select.append('<option value="' d '">' d '</option>')
})
})
At first I had this part in initcomplete: block. There happened to be a big drawback yet. The setback was that the dropdown was populated just the first time the table loaded. Hence, when the following change event was triggered, the dropdown filters did not repopulate to fit the the new data loaded.
$('#tval').on('change', function() {
tid = $("#tval option:selected").val();
allocatedtable.ajax.reload(null, false);
});
This, in fact, is what is expected; As obvious in the above code block, just the ajax call is reloaded, and the filters are not retrieved again. I read somewhere, though, to obviate the problem the trick will be to transfer the dropdown filter code block under ajax 'complete' event. It was excellent. It worked like charm, and the dropdown was filtered with the unique data in each column . Hence, the final coding was like the following:
$('#tval).on('change', function() {
allocatedtable.ajax.reload(null, false);
});
var allocatedtable = $('#tblclasses').DataTable({
ajax: {
method: 'POST',
url: 'data.php',
dataType: 'json',
data: {"term": termid = $("#tval option:selected").val()},
complete:function() {
$('#tblclasses').dataTable().api().columns([1,3,4,5,6]).every(function () {
var column = this;
var select = $('<select ><option value=""></option></select>')
.appendTo($(column.footer()).empty())
.on('change', function () {
var val = $.fn.dataTable.util.escapeRegex(
$(this).val()
);
column
.search(val ? '^' val '$' : '', true, false)
.draw();
});
column.data().unique().sort().each(function (d, j) {
select.append('<option value="' d '">' d '</option>')
});
});
}
},
columns: [
{"data": "title"},
{"data": "gender"},
],
fixedColumns: true,
"bDestroy": true,
});This works great, but still I can detect some anomalies in the outcome. Suppose you load the data for the first school term. Under the column Gender, you have 'male', 'female', and 'coed' in the data response. Under this condition, suppose we choose 'male' from gender column dropdown. Datatables greatly filters the data. There is still no problem here. Now suppose we want to change the school term: here $('#tval').on('change', function() {...} is called. Please keep in mind that before changing to a new term, we had chosen 'male' under gender column. The problem occurs here. When I change to a new term, the datatables have the last filter in its memory somehow :(( In other words, datatables gets the data and filters it by the last chosen filter in the dropdown the moment I change to another term. This is not what I expect. When changing the terms, I need to get the data without any filters. Quite interestingly, when I change the term, the dropdowns are blank, yet it applies the last chosen filter in its queries without any dropdown filter selected. For example, if Term A has classes with gender type 'male' and 'coed', and you filter the data to see just the classes held for males, just then if you change to Term B, if Term B does not have any classes with gender 'male', datatables does not show any data, even though in reality there is some data for classes with type gender 'female' for instance in that term. The data is filtered, even there is nothing selected in the filter dropdown when term is changed.
Hope I have been clear in my explanation, and hope to get a solution from you.
- At first I had the multi filter dropdown under 'initcomplete'.
- Due to the mentioned drawback, I moved it to the
ajax complete event. - Getting no results, I changed the coding a little bit to follow the suggestions expressed here; but again to no use.
- I have even transferred the filter dropdown code to the 'fncallback', but to no use.
Many thanks in advance.
CodePudding user response：
I solved the problem by sticking to the explanations offered here. In fact, I needed to change the code structure totally. The only thing, I needed to do so that the post here will fit my case was to add table.search( '' ).columns().search( '' );
As a result, the whole new code is the following:
function fetchData() {
$.ajax({
method: 'POST',
url: 'someurl.php',
data: {"acquire": true,
"term": $("#tval option:selected").val()
},
context: document.body
}).done(function( data ) {
var table = $('#tbl').DataTable();
table.clear();
table.search( '' ).columns().search( '' );
table.rows.add(data);
buildSelectLists();
table.draw();
});
}
function buildSelectLists() {
var api = $('#tbl').dataTable().api();
api.columns().each(function () {
$('input', this.footer()).on('keyup change clear', function () {
api.draw();
});
})
api.columns([1,3,4,5,6]).every(function () {
var column = this;
var select = $('<select ><option value=""></option></select>')
.appendTo($(column.footer()).empty())
.on('change', function () {
var val = $.fn.dataTable.util.escapeRegex(
$(this).val()
);
column
.search(val ? '^' val '$' : '', true, false)
.draw();
});
column.data().unique().sort().each(function (d, j) {
select.append('<option value="' d '">' d '</option>')
});
});
}
$(document).ready(function () {
$('#tval').on('change', function() {
fetchData();
});
$('#tbl').DataTable({
columns: [
{"data": "title"},
{"data": "gender"},
],
"bDestroy": true,
"initComplete": function() {
fetchData();
}
});
});