Ajax loading issue

rorypostulas

Hi Allan,

I'm a noob to DataTables and I am in total awe of the amazing quality of it all. Thanks for all the amazing hard work.

I want to create a table dynamically from data from a server.

Essentially I want to do this except with json from a server:
http://datatables.net/examples/data_sources/js_array.html

I've successfully used the following technique (works perfectly!!):
[code]
var candidatesDataTable;
$(document).ready(function() {
$('#demo').html( '' );
$.getJSON( '/demos/candidateDataTables.json', null, function( json ) {
candidatesDataTable = $('#example').dataTable( json );
});

});
[/code]

However, I just cannot get the following to work:
[code]
var candidatesDataTable;
$(document).ready(function() {
$('#demo').html( '' );
candidatesDataTable = $('#example').dataTable({
"sAjaxSource": '/demos/candidateDataTables.json'
});
});
[/code]

Both should be the exact same right?

I get the following error with the second version:
[code]
Uncaught TypeError: Cannot read property 'asSorting' of undefined
[/code]

I've see a similar thread but I couldn't understand how it fully applies:
http://datatables.net/forums/discussion/3012/error-in-google-chrome/p1

Here is my json:
[code]
{
"aaData": [
["ANTRIM","19","32","49","156","17","159","0"],
["ARMAGH","2","10","4","19","2","8","0"],
["CARLOW","6","33","18","7","3","71","0"],
["CAVAN","10","67","20","7","6","59","0"],
["CLARE","17","56","47","7","16","104","0"],
["CORK","103","440","323","78","96","1115","0"],
["DERRY","5","14","11","47","5","34","0"],
["DONEGAL","23","103","44","38","20","154","0"],
["DOWN","21","91","34","40","14","122","0"],
["DUBLIN","543","1589","1478","272","485","6263","0"],
["FERMANAGH","0","5","0","6","0","1","0"],
["GALWAY","32","175","343","27","38","530","0"],
["KERRY","14","57","54","22","14","170","0"],
["KILDARE","31","146","73","22","24","339","0"],
["KILKENNY","10","56","27","7","13","128","0"],
["LAOIS","7","35","13","6","7","72","0"],
["LEITRIM","1","24","11","1","3","37","0"],
["LIMERICK","30","110","78","16","22","275","0"],
["LONGFORD","3","12","9","2","6","31","0"],
["LOUTH","5","31","18","11","6","66","0"],
["MAYO","28","68","49","9","14","120","0"],
["MEATH","17","75","58","20","16","231","0"],
["MONAGHAN","4","26","11","9","3","23","0"],
["OFFALY","5","22","17","5","2","54","0"],
["ROSCOMMON","5","21","13","4","3","44","0"],
["SLIGO","12","59","21","7","6","100","0"],
["TIPPERARY","14","74","46","15","15","161","0"],
["TYRONE","2","4","5","25","2","9","0"],
["WATERFORD","12","75","43","15","13","180","0"],
["WESTMEATH","3","18","13","2","2","33","0"],
["WEXFORD","13","49","42","18","13","172","0"],
["WICKLOW","15","54","47","10","11","194","0"],
["Ireland (County not specified)","226","892","646","197","162","2460","0"],
["Outside Ireland","30","59","75","22","14","295","0"],
["Unknown","630","1916","1936","932","571","7821","0"],
["Total","1898","6498","5676","2081","1644","21635","0"]
],

"aoColumns": [
{
"sTitle": "Region"
},
{
"sTitle": "Mary Davis"
},
{
"sTitle": "Seán Gallagher"
},
{
"sTitle": "Michael D. Higgins"
},
{
"sTitle": "Martin McGuinness"
},
{
"sTitle": "Gay Mitchell"
},
{
"sTitle": "David Norris"
},
{
"sTitle": "Dana Rosemary Scallon"
}
]
}
[/code]


Kind regards,
Rory

allan

In this code:

[code]
var candidatesDataTable;
$(document).ready(function() {
$('#demo').html( '' );
candidatesDataTable = $('#example').dataTable({
"sAjaxSource": '/demos/candidateDataTables.json'
});
});
[/code]

there are no columns defined, so DataTables is having a problem with that fact. sAjaxSource only allows the row/cell information to be loaded in, not aoColumns (or any other parameter - just aaData) - hence the problem you are hitting.

So basically you need to either defined the columns first, or load it up with a $.getJSON as you were.

Allan

rorypostulas

Thanks Allan.

What I really like is that my json is defining both the columns and the data rows. When I use getJSON the DataTable works perfectly alright. Is it totally wrong to allow aoColumns get used with sAjaxSource? (sorry, total noob question).

The overall issue is that when I instantate the table I also wanted to set other settings not just my JSON data.

[code]
$('#example').dataTable( {"bPaginate": false,
"bLengthChange": false,
"bFilter": false,
"bSort": false,
"bInfo": false,
"bAutoWidth": false,
"bProcessing": true,
"sAjaxSource": '/demos/candidateDataTables.json'})
[/code]

What I've resorted to is putting the other parameters in my json file like so (which isn't too pretty but it works):
[code]
{
"bPaginate": false,
"bLengthChange": false,
"bFilter": false,
"bSort": false,
"bInfo": false,
"bAutoWidth": false,
"bProcessing": true,
"aaData": [
["ANTRIM","19","32","49","156","17","159","0"],
["ARMAGH","2","10","4","19","2","8","0"],
["CARLOW","6","33","18","7","3","71","0"],
["CAVAN","10","67","20","7","6","59","0"],
["CLARE","17","56","47","7","16","104","0"],
["CORK","103","440","323","78","96","1115","0"],
["DERRY","5","14","11","47","5","34","0"],
["DONEGAL","23","103","44","38","20","154","0"],
["DOWN","21","91","34","40","14","122","0"],
["DUBLIN","543","1589","1478","272","485","6263","0"],
["FERMANAGH","0","5","0","6","0","1","0"],
["GALWAY","32","175","343","27","38","530","0"],
["KERRY","14","57","54","22","14","170","0"],
["KILDARE","31","146","73","22","24","339","0"],
["KILKENNY","10","56","27","7","13","128","0"],
["LAOIS","7","35","13","6","7","72","0"],
["LEITRIM","1","24","11","1","3","37","0"],
["LIMERICK","30","110","78","16","22","275","0"],
["LONGFORD","3","12","9","2","6","31","0"],
["LOUTH","5","31","18","11","6","66","0"],
["MAYO","28","68","49","9","14","120","0"],
["MEATH","17","75","58","20","16","231","0"],
["MONAGHAN","4","26","11","9","3","23","0"],
["OFFALY","5","22","17","5","2","54","0"],
["ROSCOMMON","5","21","13","4","3","44","0"],
["SLIGO","12","59","21","7","6","100","0"],
["TIPPERARY","14","74","46","15","15","161","0"],
["TYRONE","2","4","5","25","2","9","0"],
["WATERFORD","12","75","43","15","13","180","0"],
["WESTMEATH","3","18","13","2","2","33","0"],
["WEXFORD","13","49","42","18","13","172","0"],
["WICKLOW","15","54","47","10","11","194","0"],
["Ireland (County not specified)","226","892","646","197","162","2460","0"],
["Outside Ireland","30","59","75","22","14","295","0"],
["Unknown","630","1916","1936","932","571","7821","0"],
["Total","1898","6498","5676","2081","1644","21635","0"]
],

"aoColumns": [
{
"sTitle": "Region"
},
{
"sTitle": "Mary Davis"
},
{
"sTitle": "Seán Gallagher"
},
{
"sTitle": "Michael D. Higgins"
},
{
"sTitle": "Martin McGuinness"
},
{
"sTitle": "Gay Mitchell"
},
{
"sTitle": "David Norris"
},
{
"sTitle": "Dana Rosemary Scallon"
}
]
}
[/code]

jcrawford

I believe the stuff you added to your JSON should be in your actual data table instantiation along with your columns. Doing it this way allows your source to only return the data and the table will know how to use it. For an example here is some code that I have.

Javascript
[code]
var oTable = $('#resourcelog').dataTable( {
"bProcessing": true,
"bFilter": false,
"bServerSide": true,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"sAjaxSource": "/admin/resourceajax/?resource=<?=$this->resource;?>&start_date=<?=$default_start_date?>&end_date=<?=$default_end_date?>",
"aaSorting": [[ 1, "desc" ]],
"oLanguage": {
"sZeroRecords": "No transactions found."
},
} );
[/code]

HTML
[code]



Transaction ID
Transaction Date
Transaction Code
Amount
Balance
Description




Loading data from server



[/code]

This seems to work perfectly fine for me and my JSON output looks like the following:

request from datatables to server: https://host/admin/resourceajax/?resource=bike_bucks&start_date=2011-09-29&end_date=2011-10-06&sEcho=1&iColumns=6&sColumns=&iDisplayStart=0&iDisplayLength=10&mDataProp_0=0&mDataProp_1=1&mDataProp_2=2&mDataProp_3=3&mDataProp_4=4&mDataProp_5=5&iSortingCols=1&iSortCol_0=1&sSortDir_0=desc&bSortable_0=true&bSortable_1=true&bSortable_2=true&bSortable_3=true&bSortable_4=true&bSortable_5=true

JSON RESPONSE FROM SERVER
[code]
{
"sEcho": 1,
"iTotalRecords": "5324",
"iTotalDisplayRecords": "1018",
"aaData": [
[
"10141",
"2011-10-03 15:07:06",
"CREDIT-PlayerServices",
"4978",
"1910",
"dummy data populated by load scr"
],
[
"10152",
"2011-10-03 15:05:44",
"CREDIT-Gifted",
"1966",
"539",
"dummy data populated by load scr"
],
[
"10350",
"2011-10-03 15:03:43",
"CREDIT-Purchased",
"7529",
"502",
"dummy data populated by load scr"
],
[
"10234",
"2011-10-03 15:01:09",
"DEBIT-PlayerServices",
"6422",
"3970",
"dummy data populated by load scr"
],
[
"10037",
"2011-10-03 14:45:49",
"CREDIT-Purchased",
"2694",
"947",
"dummy data populated by load scr"
],
[
"10185",
"2011-10-03 14:33:03",
"DEBIT-StorePurchase",
"2798",
"3862",
"dummy data populated by load scr"
],
[
"10091",
"2011-10-03 14:32:53",
"CREDIT-Purchased",
"6597",
"2664",
"dummy data populated by load scr"
],
[
"10773",
"2011-10-03 14:29:25",
"CREDIT-PlayerServices",
"6300",
"448",
"dummy data populated by load scr"
],
[
"10020",
"2011-10-03 14:25:46",
"DEBIT-PlayerServices",
"1549",
"3962",
"dummy data populated by load scr"
],
[
"10681",
"2011-10-03 14:19:46",
"CREDIT-LeveledUp",
"6894",
"1699",
"dummy data populated by load scr"
]
]
}
[/code]

rorypostulas

Many thanks for that. I want the table columns coming from JSON to create a fully dynamic table. Your example hard codes the table columns in HTML.

I do have everything working as long as I use jQuery to first load the JSON.

allan

> Is it totally wrong to allow aoColumns get used with sAjaxSource? (sorry, total noob question).

Perfectly valid question! In theory, no there is absolutely nothing wrong with this. However, practicality suggests otherwise :-(. DataTables currently doesn't have a method to add columns to a table on-the-fly, which would be needed here - it can only add columns during the initialisation. Since the Ajax return is async, the initialisation is already complete and the table ready to go when the data comes back - hence the problem. This is effectively a limitation in DataTables I'm afraid.

As such if you want to load your columns from Ajax you need to use the first method you showed. If this is too ugly in terms of the flash of content, one option would be to initialise an empty table and then destroy it when the getJSON return comes back.

Allan

Phunky

Sorry for bumping an old thread but i'm just looking at utilising DT myself and I was expecting that I could also load in the columns via the JSON response.

I'm expecting that this is still not possible, as I can't find anything on the forum to state other wise :)

allan

Yup - you need to make the Ajax call yourself to get the column information and then initiate the DataTables instance with that information.

Part of the the problem with allowing an Ajax loading of the initialisation options is that functions would not be allowed since they are not valid JSON.

Allan

rh0dium

Hey Allan / Rory,

Great post! I am facing the same situation.

Allan is there anything wrong with doing it this way? Could you show me how to initialise an empty table then reload it?

Rory what prompted you to want to change?



Thanks!