KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Individual and Multiple Column search box filter for DataTable #555

Open Andrew Borell opened this topic on on Dec 11, 2018 - 27 comments

Andrew Borell commented on Dec 11, 2018

Is it possible to search per-column or per-columns in addition to the global search, as documented on datatables.net https://datatables.net/reference/event/search ?

Client-side events in koolreport documentation indicates that all the datatables.net events are supported, but its not clear how this is implemented : https://www.koolreport.com/docs/datagrid/datatables/ , if implemented at all.

KoolReport commented on Dec 11, 2018

Hi,

You can do this:

<?php 
DataTables::create(array(
    "name"=>"mytable",
    "clientEvents"=>array(
        "search.dt"=>"function(){
            console.log(mytable.search()); // or you can get the columns.search()
        }"
    )
));
?>
Andrew Borell commented on Dec 12, 2018

I will test this out later. I was hoping to get the search boxes in the individual columns below the column headers in the row that follows so it was present with the sticky header in scroll. Ill post some code if I can make it happen Thanks for the example of usage.

Andrew Borell commented on Dec 14, 2018

What I intended to implement already exists in datatables.net. https://datatables.net/extensions/fixedheader/examples/options/columnFiltering.html

The client side event I am trying to trigger on doesnt seem to do anything though. I could clear everything in the function and only console.log('it works!'), but nothing will show in the console.

DataTables::create(array(
	"dataSource"=>$db_bill_3->dataStore($db_bill_3_data)
		,"name"=>"example"
		,"clientEvents"=>array(
			"init.dt"=>"function(){
				$('#example thead tr').clone(true).appendTo( '#example thead' );
				$('#example thead tr:eq(1) th').each( function (i) {
					var title = $(this).text().trim();
					$(this).html( '<input type=\"text\" placeholder=\"Search '+title+'\" />' );
					$( 'input', this ).on( 'keyup change', function () {
						if ( table.column(i).search() !== this.value ) {
							table
								.column(i)
								.search( this.value )
								.draw();
						}
					} );
				} );
				var table = $('#example').DataTable( {
					orderCellsTop: true,
					fixedHeader: true	
				} );
			}"
		)

KoolReport commented on Dec 14, 2018

You may try to put this code at the end of report view to see how

<script type="text/javascript">
KoolReport.load.onDone(function() {
    // Setup - add a text input to each footer cell
    $('#example thead tr').clone(true).appendTo( '#example thead' );
    $('#example thead tr:eq(1) th').each( function (i) {
        var title = $(this).text();
        $(this).html( '<input type="text" placeholder="Search '+title+'" />' );
 
        $( 'input', this ).on( 'keyup change', function () {
            if ( table.column(i).search() !== this.value ) {
                table
                    .column(i)
                    .search( this.value )
                    .draw();
            }
        } );
    } );
 
    var table = $('#example').DataTable( {
        orderCellsTop: true,
        fixedHeader: true
    } );
} );
</script>
Andrew Borell commented on Dec 14, 2018

Whats weird is I did try this as well, but i get an initialisation error loading the page. Perhaps something to do with ajax, so it might be my own challenge to deal with. That is why I was trying to use the client side init.dt event. Thanks for the suggestion though!

Also fwiw, I added .trim() to the script in my example, which was not in datatables.net title because koolreport throws in a generous amount of white space around the header labels. Not sure why.

Andrew Borell commented on Dec 14, 2018

Fixed it so it works. Note the .trim() and the destroy: true, lines in the code below. The destroy is to handle re-initialization issues. The trim() is to handle the 150 or so spaces around the title. I think the title part looks kinda tacky so ill probably dump that entirely anyway since the input box is directly below the header text.

echo "
<script type=\"text/javascript\">
KoolReport.load.onDone(function() {
    // Setup - add a text input to each footer cell
    $('#example thead tr').clone(true).appendTo( '#example thead' );
    $('#example thead tr:eq(1) th').each( function (i) {
        var title = $(this).text().trim();
        $(this).html( '<input type=\"text\" placeholder=\"Search '+title+'\" />' );
 
        $( 'input', this ).on( 'keyup change', function () {
            if ( table.column(i).search() !== this.value ) {
                table
                    .column(i)
                    .search( this.value )
                    .draw();
            }
        } );
    } );
 
    var table = $('#example').DataTable( {
		destroy: true,
		orderCellsTop: true,
        fixedHeader: true
    } );
} );
</script>
";	
Andrew Borell commented on Dec 14, 2018

You have to include fontawesome 4.7 to do this, but I find this a little more modern than the example from datatables.net using the title in the search field.

        $(this).html( '<input type=\"text\" placeholder=\"&#xf0b0;\" style=\"font-family:Arial, FontAwesome\" />' );

KoolReport commented on Dec 14, 2018

Great finding for the destroy option, I personally do not aware of that. Thanks!

About the FontAwesome font put the style, what does it help?

Andrew Borell commented on Dec 14, 2018

instead of placing the title in each searchbox, I put a FILTER icon. We know what the field is because its directly below the text. The repetition was unecessary and it dresses the input box placeholder a little bit so it doesnt just look like a blank box.

also important to know that the CSS needs to exist or the input boxes will not adjust.

e.g.)

thead input {
        width: 100%;
    }
KoolReport commented on Dec 14, 2018

Oh I see, I did not notice the placeholder there.

Andrew Borell commented on Dec 14, 2018

This is the resulting view :

KoolReport commented on Dec 14, 2018

Awesome :)

Andrew Borell commented on Dec 14, 2018

If you guys are looking for a Kool suggestion that I couldnt get working properly, I was thinking popovers with controls would be kinda cool here. Say we consider the billing id field in my screenshot above. In a popover or even a modal for semi-hidden fields as parameters there could be controls based on the data type. If the datatype is number for instance, then greater than, less than, range, or even a multiselect combo box. For datetime there could be datepicker options with equal to, not equal to or between, for string there could be equal to, not equal to and multi-select list.

I couldnt get popovers to work in short time ( maybe I make another attempt later ), but that was one of my ideas to submit parameters that narrow the scope of the result set. I think my issue with popovers was a combination of inheritance and event bubbling preventing the popover from appearing in the foreground and part of the problem is related to the sorting. I could use a span outside the clickable element and add onclick="event.stopPropagation()" but I think that stopped the popover from showing up as well.

The filters as we see them in my screenshot could also be used so long as the input boxes are given a name to hand off to the get/post methods, but I see them as "fuzzy query" items in the scheme of things. Not very precise parameters and using "like" on so many conditions can drag on queries depending on indexing.

The reason I suggest these types of filters as popover and modal is because I dont like cluttering the top of the page with filters. Perhaps a toggle on hidden div would be more convenient to some. And yes, I know a suggestion should probably be in its own thread but I chose to add it here since we had such good dialog on this topic already.

KoolReport commented on Dec 15, 2018

That's a great suggestion of your. I will record for dev.team for future development. Our team has done similar things on grid as well. Here are two:

  1. KoolGrid basic filter - Although in this example, there is no date so there is no datepicker however, it is possible for date picker to work on grid.
  2. FineUI Grid - A our discontinued project on web component. However we have a good base to implement this feature.

Best regards

MarkoS commented on Dec 31, 2020

Hi guys, this is quiet old post but I found it interesting for solution I am looking for. Is there a way to add one input text field somewhere outside of table and use it to search only specified columns? (I am already using built-in search box on the top right corner for filtering on only one specific column)

MarkoS commented on Dec 31, 2020

UPDATE: I've found solution:

Input field: <input type='text' value='' id='pretrazivac' placeholder='Pretraga...' class='unos-pretraga' name='proba' style='padding:5px;' />

JS code:

  $( '#pretrazivac').on( 'keyup change', function () {
        if ( DataTable1.column(1).search() !== this.value ) {
        console.log("Unio sam neki tekst, evo ga:" + this.value)
        DataTable1
            .column(1)
            .search( this.value )
            .draw();
        }
     });

Just in case if someone is looking for same solution.

Complete solution for injecting search input box into DataTables wrapper:

KoolReport.load.onDone(function(){ 
    
    function addInput() {

    var addList = document.getElementById('DataTable1_wrapper');  

    var text = document.createElement('div');
    text.id = 'additem_';
    text.innerHTML = "<input type='text' value='' id='pretrazivac' placeholder='Pretraga...' class='unos-pretraga' name='proba' style='padding:5px;' />";

    addList.appendChild(text);
    }

    // run function each time report loads up. it can be triggered any time on click etc.
    addInput();
    
 
    $( '#pretrazivac').on( 'keyup change', function () {
        if ( DataTable1.column(1).search() !== this.value ) {
        console.log("Unio sam neki tekst, evo ga:" + this.value)
        DataTable1
            .column(1)
            .search( this.value )
            .draw();
        }
    });
          
});
MarkoS commented on Dec 31, 2020

The only last thig is, how to search only on columns with indexes I set, like (2,3,4) ? I did try with array and forEach but it does not work as expected.

David Winterburn commented on Jan 4, 2021

Hi Marko,

To search on multiple columns please try command like this:

    table.column(1).search(searchTerm1).column(2).search(searchTerm2).draw(); 

Let us know if we understand your question correctly. Thanks!

Jeremy Gray commented on Nov 4, 2021

Andrew are you still around? I am running into the "initialisation" error, even with the destroy fix. Is it possible for you to share your report.view.php?

DataTables warning: table id=example - Cannot reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3
Jeremy Gray commented on Nov 4, 2021

Also I have to comment out

"clientEvents"=>array(
                        "init.dt"=>"function(){
//                              $('#example thead tr').clone(true).appendTo( '#example thead' );
                                $('#example thead tr:eq(1) th').each( function (i) {

otherwise I get a duplicate column header.

Andrew Borell commented on Nov 4, 2021

Jeremy, Your second comment fell into line with what I was wondering. Make sure the id, #name or .class you are referencing in the query is not duplicated in the page.

Jeremy Gray commented on Nov 4, 2021

Andrew, I am basically using example from your first post + your last post with the echoed php <script> placed at end of the page. 2018 was a while ago.. I wonder if Koolreports (and bundled DataTables?) changed their syntax required for destroy to work.

Jeremy Gray commented on Nov 4, 2021

andrew, oops.. I spoke too soon there. Still broken after changing to "exampley"

Jeremy Gray commented on Nov 5, 2021

in my report.view.php I have...


   <?php
    DataTables::create(array(
        "dataSource"=>$this->dataStore("result"),
        "name"=>"exampley",

"clientEvents"=>array(
                        "init.dt"=>"function(){
//                              $('#exampley thead tr').clone(true).appendTo( '#exampley thead' );
                                $('#exampley thead tr:eq(1) th').each( function (i) {
                                        var title = $(this).text().trim();
                                        $(this).html( '<input type=\"text\" placeholder=\"Search '+title+'\" />' );
                                        $( 'input', this ).on( 'keyup change', function () {
                                                if ( table.column(i).search() !== this.value ) {
                                                        table
                                                                .column(i)
                                                                .search( this.value )
                                                                .draw();
                                                }
                                        } );
                                } );
                                var table = $('#exampley').DataTable( {
                                        orderCellsTop: true,
                                        fixedHeader: true
                                } );
                        }"
                ),


(and so on... just the rest of the parameters for datatables)

and then at bottom....


   </body>
</html>

<?php echo "
<script type=\"text/javascript\">
KoolReport.load.onDone(function() {
    // Setup - add a text input to each footer cell
    $('#exampley thead tr').clone(true).appendTo( '#exampley thead' );
    $('#exampley thead tr:eq(1) th').each( function (i) {
        var title = $(this).text().trim();
        $(this).html( '<input type=\"text\" placeholder=\"Search '+title+'\" />' );

        $( 'input', this ).on( 'keyup change', function () {
            if ( table.column(i).search() !== this.value ) {
                table
                    .column(i)
                    .search( this.value )
                    .draw();
            }
        } );
    } );

    var table = $('#exampley').DataTable( {
                destroy: true,
                orderCellsTop: true,
        fixedHeader: true
    } );
} );
</script>
";
?>


Andrew Borell commented on Nov 5, 2021

Jeremy, congrats! if you find these types of issues setting you back on precious dev time you may consider a reputable browser extension that offers html syntax validation. Which to use I cannot say, but im sure you can find one you like if you are inclined. I have used them in the past because I ran into a simple issue one time where I was using an attribute that doesn't exist on an li tag that messed everything up and it cost me a half day of dev time before I realized it. thx -d

Checking on your new post. Stand by.

Jeremy Gray commented on Nov 5, 2021

other than "initialisation" issue and needing to comment out that line, it seems to work great. Exactly what I want. Hopefully can figure out the problem.

Andrew Borell commented on Nov 5, 2021

I feel like I recall having a secondary issue when adding paging. Are you adding a paging property to the table? Also what version of JS, and Koolreports and any other deps if I can find a moment to test this.
thx -d

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
wiki
help needed
suggestion

DataGrid