Dynamic columns and data types

[LokoParticipant]

Hi

I believe there is an issue with dynamic columns, i.e source retrieve from url that does not always returns the same columns number.

In your demo : http://www.jqwidgets.com/jquery-widgets-demo/demos/jqxgrid/index.htm?%28arctic%29#demos/jqxgrid/dynamiccolumns.htm

you hardcoded the columns in the gridAdapter so it is not dynamic :

var gridAdapter = new $.jqx.dataAdapter({
                        datafields: [
                        { name: 'id', type: 'number' },
                        { name: 'name', type: 'string' },
                        { name: 'type', type: 'string' },
                        { name: 'calories', type: 'int' },
                        { name: 'totalfat', type: 'string' },
                        { name: 'protein', type: 'string' }
                        ],
                        id: 'id',
                        localdata: rows
                    });

My issue is I have a server script that returns dynamic columns and rows, for instance :

[ { "columns": [ 
{ "text": "Url", "datafield": "Url" } ,
{ "text": "2015-10", "datafield": "2015-10" } ,
{ "text": "2015-11", "datafield": "2015-11" } ] 
}, 
{ "rows": [ 
{"Url": "/db/Fiche_chantier/","2015-10": "0","2015-11": "12"},
{"Url": "/db/fiche-commande/","2015-10": "0","2015-11": "2"}
]
}
]

And on the client side : `
source = {
datatype: “json”,
url: ‘http://…’,
data: {},
type: ‘POST’
};

var dataAdapter = new $.jqx.dataAdapter(source, {
autoBind: true,
downloadComplete: function (data) {
var columns = data[0].columns;
var rows = data[1].rows;
var gridAdapter = new $.jqx.dataAdapter({
id: ‘Url’,
localdata: rows
});
…
`

The grid shows correctly but as it is not possible in the grid adapter to specify the columns types , sorting is not correct for columns ‘2015-10″ and “2015-11”, they contain integers but by default jqxgrids sorts them alphabetically.

I tried to add “datatype”:”int” and “type”:”int” in the columns definition but it does not work.

How can we specify types for dynamic columns ?

Thank you
Loko

[DimitarParticipant]

Hi Loko,

It does not matter if the URL does not return the same number of columns each time. I guess you know what columns it can return (the available pool of columns). If this is so, you can define all these columns and their types in the datafields array, but only display the loaded ones.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[LokoParticipant]

Hi Dimitar

No I don’t know what columns it can return, is it true dynamic columns, 1 per incoming month, so they will never have the same name.
I can’t imagine to hardcode in the dataAdapter columns from, let’s say, now to year 2100 …

[DimitarParticipant]

Hi Loko,

Unfortunately, we cannot offer you any other solution. The datafields definitions are necessary for the grid to “know” what type of data is displayed in each column and set the sorting options accordingly.

Best Regards,
Dimitar

jQWidgets team
http://www.jqwidgets.com/

[LokoParticipant]

Hi Dimitar

I think it is not for the client side (display) to decide/tell the data types, but for the server.

Why not giving us possibility in a next release to specify data types in the source ?

[ThanZelParticipant]

First of all I want to congatulate the jqwidgets developing team who has done a wonderful job creating very useful and fast jquery widgets however there are some issues they need consideration

in response to Loko I have to say that I have a similar problem although kind of different nature

my goal was to create a fully dynamic jqwidget grid which takes all info from json via php. So, I wrote a simple php code which simulates a database connection with paging, filtering and sorting and sends JSON data to the DataAdapter. The JSON structure contains the datafield mapping, the columns mapping, the total row count and the data records.

I tried 3 approaches each one having a certain problem

• first approach: simply took datafield and data from JSON but I couldnt manage to transfer columns data from the initial DataAdapter source in order to to initiate the grid. (If I use standard columns everything works great, paging, sorting… etc)
  ‘
  var source={
  datatype: “json”,
  url: url,
  cache: false,
  root: “data”,
  beforeprocessing: function(data){
  source.datafields = data[0].datafields;
  source.totalrecords = data[2].total;
  },
  id: ‘I’,
  pagenum: 0,
  pagesize: 10
  };
  var dataAdapter = new $.jqx.dataAdapter(source);
  $(“#jqxgrid”).jqxGrid(
  {
  width: 400,
  source: dataAdapter,
  columnsresize: true,
  columns: [
  {
  “text”:”ID”,
  “datafield”:”I”,
  “width”:”84″
  },
  {
  “text”:”Field Name”,
  “datafield”:”1710″,
  “width”:”210″
  },
  …
  ]
  });
  ‘
  My question is how I can access the dataAdapter source within the Grid declaration something like this: (the code is hypothetical, it doesn’t work)

  
  var source={
      datatype: "json",
      url: url,
      cache: false,
      root: "data",
      beforeprocessing: function(data){		
  	source.datafields = data[0].datafields;
  	source.totalrecords = data[2].total;
      },
      id: 'I',
      pagenum: 0,
      pagesize: 10
  };
  var dataAdapter = new $.jqx.dataAdapter(source);
  $("#jqxgrid").jqxGrid(
  {
      width: 400,
      source: dataAdapter,
      columnsresize: true,
      columns: dataAdapter.source.data[1].columns //assuming columns refers to an actual JSON mapping
  ...
  });
  

  ultimely it would be useful if someone would like to pass more info for the grid (styling, initial settings etc…) in the JSON mapping

• second apporach: i used an implementation of the dynamic column demo which uses a second DataAdapter on DownloadComplete to retrieve the columns, everything worked fine but paging, sorting and filtering didnt work finally in the grid.

  
  ...
              var dataAdapter = new $.jqx.dataAdapter(source, {
  		autoBind: true,
                  downloadComplete: function (data) {
  		    var columns = data[1].columns;
                      var gridAdapter = new $.jqx.dataAdapter({
  			datafields: source.datafields,
                          id: 'I',
                          localdata: data[2].data,
  			totalrecords: source.totalrecords,
  			root: source.root,
  			pagenum: 0,
  			pagesize: 10
                      });
  		    
                      $("#jqxgrid").jqxGrid('hideloadelement');
                      $("#jqxgrid").jqxGrid('beginupdate', true);
                      $("#jqxgrid").jqxGrid({
  			source: gridAdapter,
                          columns: columns
  ...
  

• third approach was a hybrid between them using downloadComplete to retrieve the columns but finally using the same initial dataAdapter.

  
  ...
              var dataAdapter = new $.jqx.dataAdapter(source, {
  		autoBind: true,
                  downloadComplete: function (data) {
  		    var columns = data[1].columns;
                      $("#jqxgrid").jqxGrid('hideloadelement');
                      $("#jqxgrid").jqxGrid('beginupdate', true);
                      $("#jqxgrid").jqxGrid({
                          columns: columns
  ...
              $("#jqxgrid").jqxGrid({
                  width: 850,
  		source: dataAdapter,
  ...
  

  Everything phenomenally worked fine but using a developer tool I discover that they were two calls to the php url and there was also an uncought error saying the loading didnt finish

[ThanZelParticipant]

Success!!!

I did it! I used LoadComplete. It is actually pretty simple. Here is the code for anyone interested:
‘
$(document).ready(function () {
var url = “GetGridData.php”;

var source={
datatype: “json”,
url: url,
cache: false,
root: “data”,// the data is mapped actually in JSON data[2].data
beforeprocessing: function(data){
source.datafields = data[0].datafields;
source.totalrecords = data[2].total;
},
id: ‘I’,
pagenum: 0,
pagesize: 10
};

var dataAdapter = new $.jqx.dataAdapter(source, {
loadComplete: function (data){
// data is loaded.
$(“#jqxgrid”).jqxGrid(‘hideloadelement’);
$(“#jqxgrid”).jqxGrid(‘beginupdate’, true);
$(“#jqxgrid”).jqxGrid({
columns: data[1].columns
});
$(“#jqxgrid”).jqxGrid(‘endupdate’);
},
loadError: function (xhr, status, error){
// data is not loaded.
}
});

$(“#jqxgrid”).jqxGrid({
width: 850,
source: dataAdapter,
columnsresize: true,
sortable: true,
pageable: true,
virtualmode: true,
autoheight: true,
rendergridrows: function(obj){
return obj.data;
},
});
$(“#jqxgrid”).jqxGrid(‘showloadelement’);
});
‘

[LokoParticipant]

Hi Thanzel

Thanks for the woraround. Could you post a sample json that the adapter gets, to see what data[0], data[1] and data[2] look like ?

[ThanZelParticipant]

^
Hey Loko
the format for the JSON data was:

[  
   {  
      "datafields":[//the datafields definition (data[0].datafields) for the dataAdapter 
         {  
            "name":"I",
            "type":"string"
         },
         {  
            "name":"1710",
            "type":"string"
         },

...
...
      ]
   },
   {  
      "columns":[//the columns definition (data[1].columns) for the grid 
         {  
            "text":"ID",
            "datafield":"I",
            "width":"84"
         },
         {  
            "text":"Field Name",
            "datafield":"1710",
            "width":"210"
         },
...
...
      ]
   },
   {  
      "total":67,//the total number of records (data[2].total) for the virtual pager
      "data":[  //the actual data in records (data[2].data)
         {  
            "I":"1700_1010",
            "1710":"Account Name",
...
...
         },
         {  
            "I":"1700_2310",
            "1710":"Appllication Name",
...
...
         },
         {  
            "I":"1700_1840",
            "1710":"Calculated",
...
...
         },
...
...
      ]
   }
]

[LokoParticipant]

Thanks ThanZel

[Author]

Posts