Replies

• allan Posts: 63,523Questions: 1Answers: 10,473 Site admin

  October 2011

  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 Posts: 3Questions: 0Answers: 0

  October 2011

  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 Posts: 172Questions: 0Answers: 0

  October 2011 edited October 2011

  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 Posts: 3Questions: 0Answers: 0

  October 2011 edited October 2011

  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 Posts: 63,523Questions: 1Answers: 10,473 Site admin

  October 2011

  > 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 Posts: 1Questions: 0Answers: 0

  May 2012

  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 Posts: 63,523Questions: 1Answers: 10,473 Site admin

  May 2012

  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 Posts: 14Questions: 0Answers: 0

  December 2012 edited December 2012

  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!