Editor, multi-column searching, single button search trigger

jacob.steinberger

Using editor server-side processing with multi-column searching https://datatables.net/examples/api/multi_filter.html. Dealing with a rediculous database (~14 billion rows, yes, that's a 'B'). Already made changes to the backend scripts to speed up processing (ie., instead of count(*), use system tables to pull row counts, or look directly at parition data versus table scans, etc). Currently trying to trigger a search via a button click, versus changes in input data.

I've disabled the listeners for change/click/keyup on the input fields, no problem. I've found countless examples of how people have done this, but for whatever reason it doesn't add up. I've tried ...

$.fn.dataTable.ext.buttons.search = {
  text: 'Search',
  action: function ( ) {
  }
};

var table = $('#default').DataTable( {
  buttons: [
    'search'
  ]
} );

But it doesn't seem like $(this) or var table actually makes it to the function call of the button itself. If I try to define the function as a loop through table.columns(), it doesn't understand the draw function, which is why it makes me think I'm expecting it to know something it doesnt.

I've tried a number of other methods that seem to focus on the global filter, which is obviously only one box versus multiple. Can I get a hint?

allan

~14 billion rows, yes, that's a 'B'

Awesome!

Can you show me a more complete version of your code please? I don't see where $(this) would be used, so I'm not sure what the context is.

Are you attempting to set the search values for all columns and the global filter at the same time (when the Search button is pressed)? If so, you need to call search() and column().search() inside that action function.

Allan

jacob.steinberger

I'm looking at the column search API, on top of searchCols for a default search paramater so when we initially load we don't go overboard, and having one column be a pick-list, to restrict what we query due to the size of the database. I've disabled the global search, to try and not complicate myself further.

And learning js as I go ...

I'll use the standard rsyslog+mysql database structure for my examples, as I have it handy for replication.

        var today = new Date();
        var date1 = today.getFullYear()+'-'+(today.getMonth()+1)+'-'+today.getDate();
        $('#default tfoot th').each( function () {
                if ( $(this).text() == 'ReceivedAt' ) {
                        $(this).html( "<?php echo getPartDates(); ?>" );
                } else {
                        var title = $(this).text();
                        $(this).html( '<input type="text" placeholder="Search '+title+'" class="search-input-text"/>' );
                }
        } );

        $.fn.dataTable.ext.buttons.search = {
                text: 'Search',
                action: function ( ) {
                        table.search(this.value).draw();
                }
        };

        var table = $('#default').DataTable( {
                dom: 'CBlrtip',
                ajax: {
                        url: "/includes/datatables/editor/php/table.php",
                        type: "POST"
                },
                serverSide: true,
                processing: true,
                //stateSave: true,
                buttons: [
                        'search',
                        //'colvis',
                        { extend: 'copyHtml5',
                          text: 'Copy Selected',
                          exportOptions: {
                                rows: { selected: true }
                          }
                        },
                        { extend: 'copyHtml5',
                          text: 'Copy All',
                        }
                        ],
                select: true,
                columns: [
                        { "data": "id" },
                        { "data": "ReceivedAt" },
                        { "data": "HostName" },
                        { "data": "SysLogTag" },
                        { "data": "Message" },
                        { "data": "Facility" },
                        { "data": "Priority" },
                        { "data": "ReceivedFrom" }
                ],
                lengthMenu: [
                        [10, 25, 50, 100],
                        [10, 25, 50, 100]
                ],
                searchCols: [
                        null,
                        { sSearch: date1 },
                        null,
                        null,
                        null,
                        null,
                        null,
                        null
                ]
        } );

I'm actually expecting my current pick-list for 'ReceivedAt' to not work, but I was hoping to figure that out after I get the button working.

Back to the button:

        $.fn.dataTable.ext.buttons.search = {
                text: 'Search',
                action: function ( ) {
                        table.search(this.value).draw();
                }
        };

table.draw actually fails, "object doesn't support property or method 'draw'". I assume it's because I'm trying to trigger the table search from inside a DT button definition. I haven't figured out the magic between "embedded" buttons and the table yet.

Tried the simple, but obviously wrong:

        $.fn.dataTable.ext.buttons.search = {
                text: 'Search',
                action: function ( e, dt, node, config) {
                        dt.columns().every( function () {
                                dt.search( this.value ).draw();
                        } );
                }
        };

allan

this.value

That isn't going to give you a value from an input. The API instance doesn't have a value property. Where are you attempting to read the search value from? That is where you should be reading it from and giving to search().

Allan

allan

Actually - if I understand correctly, you are trying to get the search values from the inputs in the footer elements. Is that correct? If so, you would need to loop over them and set the search value for each column (column().search()).

Allan

jacob.steinberger

That's what I'm attempting to do, and end up with a resulting error that the method doesn't support 'draw'. Still trying to figure it out.

allan

That suggests that undefined is being passed into search(). If you pass in undefined it will return a string that is the current search value.

Allan

jacob.steinberger

So I was doing everything right from a DataTables perspective, but I was digging into the wrong documentation. Instead of scowering DataTables API, I should have been looking at jQuery.

Solution was easy once I figured out what objects were being passed around.

allan

Thanks for posting back - good to hear you've got it working.

Allan