How can I access the data in a datatable using Ajax?

SGiou

I have been trying to retrieve all the data rows from a datatable into a javascript variable, but I've been unable to get it working so far.

The testData example in my code below works well, but when I try to retrieve data from the table using rows().data() the event listener doesn't work, so the script doesn't even get as far as outputing the error message (I used the inspector tool to check this). I realise that I must have something wrong with the syntax for retrieving the data, but I've tried different methods detailed in the API section of the documentation (https://datatables.net/reference/api/), and I can't seem to get them to work. My aim is to send back all of the data within the table to the url (including multiple edits), in the same format that it was received (a json string).

Javascript code 1 - declaring the datatable:

<script>

    var columns_all = {{columns_all|safe}};

    var myTable = $('#table1').dataTable({
            destroy: true,
            scrollX: true,
            scrollY: '50vh',
            scrollCollapse: true,
            paging: false,
            searching: true,
            sort: true,
            "columns": columns_all,
            "columnDefs": [
                {"className": "dt-center", "targets": "_all"}
            ],
            "ajax": {
                url: "/index_get_data",
                headers: {
                    'X-CSRF-TOKEN': '{{ csrf_token() }}'
                },
                "dataType": "json",
                "dataSrc": "data",
                "contentType": "application/json"
            },
            "dom": '<"dt-buttons"Bf><"clear">lirtp',
            "buttons": [
                {extend: 'copy', className: 'copyButton'},
                {extend: 'csv', className: 'csvButton'},
                {extend: 'excel', className: 'excelButton'},
                {extend: 'print', className: 'printButton'}
            ],
            "lengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],

            "select": true,
            "fixedColumns": {
                leftColumns: 4
            }
        });

</script>

Javascript code 2 - posting the data to a url

<script>


    var testData = {"schema": {"fields":[{"name":"index","type":"integer"},{"name":"OB_TIME","type":"datetime"},{"name":"LATITUDE","type":"number"},{"name":"LONGITUDE","type":"number"}]}, "data": [{"index":0,"OB_TIME":"2019-09-03T00:00:00.000Z","LATITUDE":21.3,"LONGITUDE":-158.0}]};

    var tableEdits = myTable.rows().data();

    document.getElementById('commitButton').onclick = function() {
        $.ajax({
            url: "/index_get_data",
            type:'POST',
            headers: {
                'X-CSRF-TOKEN': '{{ csrf_token() }}'
            },
            "dataType": "json",
            {#  "data": JSON.stringify(testData), #}
            "data": JSON.stringify(tableEdits),
            "contentType": "application/json; charset=utf-8",
            success: function(result) {
                alert("Your changes have been committed to the database");
            },
            error: function(result) {
                alert('error');
            }
        })
    };

</script>

Thanks for any help with this

kthorngren

Are you getting errors in your browser's console?

If you are getting something like API method is not available then checkout this FAQ. Line 5 of your first snippet probably needs to look like this: var myTable = $('#table1').DataTable({. Note the D in Datatable.

If this doesn't help then we will need to see a link to your page or a test case replicating the issue. Only looking at the code snippets above doesn't help us to help you debug the issue.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

SGiou

Hi Kevin, thanks for your answer. I tried changing dataTable to DataTable as you suggested, but it made no difference (the display of the data table is working well with both usages). I'll try to put a test case together soon. I'm not sure how to replicate getting the data from and posting the data to a url within a test case, but I'll make a version as close as possible to what I have set up.

SGiou

Also, I'm not getting any errors in the console. When I comment out the line var tableEdits = myTable.rows().data() and use the data2 example, the event listener works as expected, and outputs the success message. However, when using the tableEdits data call, the event listener is not created at all, so the code doesn't reach the error message when the commit button is clicked. I'm using Intellij, and in the code snippet: var tableEdits = myTable.rows().data(); the .data() part is in grey, like it's not recognised as a valid call (while the rest is coloured), which makes me think that I'm making a mistake in the method I'm using to access the data.

kthorngren

However, when using the tableEdits data call, the event listener is not created at all, so the code doesn't reach the error message when the commit button is clicked.

That suggests to me there is a Javascript error.

Without seeing an example I'm just guessing but maybe you have a variable scoping issue. I would start by adding the following to your second code snippet. This will get an API instance of the Datatable:

    var testData = {"schema": {"fields":[{"name":"index","type":"integer"},{"name":"OB_TIME","type":"datetime"},{"name":"LATITUDE","type":"number"},{"name":"LONGITUDE","type":"number"}]}, "data": [{"index":0,"OB_TIME":"2019-09-03T00:00:00.000Z","LATITUDE":21.3,"LONGITUDE":-158.0}]};
 
    var myTable = $('#table1').DataTable();   // Get the Datatable API.

    var tableEdits = myTable.rows().data();
    console.log(tableEdits);  // Debug statement.
....

Kevin

SGiou

Thanks Kevin. I did as you suggested, and I realised that I couldn't access the tableEdits from outwith the script containing myTable. I've been using separate scripts for declaring the DataTable and for posting the data in the table back to a url.

I've changed my code as follows, but I now get a cyclic object value/ circular reference error in the console.log(tableEdits); output

/* declaration of the DataTable and retrieving edited data using .rows().data() method */
<script>
        var columns_all = {{columns_all|safe}};

        var myTable = $('#table1').DataTable({
            destroy: true,
            scrollX: true,
            scrollY: '50vh',
            scrollCollapse: true,
            paging: false,
            searching: true,
            sort: true,
            "columns": columns_all,
            "columnDefs": [
                {"className": "dt-center", "targets": "_all"}
            ],
            "ajax": {
                url: "/index_get_data",
                headers: {
                    'X-CSRF-TOKEN': '{{ csrf_token() }}'
                },
                "dataType": "json",
                "dataSrc": "data",
                "contentType": "application/json"
            },
            "dom": '<"dt-buttons"Bf><"clear">lirtp',
            "buttons": [
                {extend: 'copy', className: 'copyButton'},
                {extend: 'csv', className: 'csvButton'},
                {extend: 'excel', className: 'excelButton'},
                {extend: 'print', className: 'printButton'}
            ],
            "lengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],

            "select": true,
            "fixedColumns": {
                leftColumns: 4
            }
        });

        var tableEdits = myTable.rows().data();

        console.log(JSON.stringify(tableEdits));

</script>

And the script to POST data back to the url:

<script>
        document.getElementById('commitButton').onclick = function() {
            {# alert("button was clicked" + (count++) + " times") #}
            $.ajax({
                url: "/index_get_data",
                type:'POST',
                headers: {
                    'X-CSRF-TOKEN': '{{ csrf_token() }}'
                },
                "dataType": "json",
                {#  "data": JSON.stringify(tableEdits),#}
                "data": JSON.stringify(tableEdits),
                "contentType": "application/json; charset=utf-8",
                success: function(result) {
                    alert("Your changes have been committed to the database");
                },
                error: function(result) {
                    alert('error in POST');
                }
            })
        };

</script>

To fix the cyclic error, I tried using the following function. This allows the tableEdits json string to be posted back to the url, but it's missing all the data, and the format seems to include data for the table structure (including the pdf/excel/print buttons):

                    function decycle(obj, stack = []) {
                        if (!obj || typeof obj !== 'object')
                            return obj;

                        if (stack.includes(obj))
                            return null;

                        let s = stack.concat([obj]);

                        return Array.isArray(obj)
                            ? obj.map(x => decycle(x, s))
                            : Object.fromEntries(
                                Object.entries(obj)
                                    .map(([k, v]) => [k, decycle(v, s)]));
                    }

Do you have any suggestions as to why the circular reference is happening? The datatable is displaying the data perfectly, and I only want to be able to get the data I can see in the table rows back into a json string.

Thanks for your help with this.

colin

This looks wrong:

        var columns_all = {{columns_all|safe}};

columns should be an array, not an object.

If that doesn't help, 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

SGiou

Thanks Colin, I'm using Flask, and columns_all is an array passed to the html through the route return statement. Jinja is used to access it here. It works well for displaying the data within the table. I'll try and put together a test case, but I think it will be difficult to replicate the issue as I can't replicate the passing of the json array to and from a url.

SGiou

I was able to fix this issue using

var tableEdits = myTable.rows().data().toArray;

(following this example: https://datatables.net/forums/discussion/21421/getting-data-back-out-of-a-datatable-and-submitting-it-as-json#Comment_62055)

I've created a simplified test case here: http://live.datatables.net/gowobin/246/edit

To make the table editable I've used contenteditable = 'true' in the html <table> declaration. I was thinking that the rows().data().toArray() api would retrieve the edited values, but it instead retrieves the original data that was passed to the table.

The table I will be using has over 100 columns, and I would like to be able to edit cells in multiple columns and rows, before committing the whole data set to an array. Is there a way to retrieve the edited dataset instead of the original? Thanks

colin

Hi @SGiou ,

I don't know how you're saving the table, whether in the DOM or through the API, so hard to advise. It sounds like you're recreating Editor, it would be worth considering that as an option.

Cheers,

Colin

SGiou

Thanks Colin. The edited data was getting saved in the DOM. I've managed to access it now. For this project I'm trying to use open-source methods as far as possible and my current application doesn't need the more advanced Editor features, but I have other projects in mind that I think Editor would be great for. I really appreciate how responsive this forum is, and all the work that's been put into DataTables.