Server Side Column Filtering with Drop Down - Get all options

Server Side Column Filtering with Drop Down - Get all options

oranges13oranges13 Posts: 2Questions: 1Answers: 0

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>' )
            } );
        } );
    }
});

Answers

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    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

  • oranges13oranges13 Posts: 2Questions: 1Answers: 0

    @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.

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin
    {
      "data": [
        ...
      ],
      "columnFilters": [
        [
          { "label": 1, "value": 1 },
          ...
        ],
        ...
      ]
    }
    

    Have an entry for each column in the columnFilters array.

    Allan

  • pjustindaryllpjustindaryll Posts: 13Questions: 4Answers: 0

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

  • Elle3141Elle3141 Posts: 3Questions: 1Answers: 0

    @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.

  • ManuelWennerManuelWenner Posts: 16Questions: 2Answers: 1

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

  • colincolin Posts: 15,240Questions: 1Answers: 2,599

    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.

  • ManuelWennerManuelWenner Posts: 16Questions: 2Answers: 1

    @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.

  • colincolin Posts: 15,240Questions: 1Answers: 2,599

    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.

  • ManuelWennerManuelWenner Posts: 16Questions: 2Answers: 1

    @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?

  • colincolin Posts: 15,240Questions: 1Answers: 2,599

    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

  • ManuelWennerManuelWenner Posts: 16Questions: 2Answers: 1

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

  • saaronsaaron Posts: 1Questions: 0Answers: 0

    Anyone have full code example of this working?

  • albertodomingueztalbertodominguezt Posts: 1Questions: 0Answers: 0

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

  • tefdattefdat Posts: 42Questions: 7Answers: 3

    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.

  • ManuelWennerManuelWenner Posts: 16Questions: 2Answers: 1

    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.

  • SyedAliAkbarSyedAliAkbar Posts: 1Questions: 0Answers: 0

    Can anyone solve this problem completely? Then please share.

  • bsanderbsander Posts: 1Questions: 0Answers: 0
    edited May 2022

    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.

  • zainjafar4829zainjafar4829 Posts: 1Questions: 0Answers: 0
    edited November 2022

    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

  • DawidGrzejekDawidGrzejek Posts: 11Questions: 5Answers: 0

    @bsander coudl you provie live example?

  • DawidGrzejekDawidGrzejek Posts: 11Questions: 5Answers: 0

    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.

  • coliiecoliie Posts: 1Questions: 0Answers: 0

    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.

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

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

    Allan

  • sommerf_lfsommerf_lf Posts: 1Questions: 0Answers: 0

    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 :)

  • allanallan Posts: 63,602Questions: 1Answers: 10,486 Site admin

    Super - thank you for sharing your script!

    Allan

Sign In or Register to comment.