Server Side Column Filtering with Drop Down - Get all options

oranges13

Hello all. I have a datatable and I have successfully implemented the drop down column filters as shown in this example: https://datatables.net/examples/api/multi_filter_select.html

However, because I am using the server side processing, only the visible options on the first page are populated in the drop down boxes.

I'm using PHP on my server side. How can I pass the options into my JSON and access them in the initComplete function to prefill my select options?

$("#registrations").dataTable( {
    "ajax": '{!! route('ajax.registrations') !!}',
    processing: true,
    serverSide: true,
    select: true,
    stateSave: true,
    columns: [
        {data: "product.name"},
        {data: "alcohol"},
        {data: "reg_type.type"},
        {data: "status"},
        {data: "reg_date"},
        {data: "renew_date"},
    ],
    initComplete: function () {
        this.api().columns([0,2,3]).every( function () {
            var column = this;
            var select = $('<select class="form-control"><option value=""></option></select>')
                .appendTo( $(column.footer()).empty() )
                .on( 'change', function () {
                    var val = $(this).val();
                    column.search( this.value ).draw();
                } );

            // Only contains the *visible* options from the first page
            console.log(column.data().unique());

            // If I add extra data in my JSON, how do I access it here besides column.data?
            column.data().unique().sort().each( function ( d, j ) {
                select.append( '<option value="'+d+'">'+d+'</option>' )
            } );
        } );
    }
});

allan

The initComplete callback has the JSON data object that was loaded from the server passed into it. So what I would suggest in this case is that you have your server-side script detect the first draw (draw:1) and augment the JSON with the options for the columns, which you can then use in your initComplete callback rather than column().data().

Allan

oranges13

@allan Thank you, what would that look like in the JSON for example? That's the example that I'm missing from any that I've found online. I'm not sure how to return that data in a way that datatables will understand, and then utilize that in the initComplete function.

allan

{
  "data": [
    ...
  ],
  "columnFilters": [
    [
      { "label": 1, "value": 1 },
      ...
    ],
    ...
  ]
}

Have an entry for each column in the columnFilters array.

Allan

pjustindaryll

allan, how do we set it up? can you provide a sample?

Elle3141

@allan I am stuck on the same problem. I have searched on various forums and I haven't managed to find a solution to this.

Can you please explain how to implement this? I am happy to provide any information, which is necessary for you to help me.

ManuelWenner

@oranges13 @pjustindaryll @Elle3141
Could you fix that? I stuck on the same problem

colin

You would use ajax.json() in the initComplete function to extract the values for your select dropdown. The server would need to add that in as Allan suggested.

ManuelWenner

@colin Okay so you use the .options() from ajax.json() and add the Options when getting the fields on server side? At least that's the way I try. But if I do that, then the request is as slow as without server side request.

colin

But if I do that, then the request is as slow as without server side request.

This is only to get the options for the dropdown, on the very first Ajax request. The serverSide approach would still apply, only the data for that page would still be sent.

ManuelWenner

@colin Let me get you straight. The options are only fetched at the first request and not at the subsequent ones? Or do I have to adjust this manually?

colin

Yep, that's what Allan was suggesting above:

So what I would suggest in this case is that you have your server-side script detect the first draw (draw:1) and augment the JSON with the options for the columns

ManuelWenner

Ah okay, thanks. I will give it a try. Maybe this could be a nice feature in upcoming versions

saaron

Anyone have full code example of this working?

albertodominguezt

@ManuelWenner Did you get it done? please let us know

tefdat

just curious and want to ask, if @ManuelWenner or anybody else has a working example of drop down column filtering together with server side processing.

ManuelWenner

Hi @albertodominguezt and @tefdat yes i got it working but after we run in so much problems, we changed it back to client-side processing. If you have any detailed questions, just send me a message.

SyedAliAkbar

Can anyone solve this problem completely? Then please share.

bsander

For anyone else still looking for this solution, here's what worked for me. I had my server-side script determine whether or not this was the first draw of the table, and if yes, return an array that contained both the column number and the filter options for that column in a key called "columnFilters". So something like this:

{
  "data": <the_data>,
  "columnFilters": [
    {
      "column": 3,
      "filters": [
        {
          "label": "<filter_label>",
          "value": "<filter_value>"
        },
        {
          "label": "<filter_label>",
          "value": "<filter_value>"
        }
      ]
    },
    {
      "column": 4,
      "filters": [
        {
          "label": "<filter_label>",
          "value": "<filter_value>"
        },
        {
          "label": "<filter_label>",
          "value": "<filter_value>"
        }
      ]
    }
  ]

Then during the initComplete function, it was a matter of pulling the ajax data and using it to populate the select options. This is accomplished by setting the var that = this at the beginning of the function so I can access the dataTables instance itself, which allowed me to grab the ajax data via that.api().ajax.json(). The reason I specified the column numbers in the JSON above is so that I'd be able to match that with the column's index in the table (column[0][0] in the below code). In the example below, I only wanted column filtering for columns 3 and 4, so I only passed the filters for those columns in the JSON above

I wouldn't call myself a JS dev so apologies if the code is a bit crude, but hopefully you'll get the idea.

initComplete: function () {
var that = this;
this.api()
    .columns([3, 4])
    .every(function () {
        var column = this;
        var select = $('<select class="form-select form-select-sm"><option value="">No Filter</option></select>')
            .appendTo($(column.footer()).empty())
            .on('change', function () {
                var val = $.fn.dataTable.util.escapeRegex($(this).val());
                column.search(val ? '^' + val + '$' : '', true, false).draw();
            });
        ajax_data = that.api().ajax.json().columnFilters
        for (i=0; i < ajax_data.length; i++) {
            if (ajax_data[i].column == column[0][0]) {
                for (j = 0; j < ajax_data[i].filters.length; j++) {
                    select.append('<option value="' + ajax_data[i].filters[j].value + '">' + ajax_data[i].filters[j].label + '</option>')
                };
            };
        };
    });
}

This would work for any number of columns where you'd like filtering, just make sure your server-side script returns a columnFilter object for whichever column where you want to populate the select options.

This would not be scalable if the number of columns in your table changes frequently or dynamically, but should work well enough if the layout of your table is generally static.

zainjafar4829

Hi, everyone, I have applied the above example but the column filter method is not being called. Please explain that. Any help would be appreciated and when I call it by the below code it works but the pagination does not work and items count does not show please help me to call the additional data to show items in drop down.
respond_to do |format| if check_permission("CanDoEverything") format.html format.json { render json: ItemsDatatable.new(view_context).additional_data } end

DawidGrzejek

@bsander coudl you provie live example?

DawidGrzejek

In my example after doing that, after selecting one of filtere options in dropdown menu, table shows no matched items. I checked that in query I have LIKE statement with :binding_0 insted of actual value, meanwhile when I check no filter, data apear.

coliie

The following code worked for me in 2023 Nov.
https://gist.github.com/Daviddonadze/dbd922c50ddc3235423a714deeb9c65a

It puts the dropdown menu next to the column title text.

allan

Nice one! Thank you for sharing that with everyone who requested this.

Allan

sommerf_lf

I'm a bit late to the party but as I stuggled to find a fully working snippet, I am now sharing mine (including SSP PHP mysqli)
https://gist.github.com/sommerf-lf/c841136be9b5c68aa0a9a9e46df84ff5
(global filtering only works on type complex, not simple

To add filtering, as stated above, the first draw result from the backend will include a list of unique values for each column, that is specified in the backend as filterable.

I hope I could save some of you some time

allan

Super - thank you for sharing your script!

Allan