How to add a filter above my table that's filled via Ajax?

fingolas · April 5

Hi, I'm only days into DataTables, but already pretty hooked. I have a project, that I was working on and have now rebuilt it with DataTables – and solved most of the initial problems. But now I'm a bit stuck:

Let me explain: I am building a table, that I fill with data that I'm creating/manipulating/refining in Google Sheets (for me as a basically non-coder this is by far the easiest way to do that, and I have already built something, I really like). But I don't like very much how Sheets is presenting the table and now finally I found a way of doing that via:
https://www.bpwebs.com/how-to-display-google-sheets-data-on-a-website

Thanks to the explanation from DataTables and this very helpful forum, I've already gotten very far, but now I haven't been able to add the possibility to filter the data via forms above the columns. I found explanations, but couldn't make them work. Could anyone help me?

This is the relevant code (I think it won't fill the data in there, but for the filter forms this doesn't matter, right?):
https://live.datatables.net/rabavavu/1/edit

Here is the whole page on GitHub (including more scripts, that I tried to use to get the filters):
https://github.com/fingolas23/AccountsOverview/blob/main/overview.html

And here is the page itself (including the data itself):
https://fingolas.eu/fediverse/overview.html

kthorngren · April 5

Sounds like you will want to use ajax.data as a function to send the values you have in your inputs to the. server. See this example. In your event handler to search the values use ajax.relaod() to refresh the table data. You don't need to use serverSide: true, like the example, to use ajax.data as a function.

Kevin

bur · April 6

It would probably look something like this:

let table = new DataTable('#tableId', {
    ajax: {
        url: '/my/url/',
        data: function(data) {
            // add values from form to data here, e.g.:
           data['myFilter'] = $('#myFilter').val();
        },
    },
    // other options....
});

$('#filterForm').on('submit', function(e) {
    e.preventDefault();
    table.ajax.reload();
});

fingolas · April 6

Thank you very much. I didn't get it to work on my first try and will try again later with more time.

kthorngren · April 6

What did you try?

What exactly isn't working?

I built a simple example:
https://live.datatables.net/socirone/49/edit

The server script in this environment doesn't use the parameters sent by ajax.data. Use the browser's network inspector to see the input's value is sent via ajax. For example:

If you still need help then please update my example or provide a link to a running test case showing the issue. The test case you posted has some errors.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

fingolas · April 6

But even in your example, the field "Ajax Search" doesn't filter. Or am I missing something?

Sadly, I can't get my example working in live.datatables, It think because it won't load the json from external.
This is the example code:
https://live.datatables.net/xosokahe/1/edit

Here it works:
https://fingolas.eu/fediverse/test.html

What I want is a box exactly like this one (for some columns):
https://datatables.net/extensions/searchpanes/examples/advanced/columnFilter.html

kthorngren · April 6

But even in your example, the field "Ajax Search" doesn't filter. Or am I missing something?

As I said the server script is not setup to filter based on what is entered into the Ajax search field. That is why I suggested looking at the network inspector to see the parameter being sent to the server. Once the field is sent to the server it is up to you to develop the server side code to retrieve parameter and filter the data.

Sadly, I can't get my example working in live.datatables,

You can update your Here it works link with your code and we can help debug the client. However we won't be able to debug your server code which is what does most of the work.

What I want is a box exactly like this one (for some columns):

SearchPanes will work if all the data is at the client or if you are using Server Side Processing which requires a server library that performs server based searching, paging and sorting. You can use one of the Datatables supplied libraries for server side processing. See this blog about using the Editor server side libraries to support SearchPanes and SearchBuilder.

You can create your own inputs if you want to use the ajax.data option to filter the returned dataset based on search inputs.

Kevin

fingolas · April 7

Oh sorry, this is the example code:

<!DOCTYPE html>
<html>
    <head>
      <title>Media in the Fediverse</title>
        <script src="https://code.jquery.com/jquery-3.1.0.min.js"></script>

        <link href="https://cdn.datatables.net/v/bs4/dt-2.2.2/datatables.min.css" rel="stylesheet">
        <link href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.5.2/css/bootstrap.css" rel="stylesheet">

        <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/pdfmake.min.js" integrity="sha384-VFQrHzqBh5qiJIU0uGU5CIW3+OWpdGGJM9LBnGbuIH2mkICcFZ7lPd/AAtI7SNf7" crossorigin="anonymous"></script>
        <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.2.7/vfs_fonts.js" integrity="sha384-/RlQG9uf0M2vcTw3CX7fbqgbj/h8wKxw7C3zu9/GxcBPRKOEcESxaxufwRXqzq6n" crossorigin="anonymous"></script>
        <script src="https://cdn.datatables.net/v/se/jq-3.7.0/moment-2.29.4/jszip-3.10.1/dt-2.2.2/af-2.7.0/b-3.2.2/b-colvis-3.2.2/b-html5-3.2.2/b-print-3.2.2/cr-2.0.4/date-1.5.5/fc-5.0.4/fh-4.0.1/kt-2.12.1/r-3.0.4/rg-1.5.1/rr-1.5.0/sc-2.4.3/sb-1.8.2/sp-2.3.3/sl-3.0.0/sr-1.4.1/datatables.min.js" integrity="sha384-3zGIQnZVx2VTB2F1KvSGXKSlp7cAbtNpqKEQuy8KGQelWxlDOkYcvE0p6u4x+3vF" crossorigin="anonymous"></script>
    </head>
    
    
    
    
    <body>
      <h1 style="text-align: center;">Verified Media Accounts in the Fediverse</h1>


        <table id="example" class="table table-striped table-bordered" style="margin: 0 auto; width: 100%; clear: both; border-collapse: collapse; table-layout: fixed; word-wrap:break-word;">
        <thead>
                <tr>
                    <th>Name</th>
                    <th>Handle</th>
                    <th>Followers</th>
                    <th>Following</th>
                    <th>Posts</th>
                    <th>Per day</th>
                    <th>Last Status</th>
                    <th>Instance</th>
                    <th>Software</th>
                    <th>Created at</th>
                    <th>Bio</th>
                    <th>URL</th>
                </tr>
            </thead>
        </table>
        
        <script type="text/javascript" class="init">

    var table = $('#example').DataTable({
      orderCellsTop: true,
      ajax: 'https://fingolas.eu/fediverse/echo.json',
            columns: [
                { data: 'Name' },
                { data: 'For Import' },
                { data: 'Followers' },
                { data: 'Following' },
                { data: 'Posts' },
                { data: 'Per day' },
                { data: 'Last Status' },
                { data: 'Instance' },
                { data: 'Software' },
                { data: 'Created at' },
                { data: 'Bio' },
                { data: 'URL' }
            ],
            columnDefs: [
                {targets: [6, 9], render: function (data) {return moment(data).format('MMMM Do YYYY');}},
                {targets: [5], render: function ( data, type, row ) {return (1 * data).toFixed(1);}},
                {className: 'dt-center', targets: '_all'},
                {width: '6%', targets: [6, 9] },
                {width: '5%', targets: [2, 3, 4 ,5] },
                {width: '7%', targets: [0, 7, 8] }
            ], 
            pageLength: 1000,
            lengthMenu: [
                [100, 500, -1],
                [100, 500, 'All']
    ]
      
    });
        
    </script>
</body>
</html>

The json looks like this:

{"data":[{"Nr.":"","Lg.":"","Name":"National Geographic","Handle":"NationalGeographic@flipboard.com","Followers":6034756,"Following":0,"Posts":21316,"Per day":110.44559585492227,"Last Status":"2025-04-04T22:00:00.000Z","Instance":"flipboard.com","Software":"Flipboard","Created at":"2024-09-24T22:00:00.000Z","Bio":"Inspiring perspectives on the planet.\n\nFollow to see posts from the official National Geographic Flipboard profile.","For Import":"@NationalGeographic@flipboard.com","URL":"https://flipboard.com/@NationalGeographic"},{"Nr.":"","Lg.":"","Name":"USA TODAY","Handle":"USAToday@flipboard.com","Followers":2482968,"Following":0,"Posts":502185,"Per day":2601.9948186528495,"Last Status":"2025-04-05T22:00:00.000Z","Instance":"flipboard.com","Software":"Flipboard","Created at":"2024-09-24T22:00:00.000Z","Bio":"The nation's news.\n\nFollow to see posts from the official USA TODAY Flipboard profile.","For Import":"@USAToday@flipboard.com","URL":"https://flipboard.com/@USAToday"}]}

kthorngren · April 7

I think that is pretty much the code in your test case. What you have is pretty standard for client side processing with ajax loaded data. Do you have specific questions at this point? Reposting the same code without additional questions isn't going to go much further.

With this setup you can use any combination of the following:

Searching using inputs in the header like this example and this example.
Search inputs outside of the header to perform client side searching using search() or column().search(). Similar to option 1.
SearchPanes like these examples.
SearchBuilder like these examples.
Search inputs with ajax.data as a function to send input values as parameters to the server to filter the JSON response. Requires scripting in the server code to fetch the parameter(s) and filter the response data.

Now its your turn to choose one or more of these options to try. I suggest trying them with your test case. If you have questions or difficulties we can take a look at the updated test case to see what you are trying to do to help debug.

Kevin

fingolas · April 8

Thank you very much! I'll look into it and come back to you.

How to add a filter above my table that's filled via Ajax?

How to add a filter above my table that's filled via Ajax?

Answers

Howdy, Stranger!

Categories

DataTables

How to add a filter above my table that's filled via Ajax?

How to add a filter above my table that's filled via Ajax?

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables