How can I filter my ajax result

shuaaum

Hello. I am new here and new with datatables. I have used an external file which has a lot of json data in it and I am simply using this to output those data.

 var calltable = $('#call-table').dataTable({
            "oLanguage": {
                "sLengthMenu": "Show _MENU_",
                "sSearch": "Search"
            },
            "columnDefs": [{
                "targets": [-1],
                "searchable": false,
                "orderable": false
            }],
            "ajax": "{{ url('assets/files/call') }}",
            "columns": [
                {"data": "id"},
                {"data": "department"},
                {"data": "department_id"},
                {"data": "number"},
                {"data": "called"},
                {"data": "counter"},
                {"data": "recall"}
            ]
        });

I am using laravel and the {{ url('assets/files/call') }} point to my file. call being the file itself. So the department_id is bound to a user. So each user belongs to a department_id. Now this is not the users that I am outputting. But with the current code above, any user is able to any of the data pushed to the table. Because the file contains all the data. But I want to be able to filter the data by department_id. So what I am looking for is to be able to use where condition and say something like, where department_id is equal to 2 or something. I can not access the file and I can only do editing on this file. The json file is constantly being updated and that can not be filtered as there are other functions using that file as well. And I just want to see a solution to be able to filter this within the code above.

Thank you so much and I am loving the datatables so far! And sorry if I am not good with explanation.

kthorngren

Here are some options you can look at:

1. Use search to set the initial search to the desired department id.
2. Use the search() to search for the department id.
3. Use the ajax.data option to send the department id to the server and let the server script send the filtered results.
4. Use the ajax.dataSrc option as a function to get the rows with the desired department id and return them to Datatables.

Hope one of these will meet your requirements.

Kevin

shuaaum

@kthorngren Hello. Thank you so much for the reply. unfortunately I am very new to this and I hope you understand it. So how can I filter it by department_id 1 or so. It would really help if you could give me an example of some sort. Docs is confusing for me so sorry

shuaaum

Update: I did try method 3 but it still won't filter the results. The results are the same. Here is the updated code.

$(function() {
        var calltable = $('#call-table').dataTable({
            "oLanguage": {
                "sLengthMenu": "Show _MENU_",
                "sSearch": "Search"
            },
            "columnDefs": [{
                "targets": [ -1 ],
                "searchable": false,
                "orderable": false
            }],
            "ajax": {
                "url": "{{ url('assets/files/call') }}",
                "data": {
                    "department_id": 1
                }
            },
            "columns": [
                { "data": "id" },
                { "data": "department" },
                { "data": "number" },
                { "data": "called" },
                { "data": "counter" },
                { "data": "recall" }
            ]
        });

        setInterval(function(){
            calltable.api().ajax.reload(null,false);
        }, 3000);
    });

And it still shows all the results. The above code is updated as per the example on docs.

shuaaum

Also here is what my json file looks like

{
  "data": [
    {
      "id": 1,
      "department": "Consultation Room",
      "department_id": 1,
      "number": 2000,
      "called": "No",
      "counter": "NIL",
      "recall": "<button class=\"btn-floating disabled\" disabled><i class=\"mdi-navigation-refresh\"></i></button>"
    },
    {
      "id": 2,
      "department": "Scan Room",
      "department_id": 2,
      "number": 7010,
      "called": "No",
      "counter": "NIL",
      "recall": "<button class=\"btn-floating disabled\" disabled><i class=\"mdi-navigation-refresh\"></i></button>"
    },
    {
      "id": 3,
      "department": "Scan Room",
      "department_id": 2,
      "number": 7009,
      "called": "No",
      "counter": "NIL",
      "recall": "<button class=\"btn-floating disabled\" disabled><i class=\"mdi-navigation-refresh\"></i></button>"
    }
  ]
}

kthorngren

You have this:

            "ajax": {
                "url": "{{ url('assets/files/call') }}",
                "data": {
                    "department_id": 1
                }
            },

Is your server script setup to get the parameter department_id from the ajax request and query the data using that parameter?

Kevin

shuaaum

@kthorngren I have the exact code above. And what do you mean by my server script setup to get the parameter department_id from the ajax request? Aren’t we doing that with the above code? My json comes from a static file and the link to the file is {{ url(‘assets/files/call’) }}. The file name being “call” with no extension. And the content of the file is above.

With the code you gave, isn’t it supposed to search the json for where department_id = 1?

Thanks for the fast reply

kthorngren

Ajax is an http request sent to a server (your URL). The ajax.data option simply adds parameters to the request that is sent. It would be the responsibility of your server to look for that parameter and filter the data sent back. Datatables wouldn't be able to control this.

If you are only able to return all of the data then you will need to use option one or two above. Datatables would contain all of the data from the JSON but you would use one of those options to filter the display. Try the search first.

Kevin

shuaaum

@kthorngren The search would not work as if I write/search '2', it will output all occurrences with that number. So I want to be able to search just department_id. Is there anyway I could write the whole Ajax request so I can filter it before showing? How can I do it with my current code of adding data to column? Is there a way I could edit/write the ajax success method and filter from there and return to --->

"columns": [
            { "data": "id" },
            { "data": "department" },
            { "data": "number" },
            { "data": "called" },
            { "data": "counter" },
            { "data": "recall" }
        ]

As I mentioned before, my json is just inside a static file with a lot of json data and I do not know how to configure the server to filter the json with the method 3 you mentioned. Hope I am making sense here.

kthorngren

Maybe searchCols applied to your id column is what you want.

Kevin

shuaaum

@kthorngren Yes but this still does not give the idea of how I can search a specific column with a specific data. It just says,

$('#example').dataTable( {
  "searchCols": [
    null,
    { "search": "My filter" },
    null,
    { "search": "^[0-9]", "escapeRegex": false }
  ]
} );

It just searches for My Filter not in any specific column. And the null meaning it accepts null.

kthorngren

You would use this:

 "searchCols": [
    null,
    null,
    { "search": '2' },
    null,
    null,
    null,
    null,
   
  ]   

Here is an example with your code and data:
http://live.datatables.net/husetogi/1/edit

Kevin

shuaaum

@kthorngren Thank you so much. This works perfectly. Although the column is required to be displayed on the table. This still works. Thanks a bunch!

kthorngren

You can use columns.visible to hide the column if you like.

Kevin

shuaaum

@kthorngren This is perfect. Thank you so so much!