javascriptjquerydatatablesinput-filtering

Dynamic filtering values in select elements in Datatables


Using the following code of multi-filtering select inputs in Datatables is it possible to show only available values in the other select inputs upon a selection in one filter? To be more precise, in this example if I select 'Tokyo' as an Office, I would like to populate only the values 'Accountant', 'Integration Specialist', 'Support Engineer' and 'Regional Marketing' in the dropdown menu of Position.

$(document).ready(function() {
$('#example').DataTable( {
    initComplete: function () {
        this.api().columns([1,2]).every( function () {
            var column = this;
            var select = $('<select><option value=""></option></select>')
                .appendTo( $(column.footer()).empty() )
                .on( 'change', function () {
                    var val = $.fn.dataTable.util.escapeRegex(
                        $(this).val()
                    );

                    column
                        .search( val ? '^'+val+'$' : '', true, false )
                        .draw();
                } );

            column.data().unique().sort().each( function ( d, j ) {
                select.append( '<option value="'+d+'">'+d+'</option>' )
            } );
        } );
    }
} );
} );

////// here I get the unique values of each filtered `select` option
$('select').on('change', function () {
            var dtable = $('#datatable').DataTable();

            var filteredArray = [];
            var filteredArray2 = [];

            dtable.column(1, { search: 'applied' }).data()
            .unique()
            .sort()
            .each(function (value, index) {
                filteredArray.push(value);
            });

            dtable.column(2, { search: 'applied' })
            .data()
            .unique()
            .sort()
            .each(function (value, index) {
                filteredArray2.push(value);
            });

            console.log(filteredArray);
            console.log(filteredArray2);

});

In my case I have filters in two columns only as it is shown in the above snippet, so upon selection in one of the two filters I would ideally like to show only available values in the other filter.

Although I have managed to get the unique values of each filter upon a selection I am struggling to hide all the input values that do not exist in the filteredArrays


Solution

  • Here is one approach for doing this.

    The end result is as follows:

    enter image description here

    Building a drop-down which only contains the unfiltered (visible) values of a column is relatively straightforward. At the heart of doing this we use the following:

    columns( { search: 'applied' } ).data()[index]
    

    Most of the complexity relates to managing the inter-related states of the two drop-downs. After loading the page, whichever drop-down gets used first is designated as the "primary" drop-down and the other is the "secondary". Whenever the user selects a new value from the primary drop-down, we have to clear the secondary drop-down; and then after the primary drop-down filter has been applied, we have to re-build the secondary drop-down's list of values.

    The end result is this:

    <script type="text/javascript">
    
    /* Each drop-down selection affects the values in the other drop-downs */
    
    var primaryColIdx;
    var secondaryColIdx;
    
    $(document).ready(function() {
        $('#example').DataTable( {
            initComplete: function () {
              populateDropdowns(this);
            }
        } );
    
    } );
    
    function populateDropdowns(table) {
        table.api().columns([1,2]).every( function () {
            var column = this;
            //console.log("processing col idx " + column.index());
            var select = $('<select><option value=""></option></select>')
                .appendTo( $(column.footer()).empty() )
                .on( 'change', function () {
                    var dropdown = this;
                    doFilter(table, dropdown, column);
                    rebuildSecondaryDropdown(table, column.index());
                } );
    
            column.data().unique().sort().each( function ( val, idx ) {
                select.append( '<option value="' + val + '">' + val + '</option>' )
            } );
        } );
    }
    
    function doFilter(table, dropdown, column) {
        // first time a drop-down is used, it becomes the primary. This
        // remains the case until the page is refreshed:
        if (primaryColIdx == null) {
            primaryColIdx = column.index();
            secondaryColIdx = (primaryColIdx == 1) ? 2 : 1;
        }
    
        if (column.index() === primaryColIdx) {
            // reset all the filters because the primary is changing:
            table.api().search( '' ).columns().search( '' );
        }
    
        var filterVal = $.fn.dataTable.util.escapeRegex($(dropdown).val());
        //console.log("firing dropdown for col idx " + column.index() + " with value " + filterVal);
        column
            .search( filterVal ? '^' + filterVal + '$' : '', true, false )
            .draw();
    }
    
    function rebuildSecondaryDropdown(table, primaryColIdx) {
        var secondaryCol;
    
        table.api().columns(secondaryColIdx).every( function () {
            secondaryCol = this;
        } );
    
        // get only the unfiltered (unhidden) values for the "other" column:
        var raw = table.api().columns( { search: 'applied' } ).data()[secondaryColIdx];
        // the following uses "spread syntax" (...) for sorting and de-duping:
        // https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Spread_syntax
        var uniques = [...new Set(raw)].sort();
    
        var filteredSelect = $('<select><option value=""></option></select>')
            .appendTo( $(secondaryCol.footer()).empty() )
            .on( 'change', function () {
                var dropdown = this;
                doFilter(table, dropdown, secondaryCol);
                //rebuildSecondaryDropdown(table, column.index());
            } );
    
        uniques.forEach(function (item, index) {
            filteredSelect.append( '<option value="' + item + '">' + item + '</option>' )
        } );
    
    }
    
    </script>