render with Ajax

montoyam

I have a table with a list of queries. One of the fields is the sql statement of the particular query. I would like to have a column of the data table return the count of records that query returns.

                {
                    data: null, title: "# of Records", defaultContent: '<getting record count>',
                    
                    render: function (data, type, row) {
                        if (type === 'display') {
                            var recCount=0;
                            console.log("data1", data.Statement)
                            if (data.Statement != null && data.Statement !='') {
                                console.log("getting count")
                                $.ajax({
                                    url: "api/DynamicReturn?readText=" + data.Statement,
                                    //async: false,
                                    success: function (data) {
                                        console.log("data2", data.data.length);
                                        recCount = data.data.length;
                                    }
                                })
                                return recCount;
                            } else {
                                return 'Not Developed yet';
                            }
                        }
                    }, 
                    className: "text-center"

                },

This works fine if I use async: false. However, it takes forever to load initially. What would be awesome is that when the ajax is done, it goes back and updates the cell (I can put a 'calculating...' placeholder or something while it is loading.

Is this possible?

and a side question.... this render is being called twice per row, why is that? Is there more than one 'type ===display'

kthorngren

and a side question.... this render is being called twice per row, why is that? Is there more than one 'type ===display'

It is called multiple times to handle the different orthogonal data operations. You can use console.log(type) to see this. Its possible that the display operation is called more than once.

What would be awesome is that when the ajax is done, it goes back and updates the cell (I can put a 'calculating...' placeholder or something while it is loading.

Its not recommended to use an ajax call in columns.render for the reasons you are seeing. You would need to update the cell in the success function. It is possible, but not recommended, to update the cell using the values from the meta parameter of columns.render.

I would look at using rowCallback instead. It executes only when the row is displayed and is meant for dynamic updates like this. I would also look at keeping track of the fetched data.Statement in an Object as a cache. Here is pseudo code:

var fetchedRecCounts = {};  // Global variable
....
  "rowCallback": function( row, data ) {

      // If statement not in fetchedRecCounts cache then fetch the data
      if ( ! fetchedRecCounts.hasOwnProperty( data.Statement );
           $.ajax({
                    url: "api/DynamicReturn?readText=" + data.Statement,
                    //async: false,
                    success: function (data) {
                        console.log("data2", data.data.length);
                        recCount = data.data.length;
                        $('td:eq(4)', row).html( recCount ); 
                        fetchedRecCounts[data.Statement] = recCount;
                    }
                })
      } else {
          // Otherwise just update from the cache
          $('td:eq(4)', row).html( fetchedRecCounts[data.Statement] ); 
     }
  }

This will need some work to make it work but should give you an idea. You can add something to display 'calculating...' in the cell before the ajax call and remove it the success function.

Kevin

kthorngren

Not sure this is feasible with what you are trying to do but a better option might be to fetch all the recCounts and build the fetchedRecCounts data structure when the table loads. Then all the data is at the client and quickly accessible when drawing the table data.

Kevin

montoyam

As always, you offered the perfect solution. thank you, it works nicely.

I am not sure what you mean by your last comment though.

kthorngren

Lets assume url: "api/DynamicReturn will return all the data, whatever that is, in a format that you can create an object with all the statements and their recCounts, for example:

fetchedRecCounts = {statement1: 5, statement2: 10,....};

The basic flow would look like this:

ajax: {
  url: "api/DynamicReturn,
  success: function (data) {
    // Process the data to build
    fetchedRecCounts = {statement1: 5, statement2: 10,....};
    
    // Init your datatable
  }
}

Now you can use columns.render or rowCallback to simply access the Javascript object fetchedRecCounts with data.Statement. IMHO its more efficient to fetch all the data up front and build some data structures for it than to individually fetch the data for each row. But your API might not support this or there might be too much data.

Kevin

montoyam

hmm. I will try and wrap my head around that method further

but, for now I am using your first suggestion. However, if I get an error trying to execute that dynamic sql statement my controller will return a -1. I then display a message instead of a record count

                if (!fetchedRecCounts.hasOwnProperty(data.RequestID)) {
                    if (data.Statement != '' && data.Statement != null) {
                        $.ajax({
                            url: "api/DynamicReturn?readText=" + data.Statement,
                            //async: false,
                            success: function (data) {
                                if (data.data[0] == -1) {
                                    $('td:eq(3)', row).html('Error with SQL');
                                } else {
                                    recCount = data.data.length;
                                    $('td:eq(3)', row).html(recCount);
                                    fetchedRecCounts[data.RequestID] = recCount;
                                }
                            }
                        })
                    }
                } else {
                    // Otherwise just update from the cache
                    $('td:eq(3)', row).html(fetchedRecCounts[data.RequestID]);
                }

But, later, when that row is selected, if it was a valid sql statement, I then show the results of the sql in a second data table.

        RequestsTable.on('select', function (e) {
            var r = RequestsTable.row({ selected: true })
            var d = RequestsTable.row({ selected: true }).data();
            console.log(r);
            var sql = d.Statement;
            if (sql == "" || sql == null ) {
                destroyResults();
                $("#LoadingMessage").html("This request has not been developed yet");
            } else {
                if (r.column(3) != "Error with SQL") {
                    drawDataView(sql);
                    var caption = d.RequestDescription || d.RequestTitle; 
                    $('#APIResults').append('<caption  class="top">' + caption + '</caption>');
                }
            }
        });

My problem is, since the column with the record count is a rendered column, it is not in row().data(). I am looking at what is in row() but not seeing the rendered data. What I was going to do is if the 3rd column does not 'Error with SQL' then I would load that second data table.

montoyam

ah, i forgot that the data was stored in an array if the sql statement was successful I ended up using that

if (!fetchedRecCounts.hasOwnProperty(d.RequestID)) {