Unable to use colvis button of DataTable

Unable to use colvis button of DataTable

rahulsaxenarahulsaxena Posts: 9Questions: 0Answers: 0

Hi Team,

I am developing a UI to display output of SQL query in DataTables in Flask. The data is being returned as JSON from AJAX call. As of now everything is working as expected but when i try to add 'colvis' button as part of adding an extra feature the code break.
Below is the screenshot of the error i get:

Below is my DataTable code:

//initialize DataTables
                    var table = $('#example').DataTable({
                        columns: cols,
                        "scrollY": 500,
                        "scrollX": true,
                        // lengthChange: false,
                        // buttons: ['copy', 'excel']
                        dom: 'lBfrtip',
                        buttons: {
                            buttons: [
                                { extend: 'copy', className: 'btn btn-warning' },
                                { extend: 'excelHtml5', className: 'btn btn-info' },
                                { extend: 'pdfHtml5', orientation: 'landscape', pageSize: 'A0', className: 'btn btn-success' }
                                // {
                                //     extend: 'colvis', columns: ':not(.notToggleVis)'
                                // }
                            ]
                            // dom: {
                            //     button: {
                            //         className: 'btn'
                            //     }
                            // }
                        }
                    });
                    table.buttons().container()
                        .appendTo($('.dataTables_length', table.table().container()));

                    //add data and draw
                    table.rows.add(response).draw();
                    $("#loader").hide();

                });

Below are the .js files that are being loaded:

<script src="static/js/datatables.min.js" defer></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.js"></script>
<script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.2/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/1.6.2/js/buttons.colVis.min.js"></script>

If i remove last 2 .js files, dataTables.buttons.min.js and buttons.colVis.min.js everything works as expected.
I even tried to build the .js using DataTable downloader given below:

<script type="text/javascript"
    src="https://cdn.datatables.net/v/bs4/jszip-2.5.0/dt-1.10.21/af-2.3.5/b-1.6.3/b-colvis-1.6.3/b-flash-1.6.3/b-html5-1.6.3/b-print-1.6.3/cr-1.5.2/fc-3.3.1/fh-3.1.7/kt-2.5.2/r-2.2.5/rg-1.1.2/rr-1.2.7/sc-2.0.2/sp-1.1.1/sl-1.3.1/datatables.js"></script>

Below is my finished project without 'colvis' button:

Can someone please help me in figuring out what am i missing when it comes to loading colvis button? Any help will be appreciated.

Thanks,
Rahul

Replies

  • colincolin Posts: 12,911Questions: 0Answers: 2,200

    That error is normally because Buttons is being loaded before DataTables, which doesn't appear to be the case in your code. Everything looks as it should, so please can you link to your page so we can debug it,

    Colin

  • rahulsaxenarahulsaxena Posts: 9Questions: 0Answers: 0

    Hi Colin,

    How should i link my page here? Sorry i am new to this platform.

    Thanks,
    Rahul

  • rahulsaxenarahulsaxena Posts: 9Questions: 0Answers: 0
    edited August 2020

    Hi Colin,

    You can check my code here: https://jsfiddle.net/rahulsaxena015/9dv1e2r8/6/
    Link for base.html: https://jsfiddle.net/rahulsaxena015/0q8rwo6g/2/

    Thanks,
    Rahul

  • rahulsaxenarahulsaxena Posts: 9Questions: 0Answers: 0
    {% extends 'base.html' %}
    
    {% block head %}
    <!-- <link href="https://datatables.net/download/build/nightly/jquery.dataTables.css" rel="stylesheet" type="text/css" /> -->
    <link rel="stylesheet" href="static/css/bootstrap.min.css"
        integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <link rel="stylesheet" type="text/css"
        href="https://cdn.datatables.net/v/dt/jq-3.3.1/jszip-2.5.0/dt-1.10.21/b-1.6.2/b-html5-1.6.2/datatables.min.css" />
    <link rel="stylesheet" type="text/css" href="static/css/dataTables.bootstrap4.min.css" />
    <link href="https://cdn.datatables.net/buttons/1.5.6/css/buttons.bootstrap4.min.css" rel="stylesheet">
    <link rel="stylesheet" type="text/css"
        href="https://cdn.datatables.net/v/bs4/jszip-2.5.0/dt-1.10.21/af-2.3.5/b-1.6.3/b-colvis-1.6.3/b-flash-1.6.3/b-html5-1.6.3/b-print-1.6.3/cr-1.5.2/fc-3.3.1/fh-3.1.7/kt-2.5.2/r-2.2.5/rg-1.1.2/rr-1.2.7/sc-2.0.2/sp-1.1.1/sl-1.3.1/datatables.css" />
    <!-- <script src="static/js/bootstrap.min.js"
        integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM"
        crossorigin="anonymous"></script> -->
    <script src="static/js/datatables.min.js" defer></script>
    <script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.5.6/js/dataTables.buttons.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.5.6/js/buttons.bootstrap4.min.js"></script>
    <script src="static/js/ace.js"></script>
    <script src="static/js/ext-language_tools.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
    <script type="text/javascript"
        src="https://cdn.datatables.net/v/bs4/jszip-2.5.0/dt-1.10.21/af-2.3.5/b-1.6.3/b-colvis-1.6.3/b-flash-1.6.3/b-html5-1.6.3/b-print-1.6.3/cr-1.5.2/fc-3.3.1/fh-3.1.7/kt-2.5.2/r-2.2.5/rg-1.1.2/rr-1.2.7/sc-2.0.2/sp-1.1.1/sl-1.3.1/datatables.js"></script>
    <style>
        #editor {
            /* position: relative; */
            height: 100px;
            width: 1828px
        }
    </style>
    {% endblock %}
    
    {% block body %}
    <br>
    <!-- <div class="jumbotron text-center hoverable p-4" id="jumbotron" style="background-color:#cecece"> -->
    <div class="jumbotron text-center hoverable p-4" id="jumbotron" style="background-color: rgb(222, 224, 225)">
        <form action='/query' method='POST' name="myForm">
            <select class="form-control" id="cluster" name="cluster" searchable="Search here..">
                <option value="" selected>Select Cluster</option>
                <option value="qe">QE</option>
                <option value="na2">NA2 </option>
                <option value="na3">NA3</option>
                <option value="na4">NA4</option>
                <option value="na5">NA5</option>
            </select>
            <br>
            <br>
            <div id="editor" name="editor"></div>
            <textarea class="form-control" name="sqlquery" id="sqlquery" placeholder="Enter Your SQL Query Here!!!"
                aria-label="Enter Your SQL Query Here" rows="3" style="display:none;"></textarea>
            <br>
            <button class="btn btn-success btn-ladda" data-style="expand-left" type="button" id="btnSubmit">Execute</button>
            <input type="reset" value="Reset" class="btn btn-primary" onClick="window.location.reload()">
        </form>
        <br>
        <div id="table2">
            <table id="example" class="display cell-border" width="100%"></table>
        </div>
    </div>
    <br>
    
    
    <!-- Image loader -->
    <div id="loader" style="display:none;position:relative;margin:auto;top:50%;left:50%;padding:2px;">
        <img src='static/images/loader.gif' height="50" width="50" /></div>
    
    <script type="text/javascript">
        var editor = ace.edit("editor");
        editor.setTheme("ace/theme/sqlserver");
        editor.getSession().setMode("ace/mode/sql");
        var textarea = $('textarea[name="sqlquery"]');
        editor.getSession().on("change", function () {
            textarea.val(editor.getSession().getValue());
        });
        editor.setShowPrintMargin(false);
        editor.setFontSize("18px");
        editor.setOptions({
            enableBasicAutocompletion: true,
            enableSnippets: true,
            enableLiveAutocompletion: true
        });
    
        function validate() {
            if (document.myForm.cluster.value == "") {
                alert("Please select cluster!!!");
                return false
            }
            else if (document.myForm.sqlquery.value == "") {
                alert("Please type SQL Query!!");
                document.myForm.sqlquery.focus();
                return false
            }
            return true
        }
    
        // function myerror() {
        var myerror = '{{ err }}';
        //     if (myerror !== "") {
        //         alert("There was an error processing your query. Please validate again.")
        //         return false
        //     }
        //     return true
        // }
    
        $(document).ready(function () {
    
            $("#btnSubmit").click(function (event) {
                if ($.trim($('#cluster').val()) == '') {
                    alert("Please select Cluster");
                    $('#cluster').focus();
                    return false
                }
    
                else if ($.trim($('#sqlquery').val()) == '') {
                    alert("Please enter query");
                    $('#sqlquery').focus();
                    return false
                }
    
                else {
                    $("#loader").show();
                }
                // if (validate()) {
                //     $("#loader").show();
                // }
                if ($.fn.DataTable.isDataTable("#example")) {
                    $('#example').DataTable().clear().destroy();
                    $("#example").empty();
                }
    
                // $('#example').DataTable().clear().draw();
                $.ajax({
                    data: {
                        cluster: $('#cluster').val(),
                        sqlquery: $('#sqlquery').val()
                    },
                    type: 'POST',
                    url: "{{url_for('run_query')}}"
                })
                    .done(function (response) {
                        console.log(response)
                        var cols = [];
    
                        var exampleRecord = response[0];
    
                        //get keys in object. This will only work if your statement remains true that all objects have identical keys
                        var keys = Object.keys(exampleRecord);
    
                        //for each key, add a column definition
                        keys.forEach(function (k) {
                            cols.push({
                                title: k,
                                data: k
                                //optionally do some type detection here for render function
                            });
                        });
    
                        //initialize DataTables
                        var table = $('#example').DataTable({
                            columns: cols,
                            "scrollY": 500,
                            "scrollX": true,
                            dom: 'lBfrtip',
                            // 'dom': "<'row'<'col-sm-12 col-md-6'Bl><'col-sm-12 col-md-6'f>><'row'<'col-sm-12'tr>><'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
                            buttons: ['copy', 'excel', 'pdf']
                            // buttons: {
                            //     buttons: [
                            //         { extend: 'copy', className: 'btn btn-warning' },
                            //         { extend: 'excelHtml5', className: 'btn btn-info' },
                            //         { extend: 'pdfHtml5', orientation: 'landscape', pageSize: 'A0', className: 'btn btn-success' }
                            //     ]
                            //     // dom: {
                            //     //     button: {
                            //     //         className: 'btn'
                            //     //     }
                            //     // }
                            // }
                        });
                        table.buttons().container().appendTo('#example_wrapper .col-md-6:eq(0)');
                        // table.buttons().container()
                        //     .appendTo($('.dataTables_length', table.table().container()));
    
                        //add data and draw
                        table.rows.add(response).draw();
                        $("#loader").hide();
    
                    });
            });
        });
    
    </script>
    
    {% endblock %}
    
  • rahulsaxenarahulsaxena Posts: 9Questions: 0Answers: 0
    {% extends 'base.html' %}
    
    {% block head %}
    <!-- <link href="https://datatables.net/download/build/nightly/jquery.dataTables.css" rel="stylesheet" type="text/css" /> -->
    <link rel="stylesheet" href="static/css/bootstrap.min.css"
        integrity="sha384-ggOyR0iXCbMQv3Xipma34MD+dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
    <link rel="stylesheet" type="text/css"
        href="https://cdn.datatables.net/v/dt/jq-3.3.1/jszip-2.5.0/dt-1.10.21/b-1.6.2/b-html5-1.6.2/datatables.min.css" />
    <link rel="stylesheet" type="text/css" href="static/css/dataTables.bootstrap4.min.css" />
    <link href="https://cdn.datatables.net/buttons/1.5.6/css/buttons.bootstrap4.min.css" rel="stylesheet">
    <link rel="stylesheet" type="text/css"
        href="https://cdn.datatables.net/v/bs4/jszip-2.5.0/dt-1.10.21/af-2.3.5/b-1.6.3/b-colvis-1.6.3/b-flash-1.6.3/b-html5-1.6.3/b-print-1.6.3/cr-1.5.2/fc-3.3.1/fh-3.1.7/kt-2.5.2/r-2.2.5/rg-1.1.2/rr-1.2.7/sc-2.0.2/sp-1.1.1/sl-1.3.1/datatables.css" />
    <!-- <script src="static/js/bootstrap.min.js"
        integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM+B07jRM"
        crossorigin="anonymous"></script> -->
    <script src="static/js/datatables.min.js" defer></script>
    <script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.5.6/js/dataTables.buttons.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/1.5.6/js/buttons.bootstrap4.min.js"></script>
    <script src="static/js/ace.js"></script>
    <script src="static/js/ext-language_tools.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/pdfmake.js"></script>
    <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.36/vfs_fonts.js"></script>
    <script type="text/javascript"
        src="https://cdn.datatables.net/v/bs4/jszip-2.5.0/dt-1.10.21/af-2.3.5/b-1.6.3/b-colvis-1.6.3/b-flash-1.6.3/b-html5-1.6.3/b-print-1.6.3/cr-1.5.2/fc-3.3.1/fh-3.1.7/kt-2.5.2/r-2.2.5/rg-1.1.2/rr-1.2.7/sc-2.0.2/sp-1.1.1/sl-1.3.1/datatables.js"></script>
    <style>
        #editor {
            /* position: relative; */
            height: 100px;
            width: 1828px
        }
    </style>
    {% endblock %}
    
    {% block body %}
    <br>
    <!-- <div class="jumbotron text-center hoverable p-4" id="jumbotron" style="background-color:#cecece"> -->
    <div class="jumbotron text-center hoverable p-4" id="jumbotron" style="background-color: rgb(222, 224, 225)">
        <form action='/query' method='POST' name="myForm">
            <select class="form-control" id="cluster" name="cluster" searchable="Search here..">
                <option value="" selected>Select Cluster</option>
                <option value="qe">QE</option>
                <option value="na2">NA2 </option>
                <option value="na3">NA3</option>
                <option value="na4">NA4</option>
                <option value="na5">NA5</option>
            </select>
            <br>
            <br>
            <div id="editor" name="editor"></div>
            <textarea class="form-control" name="sqlquery" id="sqlquery" placeholder="Enter Your SQL Query Here!!!"
                aria-label="Enter Your SQL Query Here" rows="3" style="display:none;"></textarea>
            <br>
            <button class="btn btn-success btn-ladda" data-style="expand-left" type="button" id="btnSubmit">Execute</button>
            <input type="reset" value="Reset" class="btn btn-primary" onClick="window.location.reload()">
        </form>
        <br>
        <div id="table2">
            <table id="example" class="display cell-border" width="100%"></table>
        </div>
    </div>
    <br>
    
    
    <!-- Image loader -->
    <div id="loader" style="display:none;position:relative;margin:auto;top:50%;left:50%;padding:2px;">
        <img src='static/images/loader.gif' height="50" width="50" /></div>
    
    <script type="text/javascript">
        var editor = ace.edit("editor");
        editor.setTheme("ace/theme/sqlserver");
        editor.getSession().setMode("ace/mode/sql");
        var textarea = $('textarea[name="sqlquery"]');
        editor.getSession().on("change", function () {
            textarea.val(editor.getSession().getValue());
        });
        editor.setShowPrintMargin(false);
        editor.setFontSize("18px");
        editor.setOptions({
            enableBasicAutocompletion: true,
            enableSnippets: true,
            enableLiveAutocompletion: true
        });
    
        function validate() {
            if (document.myForm.cluster.value == "") {
                alert("Please select cluster!!!");
                return false
            }
            else if (document.myForm.sqlquery.value == "") {
                alert("Please type SQL Query!!");
                document.myForm.sqlquery.focus();
                return false
            }
            return true
        }
    
        // function myerror() {
        var myerror = '{{ err }}';
        //     if (myerror !== "") {
        //         alert("There was an error processing your query. Please validate again.")
        //         return false
        //     }
        //     return true
        // }
    
        $(document).ready(function () {
    
            $("#btnSubmit").click(function (event) {
                if ($.trim($('#cluster').val()) == '') {
                    alert("Please select Cluster");
                    $('#cluster').focus();
                    return false
                }
    
                else if ($.trim($('#sqlquery').val()) == '') {
                    alert("Please enter query");
                    $('#sqlquery').focus();
                    return false
                }
    
                else {
                    $("#loader").show();
                }
                // if (validate()) {
                //     $("#loader").show();
                // }
                if ($.fn.DataTable.isDataTable("#example")) {
                    $('#example').DataTable().clear().destroy();
                    $("#example").empty();
                }
    
                // $('#example').DataTable().clear().draw();
                $.ajax({
                    data: {
                        cluster: $('#cluster').val(),
                        sqlquery: $('#sqlquery').val()
                    },
                    type: 'POST',
                    url: "{{url_for('run_query')}}"
                })
                    .done(function (response) {
                        console.log(response)
                        var cols = [];
    
                        var exampleRecord = response[0];
    
                        //get keys in object. This will only work if your statement remains true that all objects have identical keys
                        var keys = Object.keys(exampleRecord);
    
                        //for each key, add a column definition
                        keys.forEach(function (k) {
                            cols.push({
                                title: k,
                                data: k
                                //optionally do some type detection here for render function
                            });
                        });
    
                        //initialize DataTables
                        var table = $('#example').DataTable({
                            columns: cols,
                            "scrollY": 500,
                            "scrollX": true,
                            dom: 'lBfrtip',
                            // 'dom': "<'row'<'col-sm-12 col-md-6'Bl><'col-sm-12 col-md-6'f>><'row'<'col-sm-12'tr>><'row'<'col-sm-12 col-md-5'i><'col-sm-12 col-md-7'p>>",
                            buttons: ['copy', 'excel', 'pdf']
                            // buttons: {
                            //     buttons: [
                            //         { extend: 'copy', className: 'btn btn-warning' },
                            //         { extend: 'excelHtml5', className: 'btn btn-info' },
                            //         { extend: 'pdfHtml5', orientation: 'landscape', pageSize: 'A0', className: 'btn btn-success' }
                            //     ]
                            //     // dom: {
                            //     //     button: {
                            //     //         className: 'btn'
                            //     //     }
                            //     // }
                            // }
                        });
                        table.buttons().container().appendTo('#example_wrapper .col-md-6:eq(0)');
                        // table.buttons().container()
                        //     .appendTo($('.dataTables_length', table.table().container()));
    
                        //add data and draw
                        table.rows.add(response).draw();
                        $("#loader").hide();
    
                    });
            });
        });
    
    </script>
    
    {% endblock %}
    
  • colincolin Posts: 12,911Questions: 0Answers: 2,200

    Thanks for the code, but we really need to be seeing it fail to be able to debug. Just seeing the code doesn't help with that.

    Colin

  • rahulsaxenarahulsaxena Posts: 9Questions: 0Answers: 0

    How do you want me to share the required details? Can you please let me know?

  • tangerinetangerine Posts: 3,144Questions: 27Answers: 366

    Provide a link to a test case showing the issue.

  • colincolin Posts: 12,911Questions: 0Answers: 2,200

    We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

    Cheers,

    Colin

  • rahulsaxenarahulsaxena Posts: 9Questions: 0Answers: 0

    Thanks guys for the help. I just figured out the issue. Let me explain it. I created a similar page in my test environment and i could see COLVIS button. I then compared the network tab for both the environments and compared the js files that loaded. In my working app i found two instances of .js file one being .js and other being .min.js

    <!-- <script src="static/js/datatables.min.js" defer></script> -->
    <script type="text/javascript"
        src="https://cdn.datatables.net/v/bs4/jszip-2.5.0/dt-1.10.21/af-2.3.5/b-1.6.3/b-colvis-1.6.3/b-flash-1.6.3/b-html5-1.6.3/b-print-1.6.3/cr-1.5.2/fc-3.3.1/fh-3.1.7/kt-2.5.2/r-2.2.5/rg-1.1.2/rr-1.2.7/sc-2.0.2/sp-1.1.1/sl-1.3.1/datatables.js"
        defer></script>
    

    As you can see i commented out the first one and that resolved the issue.

  • rahulsaxenarahulsaxena Posts: 9Questions: 0Answers: 0
    edited August 2020

    Although i get the button but under drop down i don't see any column. Do you guys have an idea what could have caused it? I don't see any error in the browser now.

  • rahulsaxenarahulsaxena Posts: 9Questions: 0Answers: 0
    edited August 2020

    i found the issue to be with .css file. Dropdown now has all the columns.

  • galaxysailorgalaxysailor Posts: 2Questions: 1Answers: 0

    hi @rahulsaxena could you please tell me how the css file disrupted your your columns from showing

Sign In or Register to comment.