jQuery : Custom filter buttons for DataTables

Hi

I'm using DataTables to display some tabular data
Within the data I have a code column which I need to filter by

I can Hide the filtered rows using jQuery I'm not certain this is the best approach as it relies on the whole table being visible

I've made a JS fiddle to illustrate what I'm doing

I've simulated the query form by creating some static JSON and hosting in a myjson bin
LVL 1
trevor1940Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian DayhoffSenior Full Stack DeveloperCommented:
The easiest way to accomplish this is to put a css class or data attribute on the columns so you have a distinct selector for each column that can be directly accessed from the DOM, regardless of visibility. So you want a row structure something like this:

<tr>
          <td class="id">id</td>
          <td class="city">city</td>
          <td class="long">long</td>
          <td class="id">lat</td>
          <td class="country_code">country_code</td>
          <td class="code">code</td>
          <td class="url">url</td>
          <td class="description">description</td>
        </tr>

Open in new window


And then you can do something like this with jQuery:

jQuery('tr > td.code').on('change', function ( event ) // This creates an independent listener for each table row that fires immediately when a change occurs, scoped to the individual cell that changed.
{
    var value = jQuery(this).text(); // This gets you the current cell value.
    var row = jQuery(this).parent(); // This gets you the row tr element, so you can access any other cell by using row.children('.classname');
    // ... do stuff with the value. Ajax/ calling another function, etc.
});

Open in new window


You can do pretty much the same for any other cell in the table that you need to track and bind events to on the page also.

To avoid collisions with your table head row, it is suggested to use th for the first row in place of tr, or alternately use a thead element for the header row and a tbody element for the data.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trevor1940Author Commented:
Hi Brian

First the "td" for the table header was an oversize of course it should be "th"

When you use DataTables plugin there are various options you can set 1 is "paging"  if set to  true only 10, Default number, rows are shown my custom filter button only works on the first page unless paging: false,

I'm unclear how your  suggestion of a change event will work!

Once the "Query" is made and the table loads where is the change

There is an item on custom-filter-buttons This uses the search API I'm guessing I could do a Negative search
Brian DayhoffSenior Full Stack DeveloperCommented:
There are a number of ways to accomplish this with pagination, but most will incur an ajax or rest call back to the server the data originated from, followed by a draw() command to the dataTable.

So with respect to pagination, say you want ten rows visible per page, and the other rows do not exist hidden in the dom. You have a couple of tasks to do to accomplish this:

First, the datatable itself should be initialized inside of a closure that provides a baseline scope, and acts as a component controller for it. You will also want to use this scope to enact any temporary data vars that exist outside the scope of the dataTable, such as a raw server response prior to redrawing the table, and any jQuery or querySelector objects you use to mark ui buttons the user interacts with. A button click should defer back to a handler method in this scope, which fires off an ajax call back to the server. The success function should trigger another function in this scope, which receives the response and either passes the data directly to dataTables to handle, or alternately creates ten new hidden rows, deletes the previous ten, and then calls redraw against the dataTable to make the component recognize them. Both have ups and downs. The first method can be visually glitchy, as any css transitions or effects bound to the table will fire in transition and may show duplicate rows momentarily, and also your selector id and class bindings maycnot update correctly. The latter method will present all of the data correctly and redraw it all in one consistent chunk, but requires writing your own callback logic.

The other option is to use the data binding methods provided with dataTables, but unless you have a very performant rest layer on the server it can be troublesome, because that approach fires off a ton of tiny little ajax requests, and if your server does not have pretty rock solid backend optimization, you will incur a pretty heavy load on the backend and database.

So you want something basically like this:

/**
 * This represents a very basic table component for correctly leveraging ajax to update a table, and maintaining a bug free tracking system.
 */
(function($){
    var dataTable; // This will be your dataTable object, so you do not need to clutter up the ram of the client with a ton of jQuery calls to obtain the same element
    var next; // This will be your next page button selector
    var previous; // This will be your previous page button selector
    var page; // This will be the current pagination page of the table. This will get updated whenever your table is redrawn after a successful ajax callback.
    var updated_page = page; //This stores the next page while the ajax request is in a transitional status, which prevents you from needing two separate handler methods for next and previous to update.
    var count; // This will be your count of rows. This needs to get sent to the backend to form a SQL query limit statement correctly. This will probably not change often, unless a given result set has less rows than the limit.
    var offset = page * count; // This will be the offset, which should be the number of the page multiplied by the count variable. This also needs to get sent to the backend over ajax to form the SQL query limit statement correctly. This will get updated whenever your table is redrawn after a successful ajax callback, by an increment of whatever your page multiplied by the count is
    var options = {}; // Your datatable options used to create the instance. Put whatever in here you would normally pass to dataTables
    var lib = {
        next: function()
        {
            updated_page = page++;
            jQuery.get({
                // Your data for the ajax call needs to go here. Your server must also provide a method to capture this and send the next page records. It will need the page number and the count of rows that should be returned, which you should have stored above under count and offset.
            }).done( lib.success ).fail( lib.fail );
        },
        previous: function()
        {
            updated_page = page--;
            if ( previous_page == 0)
            {
                // This means you are already on the first page, don't do anything
                return false;
            }
            jQuery.get({
                // Your data for the ajax call needs to go here. Your server must also provide a method to capture this and send the next page records. It will need the page number and the count of rows that should be returned, which you should have stored above under count and offset.
            }).done( lib.success ).fail( lib.fail );
        },
        success: function( data )
        {
             // This gets called when ajax resolves correctly. parse your data.
            // If you need to call any other ajax success related methods prior to handling the table update, do that here also, then call: 
            page = updated_page // Since the data is good, the page needs to change to reflect whatever the current page is.
            offset = page * count; // Update the offset so the next pagination call reflects the correct row set when sent back to the server again.
             lib.updateTable( data );
        },
        fail: function( data )
        {
            // Handle server errors here, like covering the table with a warning dialog or something
            updated_page = page; // Put the updated page back where it was, since the data is no good
            alert('Error with the server'); // Replace this with whatever looks best for your own app.
        },
        updateTable: function( data )
        {
            // Implement your redraw strategy here with whatever you get back from the server, then call the following:
            
            // You are going to want to replace your existing rows here based on the response data.
            
            dataTable.draw(); // Tells dataTables to update the table state, so the new rows reflect in the ui.
        }
    };
    dataTable = jQuery.dataTable('#table_id', options);
    next = jQuery('button#next_button');
    previous = jQuery('button#previous_button');
    next.on('click', lib.next );
    previous.on('click', lib.previous );
    
}(jQuery);

Open in new window


This represents how to performantly handle pagination. If you wanted search term filtering, you would expand on this same sort of logic to fire ajax, update the rows, and then redraw the table similarly. You would also want to implement a rescrape of the number of pages and some other things, but the above pagination should still work with that. You can easily build out more features by just adding the handlers to the lib object in the closure, and then placing the event bindings at the bottom after the next/previous ones.


On the backend, your server needs to run a SQL query similar to the following:

SELECT *
FROM `mytable`
WHERE `myfield` IN (id_1, id_2, id_3), -- The search ids if relevant, otherwise do not use this line
LIMIT 10, 20; -- The first number is the number of rows in your table, which ajax should have passed back from the count variable. The second number is the offset, which corresponds to the offset variable in the above javascript, and also should have been sent via ajax.

Open in new window

trevor1940Author Commented:
Thanx
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JavaScript

From novice to tech pro — start learning today.