Optgroup for global filter that uses Datatable column data

ctran2

I bought DataTables Editor license and have been trying to create a global select dropdown with optgroup to filter the table as user selects an option.

Optgroup sections are populated with the Category column from Datatable, and the nested options are populated with Question column. I've followed this forum post http://mail.datatables.net/forums/discussion/50262/optgroup-in-select2-plugin-of-datatables-editor and found that select2 plugin can be used to generate optgroups inside editor. However, I'm looking to append the dropdown to an element outside of Datatable. I have the dropdown with a list of Question using Datatable column().search(), but not sure on how to add optgroup to it. Any thoughts or guidance would be much appreciated!

SQL Server table:

Create table QuestionnaireResponse(
     Id int
     Category varchar(50)
     Question varchar(100)
     Response varchar(100)
)

Controller:

public ActionResult QuestionnaireResponseSummary()
        {

            using (var db = new Database("sqlserver", cnStr))
            {
                var response = new Editor(db, "QuestionnaireResponse", "Id")
                    .Model<QuestionnaireResponseModel>("QuestionnaireResponse")
                    .Where(q =>
                    {
                        q.Where(r =>
                        {
                           // This receives result sent from datasetPeriod dropown element
                            r.Where("DatasetPeriod", Request.Form["datasetPeriod"], "=");
                        });
                    }.Process(Request).Data();

                return new JsonResult(response);
            }
        }

Javascript:

var QuestionnaireResponseTable = $('#QuestionnaireResponse').DataTable({
                dom: 'Bfiprt',
                ajax: {
                    'url': '/api/QuestionnaireResponseSummary',
                    "type": 'POST',
                    "data": function (d) {
                        d.datasetPeriod = $('#datasetPeriod').val();
                    }
                },
                columns: [
                    {
                        "data": "QuestionnaireResponse.Id"
                    },
                                        {
                        "data": "QuestionnaireResponse.Question"
                    },
                                        {
                        "data": "QuestionnaireResponse.Category"
                    },
                                        {
                        "data": "QuestionnaireResponse.Response"
                    }
                ],
                select: true,
                lengthChange: false,
                                columnDefs: [
                    {
                        visible: false,
                        targets: [0,1,2]
                    }

                ],

                buttons: [
                    {
                        extend: 'excel',
                        text: 'Excel',
                        show: ':hidden'
                    },
                    {
                        extend: 'pageLength'
                    }
                ]
            });
               // Selecting datatasetPeriod reloads table
        $('#datasetPeriod').on('change', function () {
            QuestionnaireResponseTable.ajax.reload();
        });
               
               // Refresh list of question as table is drawn
        QuestionnaireResponseTable.on('draw', function () {
            buildSelect(QuestionnaireResponseTable);
            $('.selectpicker').selectpicker('refresh');
        });

               // Populate question dropdown list with column[1] from datatable
               // Not sure how to append optgroups in here
        function buildSelect(QuestionnaireResponseTable) {
            QuestionnaireResponseTable.column([1]).every(function () {
                var column = QuestionnaireResponseTable.column(this, { search: 'applied' });
                var select = $('<select class="selectpicker" data-style="btn-secondary" data-live-search="true"></select>')
                    .appendTo($('#Question').empty())
                    .on('change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );

                        column
                            .search(val ? '^' + val + '$' : '', true, false)
                            .draw();
                        console.log('Search value is: {' + val + '}')
                    });

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

                });

                // The rebuild will clear the exisiting select, so it needs to be repopulated
                var currSearch = column.search();
                if (currSearch) {
                    select.val(currSearch.substring(1, currSearch.length - 1));
                }
            });
        }

CSHTML:

<table border="0" cellspacing="5" cellpadding="5">
    <tbody>
        <tr>
             <td>Dataset Period:</td>
             <td><select class="selectpicker" data-style="btn-secondary" data-live-search="true" id="datasetPeriod"></select></td>
        </tr>
        <tr>
            <td>Question:</td>
            <td><span id="Question"></span></td>
        </tr>
    </tbody>
</table>

<br />
<br />

<table cellpadding="0" cellspacing="0" border="0" class="table table-hover table-bordered" id="QuestionnaireResponse" width="100%">
    <thead>
        <tr>
            <th>ID</th>
            <th>Question</th>
            <th>Category</th>
            <th>Response</th>
        </tr>
    </thead>
</table>
   

allan

The optgroup element is a child of the select element, as shown in the MDN documentation.

So what you would do is create an optgroup for each group and append it to the select. In turn, for each optgroup you would append your option tags to the group it belongs to.

Allan

ctran2

Hi @allan,
Thank your your guide. Just wanted to make sure I didn't get the wrong idea. I have adjusted buildSelect function to create an optgroup for each element in column[3], which is Category column. Then I append the according options from column[1], which is Question. However I haven't figured out how to match the questions to their category. Here's my buildSelect function:

function buildSelect(QuestionnaireResponseTable) {
            QuestionnaireResponseTable.column([3]).every(function () {
                var optgroups = this;
                // create select element
                var select = $('<select class="selectpicker" data-style="btn-secondary" data-live-search="true"></select>')
                    .appendTo($('#Question').empty()); 
// Create optgroup for each category element
                optgroups.data().unique().sort().each(function (o, i) {
                    var optgroup = $('<optgroup label="' + o + '">').appendTo(select);

                    QuestionnaireResponseTable.column([1]).every(function ()
                    {
                        var column = QuestionnaireResponseTable.column(this, { search: 'applied' });

                        select.on('change', function () {
                            var val = $.fn.dataTable.util.escapeRegex($(this).val());

                            column.search(val ? '^' + val + '$' : '', true, false).draw();
                            console.log('Search value is: {' + val + '}');
                        });

                        column.data().unique().sort().each(function (d, j) {
                            // Check question's category. Issue here
                            if (QuestionnaireResponseTable.cell(j, 3).data() === o) {
                                $('<option value="' + d + '">' + d + '</option>').appendTo(optgroup);
                            }
                        });

                        // The rebuild will clear the existing select, so it needs to be repopulated
                        var currSearch = column.search();
                        if (currSearch) {
                            select.val(currSearch.substring(1, currSearch.length - 1));
                        }
                    });
// Closing optgroup
                    $('</optgroup>').appendTo(select)
                });

            });
        }

kthorngren

Sounds like you are trying to group the select options for column 3 based on their column 1 values. You will need to restructure your loops. One option might be to build an object where the keys are the values in column 1 and the value is an array of column 3 values corresponding to column 1. Loop through the object and create a new optgroup for each new key.

If you want help with this then please build a simple test case with example values for columns 1 and 3 and specify what the select list should look like. This is more a Javascript exercise than anything specific to Datatables.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin