Unable to use colvis button of DataTable

rahulsaxena

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

colin

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

rahulsaxena

Hi Colin,

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

Thanks,
Rahul

rahulsaxena

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

rahulsaxena

{% 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 %}

rahulsaxena

{% 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 %}

colin

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

rahulsaxena

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

tangerine

Provide a link to a test case showing the issue.

colin

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

rahulsaxena

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.

rahulsaxena

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.

rahulsaxena

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

galaxysailor

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