Home>


I am making a web application using Flask.
Enter search conditions in a form, generate a SQL query, extract the relevant records from the database, and display them in a table format. I'm using the JQuery plugin Datatables for tabular display. Datatables' sort function sorts in ascending/descending order by clicking on the column header. However, the drop-down list column using the select tag does not sort by the selected value.
The n_wire column in the figure is the drop-down list. The other columns sort by header clicks, but the n_wire column does nothing when clicked.

How can I sort it?

Source code
Arranged the code for the question.
The first is a Python program (Flask_Form06.py) that uses Flask.

from flask import Flask, render_template, request, jsonify
app = Flask (__ name__)
@ app.route ('/')
def sql_01 ():
    #return render_template ('ShowList06.html', rlist = jsonify (rlist))
    return render_template ('ShowList06.html')
@ app.route ('/ list_post', methods = ['POST'])
def send_json ():
    rlist = [
        {'id_inspect': 2030,'inspected_at': '2020-07-01 00:16:53','judge': 101,'n_wire': 0,'n_seg': 8},

        {'id_inspect': 9789,'inspected_at': '2020-07-01 02:31:34','judge': 102,'n_wire': 0,'n_seg': 1},

        {'id_inspect': 12976,'inspected_at': '2020-07-01 03:08:11','judge': 101,'n_wire': 29,'n_seg': 0},


        {'id_inspect': 40417,'inspected_at': '2020-07-01 10:01:29','judge': 105,'n_wire': 1,'n_seg': 0},

        {'id_inspect': 41683,'inspected_at': '2020-07-01 10:13:22','judge': 104,'n_wire': 3,'n_seg': 6}
    ]
    print (rlist)
    return jsonify (ResultSet = rlist)
## Magic
if __name__ == "__main__":
    app.run (debug = True)

Next is the ShowList06.html file that you put in the templates folder.

<! DOCTYPE html><html><head><!-Bootstrap-><link rel = "stylesheet"
    href = "https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"integrity = "sha384-ggOyR0iXCbMQv3Xipma34MD + dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T"
    crossorigin = "anonymous"><script src = "https://code.jquery.com/jquery-3.3.1.slim.min.js"
    integrity = "sha384-q8i/X + 965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH + 8abtTE1Pi6jizo"
    crossorigin = "anonymous"></script><script src = "https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"
    integrity = "sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1"
    crossorigin = "anonymous"></script><script src = "https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
    integrity = "sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM + B07jRM"
    crossorigin = "anonymous"></script><!-DataTables-><link rel = "stylesheet" href = "https://cdn.datatables.net/t/bs-3.3.6/jqc-1.12.0,dt-1.10.11/datatables.min.css" /><script src = "https://cdn.datatables.net/t/bs-3.3.6/jqc-1.12.0,dt-1.10.11/datatables.min.js"></script></head><body>                    <table>            <thead>                <tr>                    <th>id_inspect</th>                    <th>inspected_at</th>                    <th>judge</th>                    <th>n_wire</th>                    <th>n_seg</th>                </tr>            </header>        </table>            <script>      // Set the display language to Japanese
        $.extend ($.fn.dataTable.defaults, {
            language: {
                url: "http://cdn.datatables.net/plug-ins/1.10.16/i18n/Japanese.json"
            }
        });
        var n_wire_op = [0,1,2,3,4,5,28,29,30];// n_wire drop-down list enumeration
        $(document) .ready (function () {
            var tbl = $('#myTable'). DataTable ({
                bInfo: true,
                bSort: true,
                processing: true,ajax: {
                    dataType:'json',
                    url:'/ list_post',
                    method:'post',
                    dataSrc: "ResultSet"
                },


            columns: [
                {data:'id_inspect', className:'text-right'},


                {data:'inspected_at'},


                {data:'judge', className:'text-right'},


                {data:'n_wire', className:'text-right',
                    render: function (data, type, row) {
                        var html = "";
                        html + = "<select class ='form-control'>";
                        for (var val of n_wire_op) {
                            if (data == val) {
                                str1 = "selected>"
                            } else {
                                str1 = ">"
                            }
                            html + = "<option value =" + val + str1 + val + "</option>"
                        }
                        html + = "</select>"
                        return html;
                        }
                    },


                {data:'n_seg', className:'text-right'}
            ]
            });
        });
  </script></body>

that's all, thank you very much.

  • Answer # 1

    It took time, but the reference site
    https://www.gyrocode.com/articles/jquery-datatables-how-to-search-and-order-by-input-or-select-elements/
    I solved it by referring to.
    In the reference site, the select box was defined in html, but in my case I tried changing the code variously, probably because it was received from the server with json and rendered, but even if I changed the selection It didn't work properly because I couldn't sort it, or even if I selected it, it returned to the original value, but it finally worked with the following code. In addition to debugging, there is also a button to copy the contents of the table to the clipboard and a button to save it to a CSV file. However, for some reason, there is still a new problem that all the values ​​of the column (n_wire) of the select box become "012345282930", so please be careful if you use the code.
    ShowList06.html code

    <! DOCTYPE html><html lang = "ja"><head><!-Bootstrap-><p><script src = "https://code.jquery.com/jquery-3.3.1.slim.min.js"
        integrity = "sha384-q8i/X + 965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH + 8abtTE1Pi6jizo"
        crossorigin = "anonymous"></script><script src = "https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"
        integrity = "sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1"
        crossorigin = "anonymous"></script><script src = "https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
        integrity = "sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM + B07jRM"
        crossorigin = "anonymous"></script><!-DataTables-><p><script src = "https://cdn.datatables.net/t/bs-3.3.6/jqc-1.12.0,dt-1.10.11/datatables.min.js"></script><script src = "https://cdn.datatables.net/buttons/1.6.4/js/dataTables.buttons.min.js"></script><script src = "https://cdn.datatables.net/buttons/1.6.4/js/buttons.flash.min.js"></script><script src = "https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script><script src = "https://cdn.datatables.net/buttons/1.6.4/js/buttons.html5.min.js"></script></head><body>                    <table id = "myTable">            <thead>                <tr>                    <th>id_inspect</th>                    <th>inspected_at</th>                    <th>judge</th>                    <th>n_wire</th>                    <th>n_seg</th>                </tr>            </header>        </table>            <script>      // Set the display language to Japanese
            $.extend ($.fn.dataTable.defaults, {
                language: {
                    url: "http://cdn.datatables.net/plug-ins/1.10.16/i18n/Japanese.json"
                }
            });
            var n_wire_op = [0,1,2,3,4,5,28,29,30];// n_wire drop-down list enumeration
            $(document) .ready (function () {
                var tbl = $('#myTable'). DataTable ({
                    lengthMenu: [5, 10, 15, 20, 25],
     // Set the number switching value in 5 increments
                    displayLength: 5, // Set the default value of the number to 5
                    bInfo: true, // Display of "Display from yy to zz in xx"
                    bSort: true,
                    dom:'Bfrtip',
                    buttons: [
                       //'copy','csv','excel','pdf','print'
                       'copy','csv'
                    ],,
                    bInfo: true,bSort: true,
                    processing: true,
                    ajax: {
                        dataType:'json',
                        url:'/ list_post',
                        method:'post',
                        dataSrc: "ResultSet"
                    },
                    columnDefs: [
                        {targets: [3],
                            type:'string',
                            render: function (data, type, full, meta) {
                                if (type ==='filter' || type ==='sort') {
                                    var api = new $.fn.dataTable.Api (meta.settings);
                                    var td = api.cell ({row: meta.row, column: meta.col}). node ();
                                    data = $('select', td) .val ();
                                }
                                return data;
                            }
                        }
                    ],,
                columns: [
                    {data:'id_inspect', className:'text-right'},
                    {data:'inspected_at'},
                    {data:'judge', className:'text-right'},
                    {data:'n_wire', className:'row-selected',
                        render: function (data, type, row, meta) {
                            var html = "";
                            html + = "<select class ='form-control' id ='ListA'>";
                            for (var val of n_wire_op) {
                                if (data == val) {
                                    str1 = "selected>"
                                } else {
                                    str1 = ">"
                                }
                                html + = "<option value =" + val + str1 + val + "</option>"
                            }
                            html + = "</select>"
                            return html;
                            }
                        },
                    {data:'n_seg', className:'text-right'}
                ]
                });
                $('#myTable'). on ('change','tbody select', function () {
                    var sVal = $(this) .val ();
                    var cell = tbl.cell ($(this) .closest ('td'));
                    cell.data (sVal) .draw ();
                });
            });
      </script></body>

    After that, I found a site that explains how to do it better.
    Referencing on this site
    https://datatables.net/release-datatables/examples/plug-ins/dom_sort.html
    If you look at, you can see how to sort by select box, text input, checkbox, etc.
    By the way, even if I used that method, the clipboard value remained fixed.

  • Answer # 2

    I had a similar question below with an answer.
    Respondents have also made demos, so why not try it as a reference?

    JQuery Datatables sorting a select inside a column

  • Answer # 3

    It took time, but the reference site
    https://www.gyrocode.com/articles/jquery-datatables-how-to-search-and-order-by-input-or-select-elements/
    I solved it by referring to.
    In the reference site, the select box was defined in html, but in my case I tried changing the code variously, probably because it was received from the server with json and rendered, but even if I changed the selection It didn't work properly because I couldn't sort it, or even if I selected it, it returned to the original value, but it finally worked with the following code. In addition to debugging, there is also a button to copy the contents of the table to the clipboard and a button to save it to a CSV file. However, for some reason, there is still a new problem that all the values ​​of the column (n_wire) of the select box become "012345282930", so please be careful if you use the code.
    ShowList06.html code

    <! DOCTYPE html><html lang = "ja"><head><!-Bootstrap-><p><script src = "https://code.jquery.com/jquery-3.3.1.slim.min.js"
        integrity = "sha384-q8i/X + 965DzO0rT7abK41JStQIAqVgRVzpbzo5smXKp4YfRvH + 8abtTE1Pi6jizo"
        crossorigin = "anonymous"></script><script src = "https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"
        integrity = "sha384-UO2eT0CpHqdSJQ6hJty5KVphtPhzWj9WO1clHTMGa3JDZwrnQq4sF86dIHNDz0W1"
        crossorigin = "anonymous"></script><script src = "https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"
        integrity = "sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM + B07jRM"
        crossorigin = "anonymous"></script><!-DataTables-><p><script src = "https://cdn.datatables.net/t/bs-3.3.6/jqc-1.12.0,dt-1.10.11/datatables.min.js"></script><script src = "https://cdn.datatables.net/buttons/1.6.4/js/dataTables.buttons.min.js"></script><script src = "https://cdn.datatables.net/buttons/1.6.4/js/buttons.flash.min.js"></script><script src = "https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script><script src = "https://cdn.datatables.net/buttons/1.6.4/js/buttons.html5.min.js"></script></head><body>                    <table id = "myTable">            <thead>                <tr>                    <th>id_inspect</th>                    <th>inspected_at</th>                    <th>judge</th>                    <th>n_wire</th>                    <th>n_seg</th>                </tr>            </header>        </table>            <script>      // Set the display language to Japanese
            $.extend ($.fn.dataTable.defaults, {
                language: {
                    url: "http://cdn.datatables.net/plug-ins/1.10.16/i18n/Japanese.json"
                }
            });
            var n_wire_op = [0,1,2,3,4,5,28,29,30];// n_wire drop-down list enumeration
            $(document) .ready (function () {
                var tbl = $('#myTable'). DataTable ({
                    lengthMenu: [5, 10, 15, 20, 25],
     // Set the number switching value in 5 increments
                    displayLength: 5, // Set the default value of the number to 5
                    bInfo: true, // Display of "Display from yy to zz in xx"
                    bSort: true,
                    dom:'Bfrtip',
                    buttons: [
                       //'copy','csv','excel','pdf','print'
                       'copy','csv'
                    ],,bInfo: true,
                    bSort: true,
                    processing: true,
                    ajax: {
                        dataType:'json',
                        url:'/ list_post',
                        method:'post',
                        dataSrc: "ResultSet"
                    },
                    columnDefs: [
                        {targets: [3],
                            type:'string',
                            render: function (data, type, full, meta) {
                                if (type ==='filter' || type ==='sort') {
                                    var api = new $.fn.dataTable.Api (meta.settings);
                                    var td = api.cell ({row: meta.row, column: meta.col}). node ();
                                    data = $('select', td) .val ();
                                }
                                return data;
                            }
                        }
                    ],,
                columns: [
                    {data:'id_inspect', className:'text-right'},
                    {data:'inspected_at'},
                    {data:'judge', className:'text-right'},
                    {data:'n_wire', className:'row-selected',
                        render: function (data, type, row, meta) {
                            var html = "";
                            html + = "<select class ='form-control' id ='ListA'>";
                            for (var val of n_wire_op) {
                                if (data == val) {
                                    str1 = "selected>"
                                } else {
                                    str1 = ">"
                                }
                                html + = "<option value =" + val + str1 + val + "</option>"
                            }
                            html + = "</select>"
                            return html;
                            }
                        },
                    {data:'n_seg', className:'text-right'}
                ]
                });
                $('#myTable'). on ('change','tbody select', function () {
                    var sVal = $(this) .val ();
                    var cell = tbl.cell ($(this) .closest ('td'));
                    cell.data (sVal) .draw ();
                });
            });
      </script></body>