Hello, I am using datagrid datatables which uses client side pagination. I have to add dropdown filter for a datetime column like LAST 12 MONTHS, LAST 6 MONTHS and so on. Since i already have all the data , is there a way to bind the dropdown event , which on change searches within the datatable and only displays those rows for which it holds true? There is no point of sending an AJAX Request to display selective data when you already have the full data. How can i implement it in koolreport?
KoolReport's Forum
Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines
DataTable regular expression (regex) search/filter input #1364
Hi,
For client side range filtering of DataTables, please check this link:
https://datatables.net/examples/plug-ins/range_filtering.html
The DataTables object's name is the name you use when creating datagrid/DataTables widget. For example:
<?php
\koolphp\datagrid\DataTables::create(array(
"name" => "datatables1",
...
));
?>
<script>
...
$('#min, #max').keyup( function() {
datatables1.draw();
} );
</script>
Please try this and let us know if you have any difficulty. Thanks!
Hi David,
I am looking for similar filtering as @ankit described. I am using example from here https://datatables.net/examples/plug-ins/range_filtering.html which, when line below is used I am getting two search fields, table layout is messed up a bit (attached image)
var table = $('#DataTable1').DataTable();
I want to filter column "Grupa" and so far only way is manipulating input search field. Code below does fill the search input field(s) but filtered table does not return any matched data. Here is full example:
$(document).ready(function() {
var table = $('#DataTable1').DataTable();
// Event listener to the two range filtering inputs to redraw on input
$('#nadgrupa').change( function() {
var odabrano = $('#nadgrupa').val();
$("#DataTable1_filter > label").find('input').val(odabrano);
table.draw();
} );
} );
I would like to use drop down to filter only one column, where search can be used as normal for all columns. This what I am trying to make with example above is work-around.
BTW: #nadgrupa is ID of drop down select for different Groups of products.
Hi Marko,
If you are using datagrid's DataTables, there's no need to init DataTables on client again like this:
var table = $('#DataTable1').DataTable();
...
table.draw();
Instead you could access the DataTables object directly:
DataTables1.draw();
Besides, did you extend the search function like in the link?
$.fn.dataTable.ext.search.push(
function( settings, data, dataIndex ) {
...
{
return true;
}
return false;
}
);
Hi David, thanks for quick reply. As you pointed out, DataTables1.draw(); is enough without second init. of the client.
Re extending the search, I could not make it work. My code looks like this:
<script type="text/javascript">
// $.fn.dataTable.ext.search.push(
// function( settings, data, dataIndex ) {
// var min = $('#nadgrupa').val();
// var max = $('#nadgrupa').val();
// var age = parseFloat( data[1] ) || 0; // use data for the age column
// if ( ( isNaN( min ) && isNaN( max ) ) ||
// ( isNaN( min ) && age <= max ) ||
// ( min <= age && isNaN( max ) ) ||
// ( min <= age && age <= max ) )
// {
// return true;
// }
// return true;
// }
// );
$(document).ready(function() {
$('#nadgrupa').change( function() {
var odabrano = $('#nadgrupa').val();
DataTable1.search(new RegExp("^"+this.value,"i"));
$("#DataTable1_filter > label").find('input').focus();
$("#DataTable1_filter > label").find('input').val(odabrano);
// $("#DataTable1_filter > label").find('input').off();
DataTable1.draw();
} );
} );
</script>
I do not really understand what $.fn.dataTable.ext.search.push does, returns false or true for what exactly.. I've managed to narrow the search in only one column by setting columnDefs searchable: false for all other columns. I would like to filter by first letter, not by last or any different match. I just need first letter. The search on image below should not give any result.
Hi Marko,
By default DataTables' search function set regex searching = false:
https://datatables.net/reference/api/search()#Types
So I think you could change it a bit to enable regex searching:
DataTable1.search(new RegExp("^"+this.value,"i"), true); //2nd parameter disable/enable regex search
Let us know if it works for you. Thanks!
Hi David, thanks for reply. It did not work for me :/ I have tried regex before and no difference what so ever.
I am thinking about workaround by adding one more column where "Grupa" data will be trimmed to only one (first) letter. Than I just have to hide it somehow :P
Not perfect but possible solution.
My initial plan is to have sort from Drop down selection menu and not use Search input field / filter.
Hi Sebastian, still no difference. I am only guessing that I didn't place the command in right place.
Here is example I am running now:
$(document).ready(function() {
$('#nadgrupa').change( function() {
var odabrano = $('#nadgrupa').val();
$("#DataTable1_filter > label").find('input').focus();
$("#DataTable1_filter > label").find('input').val(odabrano);
DataTable1.search(new RegExp("^"+odabrano,"i"), true);
DataTable1.draw();
} );
} );
Marko, after thorough experiments with DataTables' regex search I find out that the start of string operator "^" only works with individual column search, DataTables.columns(...).search(), and not global search, DataTables.search().
Thus, to make your regex pattern works please try this:
DataTable1.column(1).search("^"+odabrano, true, false); //assuming you want to search on the 2nd column. 2nd param=true means enable regex search, 3rd one=false means disable smart search which could conflict with regex
This took me several hours to discover as no docs or pages mention this exact detail. Hope it helps your case. Cheers,
Hi Sebastiam, I am glad to inform you that this works! Works exactly as I wanted. My final code looks like this, in case someone needs the same thing:
HTML
<select id="nadgrupa" name="poslovnica" class="form-control" deluminate_imagetype="gif">
<option value="sve" selected="">Sve nadgrupe</option>
<option value="0">O-Ostalo</option>
<option value="A">A-Modni asesoari</option>
<option value="B">B-Tekstil i uredski materijal</option>
<option value="D">D-Dekorativa</option>
<option value="F">F-Farmacija</option>
<option value="H">H-Higijena</option>
<option value="K">K-Sve za kosu</option>
<option value="M">M-Majka i djete</option>
<option value="S">S-Sve za dom</option>
<option value="T">T-Sve za tijelo</option>
<option value="P">P-Parfemi</option>
<option value="L">L-Selektivna kozmetika</option>
</select>
And JS:
$(document).ready(function() {
$('#nadgrupa').change( function() {
var odabrano = $('#nadgrupa').val();
// ## Fill the search filed with first letter selected in drop down list
// $("#DataTable1_filter > label").find('input').focus();
// $("#DataTable1_filter > label").find('input').val(odabrano);
DataTable1.column(1).search("^"+odabrano, true, false); //assuming you want to search on the 2nd column. 2nd param=true means enable regex search, 3rd one=false means disable smart search which could conflict with regex
DataTable1.draw();
} );
} );
Only one IF statement is required to fix default value for all groups.
Thank you so much! We all learned something new today :)
Build Your Excellent Data Report
Let KoolReport help you to make great reports. It's free & open-source released under MIT license.
Download KoolReport View demo