Errors trying to Create an Index Column with two database columns (Mysql/Flask)

etilley · May 2020

This is the second datatable on a page. The first dataTable builds a list correctly and this one gives the error:

DATA:
The http://localhost:5000/ind_rankings/1 page returns a list of Countries and values:

[{"name":"Afghanistan","value":1.030660058},{"name":"Albania","value":4.1452379359},{"name":"Algeria","value":1.4},{"name":"American Samoa","value":2.1848739496}]

JAVASCRIPT

<script>

    function rankingList() {
      var t = $('#rankings').DataTable( {
          serverSide: true,
          ajax: '/ind_rankings/' + window.indid.toString(),            // This is the database call  
          // data: dataSet,
          columnDefs: [ {
              "searchable": false,
              "orderable": false,
              "targets": 0 
          }],
          scrollY: "200px",
          scrollCollapse: true,
          paging: false,
          columns: [
              // { title: "Index" },
              { title: "name" },
              { title: "value" }
          ],
          order: [[ 1, 'asc' ]]
      } );

      t.on( 'order.dt search.dt', function () {
          t.column(0, {search:'applied', order:'applied'}).nodes().each( function (cell, i) {
              cell.innerHTML = i+1;
          } );
      } ).draw();
    };

  </script>

HTML

<table id="rankings" class="display" style="width:100%">
                  <thead>
                    <tr>
                      <th>Rank</th>
                      <th>Country</th>
                      <th>Value</th>
                    </tr>
                  </thead>
                  <tfoot>
                    <tr>
                      <th>Rank</th>
                      <th>Country</th>
                      <th>Value</th>
                    </tr>
                </tfoot>
                </table>

Link to test case: I don't see a link to your debuggers on the Ask page... I will add it shortly if I can (15-minute edit limit is in effect)
Debugger code:
Error messages shown:
Description of problem:

rf1234 · May 2020

You are probably calling the function rankingList() on multiple occasions. Every time you do this Data Tables will try to (re)initialize your data table. If your second data table were outside a function the code would only be executed once and no problem would occur.

So if you really need your child table in a separate function you could use the "retrieve" option. Here is more on this:

https://datatables.net/reference/option/retrieve

https://datatables.net/forums/discussion/comment/169296/#Comment_169296

Search for this please:

The function with the Editor and the Data Table (part of it). If you are not destroying the data table if it exists - which is not required - make sure you set the "retrieve"
option.

etilley · May 2020

The table isn't assigning data to columns as expected - so there is clearly a syntax problem, but there isn't an example of a server-side database-called data list here on the forum.

@rf1234 - I can address multiple calls - and use "retrieve" for updates, but it should chart the first table correctly and its not. The syntax used to build the table isn't correct I don't think.

@kthorngren - Do you see the problem with this syntax?

tangerine · May 2020

@rf1234 is correct: you are initializing DataTables inside the function rankingList(), which is presumably called more than once. Hence the error "cannot reinitialise".

there isn't an example of a server-side database-called data list here on the forum.

The data source is irrelevant in this case.

Link to test case: I don't see a link to your debuggers on the Ask page.

Creating a test case is explained here:
https://datatables.net/forums/discussion/12899/post-test-cases-when-asking-for-help-please-read#latest

etilley · May 2020

Thanks @tangerine - I will pull out the other calls. It sounds like my script syntax is correct - and not the problem as I assumed. I assumed syntax was wrong because the table didn't draw correctly...

kthorngren · May 2020

There looks to be a few issues:

You have serverSide: true, which expects your server script to follow the protocol document in the Server Side Processing Docs. It doesn't look like your server script is responding with the expected data structure. See this FAQ to determine if you need server side processing.
I'm assuming you don't need server side processing. Datatables defaults to looking for the Ajax data in the data object as described in the Ajax docs. You don't have that so you will need to use ajax.dataSrc to point to the correct location. In your case it looks like the 2nd example in the docs is what you want.
You have 3 columns defined in HTML but only 2 in columns, one of them is commented out. Plus your data set is an array of objects so you will need to use columns.data. More info can be found in the Data docs.

There are a number of ways to resolve the Cannot Reinitialize error. The resolution needed is based on your requirements, like the need to change config options. Look at the technote in the error's link:
https://datatables.net/manual/tech-notes/3

There are numerous ways to update the Datatatbles data if this is your intent with the rankingList() function. Let us know what you are needing to do so we can provide guidance.

Kevin

etilley · May 2020

@kthorngren - Great corrections. These changes did allow me to get the second table running - but it didn't update its json data upon a row selection from the the first table.

I need this to reload based on a call - rankingList() or t.ajax.reload(), or similar.

I notice that @medusa101 did the same thing with a data.reload api - but I can't find a working script example.

    function rankingList() {
      // alert(window.indid);
      var t = $('#rankings').DataTable( {
          "ajax" : {
            "url": "/ind_rankings/" + window.indid.toString() ,
            "dataSrc": "",
            "dataType": "json",
            "contentType":"application/json"
          },
          "columns": [
            {"data": "top"},
            {"data": "bottom"},  
            {"data": "name"},
            {"data": "value"}
          ],

          "retrieve": true,

          "scrollY": "200px",
          "scrollCollapse": true,
          "paging": false

        } );
      };
      
//   This part doesn't 

      $('#rankings').change(function() {
        // warehouseIDReload = document.getElementById("selectWarehouse").value;
        //table.ajax.reload();
        t.ajax.url('/ind_rankings/' + window.indid.toString() ).load();
      } );

etilley · May 2020

http://live.datatables.net/taquhelo/2/

Here is the test case - json data format is above as I can't publish the url presently. I added a couple of numbered columns.

colin · May 2020

That test case doesn't execute - even if I change the order of the libraries it still doesn't load the table. Please can you provide a test case that demonstrates the issue you want support with.

Colin

etilley · May 2020

@colin Testcase doesn't execute because the code needs to pull json data from a public url ... which I don't have. I tried emulating with two datasets within the test case - but without success; the DataTables' syntax is very different for ajax data.

The best that I can show is the config above that draws the second table correctly - (this is also in the test case too).

I've basically tried every solution that is documented to work here (I think) without success.

I'm missing something small but important - and it might be a bootstrap4 related issue too. I've tried:

1) calling the function from the select row of the first table as follows

$('#example tbody').on( 'click', 'tr', function () {
      var table = $('#example').DataTable();
      var data = table.row( this ).data();
      window.indid=data.id;              // - this changes the ajax - window.indid.toString()
      rankingList();

2) reloading the second table everytime it is called - with jquery .change - table.ajax.reload() - as shown above

Neither approach refreshes the second table.

Maybe calling $('#rankings').on("submit") could work, but the first table would have to be forwarding a jquery-recognized submit

kthorngren · May 2020

Neither approach refreshes the second table.

Can you describe what happens? Do the click events work? Is the child table fetching data but not with an updated URL?

The URL is probably static once the Datatable is initialized. You may need to update the URL using ajax.url().load(). If this doesn't help then you will need to trace through the code to find where the problem might be to report back here.

Kevin

etilley · May 2020

@kthorngren Right again - the ajax.url().load() did refresh the second table. I was having a lot of database problems which delayed my reply ...

The only trouble that I am running into now is that the second table takes a few seconds to build - some of the time (whenever the database has to fetch data from an external api - and doesn't have the data cached locally yet).

The javascript's ajax call is much faster than the server-side background process.

Is there a trick (a ".wait" command for example) to delay the second table's ajax load until the flask server has built a database entry for the second table to call? Or, an "On Error then Retry" option? One that permits an ajax reload retry every 2 seconds in a loop so the ajax call can work?

Here is the second table's working syntax ... I call this function by adding a call to "ranking()" in the first table.

$('#example tbody').on( 'click', 'tr', function () {
      var table = $('#example').DataTable();
      var data = table.row( this ).data();
      ranking();
});

The Second Table (the one that needs to .wait for its ajax call)

function ranking() {
      //  alert("First Window.indid in function rankingList()", window.indid);
      var t2 = $('#rankings').DataTable( {
          "ajax" : {
            "url": "/ind_rankings/" + window.indid.toString() ,
            "dataSrc": "",
            "dataType": "json",
            "contentType":"application/json"
          },
          "columns": [
            {"data": "top"},
            {"data": "bottom"},
            {"data": "code"},
            {"data": "name"},
            {"data": "value"}
          ],
          "retrieve": true,
          "scrollY": "200px",
          "scrollCollapse": true,
          "paging": false
      } );
      // alert ("Window.indid: ", window.indid , "indid:", indid );
      t2.ajax.url( "/ind_rankings/" + window.indid.toString() ).load();

kthorngren · May 2020

The javascript's ajax call is much faster than the server-side background process.

Sounds like the ajax request is timing out, correct?

On option may be to use the jQuery Ajax() timeout option. The ajax option passes the parameter you define to jQuery ajax. More details can be found in the ajax docs. You might be able to use the timeout option.

Or you may be able to use the xhr event. I've never used it but according to the docs there is error information passed into the event. Sounds like you can determine the error and decide if you want to try another ajax.url().load(). You will need to be careful with this so you don't introduce an infinite loop of sending ajax requests on errors.

Not sure if any of these will work as I've not had the need to use them.

Kevin

kthorngren · May 2020

Was thinking about this a bit more. Your first call to ranking() will result in two ajax requests. You can use the $.fn.dataTable.isDataTable() API to determine if the Datatable has been initialized. Also inside that function you can create a one time xhr event which will eliminate the infinite loop I mentioned. Basically something like this:

function ranking() {
      //  alert("First Window.indid in function rankingList()", window.indid);
    
    if ( ! $.fn.DataTable.isDataTable( '#rankings' ) ) {
      var t2 = $('#rankings').DataTable( {
          "ajax" : {
            "url": "/ind_rankings/" + window.indid.toString() ,
            "dataSrc": "",
            "dataType": "json",
            "contentType":"application/json"
          },
          "columns": [
            {"data": "top"},
            {"data": "bottom"},
            {"data": "code"},
            {"data": "name"},
            {"data": "value"}
          ],
          "retrieve": true,
          "scrollY": "200px",
          "scrollCollapse": true,
          "paging": false
      } );
    } else {
      // alert ("Window.indid: ", window.indid , "indid:", indid );
      $('#rankings'). // Use .one() instead of .on() for a one time event that will 
           // happen after the ajax.url().load()
          .one('xhr.dt', function ( e, settings, json, xhr ) {
              // Check for error and if appropriate retry the ajax request with this:
             t2.ajax.url( "/ind_rankings/" + window.indid.toString() ).load();
          } );
      t2.ajax.url( "/ind_rankings/" + window.indid.toString() ).load();
    }

Kevin

Errors trying to Create an Index Column with two database columns (Mysql/Flask)

Errors trying to Create an Index Column with two database columns (Mysql/Flask)

Answers

Howdy, Stranger!

Categories

DataTables

Errors trying to Create an Index Column with two database columns (Mysql/Flask)

Errors trying to Create an Index Column with two database columns (Mysql/Flask)

Answers

Howdy, Stranger!

Quick Links

Categories

DataTables