Using other included properties when using Custom data source property via AJAX

jLinux

Im using an AJAX source with a custom data source property, heres the JSON:

{
  "partition":{
    "partition_id":"1",
    "name":"Server Asset Management",
    "partition_table":"server_asset_management",
    "status":"0",
    "description":"Managing servers in the application Network",
    "created":"1441293871",
    "primary_column":"server_hostname",
    "groups":[
      ""
    ],
    "fields":{
      "server_hostname":{
        "field_id":"1",
        "partition_id":"1",
        "name":"Server Hostname",
        "column_name":"server_hostname",
        "description":"FQDN Of server",
        "visible":"1",
        "placeholder":"____.application.ad",
        "primary":"1",
        "order":"0",
        "required":"1",
        "unique":"1",
        "regex":null,
        "min":"0",
        "max":"0",
        "type":"string",
        "options":null,
        "default":null,
        "dependent":null,
        "partition_name":"Server Asset Management"
      },
      "operating_system":{
        "field_id":"2",
        "partition_id":"1",
        "name":"Operating System",
        "column_name":"operating_system",
        "description":"OS On server",
        "visible":"1",
        "placeholder":null,
        "primary":"0",
        "order":"1",
        "required":"0",
        "unique":"0",
        "regex":null,
        "min":"0",
        "max":"0",
        "type":"select",
        "options":[
          "RHEL 4",
          "RHEL 5",
          "RHEL 6",
          "CentOS 4",
          "CentOS 5",
          "CentOS 6",
          "Other"
        ],
        "default":"RHEL 4",
        "dependent":null,
        "partition_name":"Server Asset Management"
      }
    },
    "visible_fields":{
      "server_hostname":{
        "field_id":"1",
        "partition_id":"1",
        "name":"Server Hostname",
        "column_name":"server_hostname",
        "description":"FQDN Of server",
        "visible":"1",
        "placeholder":"____.application.ad",
        "primary":"1",
        "order":"0",
        "required":"1",
        "unique":"1",
        "regex":null,
        "min":"0",
        "max":"0",
        "type":"string",
        "options":null,
        "default":null,
        "dependent":null,
        "partition_name":"Server Asset Management"
      },
      "operating_system":{
        "field_id":"2",
        "partition_id":"1",
        "name":"Operating System",
        "column_name":"operating_system",
        "description":"OS On server",
        "visible":"1",
        "placeholder":null,
        "primary":"0",
        "order":"1",
        "required":"0",
        "unique":"0",
        "regex":null,
        "min":"0",
        "max":"0",
        "type":"select",
        "options":[
          "RHEL 4",
          "RHEL 5",
          "RHEL 6",
          "CentOS 4",
          "CentOS 5",
          "CentOS 6",
          "Other"
        ],
        "default":"RHEL 4",
        "dependent":null,
        "partition_name":"Server Asset Management"
      }
    }
  },
  "assets":[
    {
      "asset_id":"1",
      "status":"locked",
      "created":"1441294069",
      "creator":"1",
      "modified":"1444503758",
      "modifier":"1",
      "server_hostname":"lqdasseat13.application.ad",
      "operating_system":"CentOS 5",
      "applications":"Apache,Nagios,Chef,Other",
      "monitoring":"1",
      "notes":"tester",
      "comments":"Something Important"
    },
    {
      "asset_id":"2",
      "status":null,
      "created":"1441942931",
      "creator":"1",
      "modified":"1444441599",
      "modifier":"1",
      "server_hostname":"ldsassaet24.application.ad",
      "operating_system":"RHEL 6",
      "applications":"Jenkins,Tomcat,Nagios,Chef,RHDS",
      "monitoring":"",
      "notes":"tester",
      "comments":""
    }
  ]
}

And heres the HTML/DataTables:

<script language="JavaScript">
    $( document ).ready(function(){
        $('#example').DataTable( {
            "ajax": {
                "url": "/REST/partition/asset_list/partition_id/1/format/json",
                "dataSrc": "assets"
            },
            "columns": [
                { "data": "asset_id" },
                { "data": "server_hostname" },
                { "data": "operating_system" },
                { "data": "applications" },
                { "data": "monitoring" }
            ]
        } );
    });
</script>

<table id="example" class="display" cellspacing="0" width="100%">
    <thead>
    <tr>
        <th>Asset ID</th>
        <th>Server Hostname</th>
        <th>OS</th>
        <th>Apps</th>
        <th>Monitoring</th>
    </tr>
    </thead>

    <tfoot>
    <tr>
        <th>Asset ID</th>
        <th>Server Hostname</th>
        <th>OS</th>
        <th>Apps</th>
        <th>Monitoring</th>
    </tr>
    </tfoot>
</table>

As you can see, im using the assets property, I was wondering, if there was a way to use whats in the partition property, without having to re-query the source on my own. I need to use the contents inside createdRow for example.

Thanks!

jLinux

If thats not possible, then I have a different question. But first, heres how the new js is laid out (Notice it has an ajax call, then loads datatable with a different ajax source, the goal is to use the same source)

 $( document ).ready(function(){
        $.ajax({
            type: 'GET',
            url: '/REST/partition/partition/partition_id/1/format/json',
            dataType: 'json',
            success : function (data) {
                do_datatable(data);
            },
            error: function (xhr, ajaxOptions, thrownError) {
               template.alert('error',xhr);
            }
        });

        function do_datatable(partition){
            console.log('partition',partition);
            
            $('#example').DataTable( {
                "ajax": {
                    "url": "/REST/partition/assets/partition_id/1/format/json",
                    "dataSrc": "assets"
                },
                "columns": [
                    { "data": "asset_id" },
                    { "data": "server_hostname" },
                    { "data": "operating_system" },
                    { "data": "applications" },
                    { "data": "monitoring" }
                ]
            } );
        }
    });

What I could do, is have all of the data pulled from the first $.ajax query, and then pull the data source out of that and pass it to DataTables as JavaScript sourced data, but my main reason for wanting to use the AJAX data source is the ability to use the deferRender feature... is it possible to use that if the source is a JSON string handed to it? as opposed to an AJAX source

---

Summary of this question: If the answer to the question in post #1 is no, then it possible to use deferRender with JavaScript sourced data

jLinux

Heres an example of what I mean in the above post

    $( document ).ready(function(){
        $.ajax({
            type: 'GET',
            url: '/REST/partition/asset_list_dt/partition_id/1/format/json', 
            dataType: 'json',
            success : function (data) {
                do_datatable(data);
            },
            error: function (xhr, ajaxOptions, thrownError) {
               template.alert('error',xhr);
            }
        });

        function do_datatable(data){
            console.log('partition',data);

            $('#example').DataTable( {
                /*
                "ajax": {
                    "url": "/REST/partition/assets/partition_id/1/format/json",
                    "dataSrc": "assets"
                },
                */
                data: data.assets,
                deferRender: true,
                "columns": [
                    { "data": "asset_id" },
                    { "data": "server_hostname" },
                    { "data": "operating_system" },
                    { "data": "applications" },
                    { "data": "monitoring" }
                ]
            } );
        }
    });

It will display the table just fine, but I dont have enough data to tell if the deferRender is actually doing anything

ThomD

deferRender says that it should work with ajax sourced data, so I think your last post is headed in the right direction. But I don't think you need it.

At line 16 you can insert some code that parses the 'partition' data and sets variables that you use in createdRow All you have to do is account for variable scope.

jLinux

@thomD I cant tel if the deferRender is working or not, but the loading of data seems to "work", in that the table loads. However I found that theres much more benefit to using the actual ajax source, especially the ajax.reload() functionality.

So I guess its back to the original question... If I specify a specific property from the AJAX source to use, is there any way to have access to the other properties?

ThomD

Did you try something like this

    function do_datatable(data){
       var PartInfo = data.partition.name;
(etc)
 
        $('#example').DataTable( {
            data: data.assets,
            deferRender: true,
            "columns": [
                { "data": "asset_id" },
                { "data": "server_hostname" },
                { "data": "operating_system" },
                { "data": "applications" },
                { "data": "monitoring" },
                { "data", function ( row, type, val, meta ) {
                    return PartInfo;
               }
            ]
        } );
   }

your "data" object should be fully accessible as an javascript object.

jLinux

@ThomD, im not using the data anymore, thats for local JS/JSON sourced data, I was using it, until I realized that the ajax.reload() wont work with it. (I tried it because the data.assets array was loaded via ajax, but that doesn't mean that DataTables sees it as an AJAX source, which should have been obvious to me)

Heres the current version I have (I tried to shorten it up by taking out any irrelevant code, full version linked below), Look through the comments for ThomD for any relevant notes to this thread:

$( document ).ready(function(){
    $.ajax({
        type: 'GET',
        /** 
         * ThomD - Notice this AJAX source is the same as the ajax.uri used by 
         * DataTables, but the $.each() loop inside the  do_datatable() 
         * function uses the 'partition' index, as well as the anonymous function 
         * inside createdRow as to where DataTables uses the 'assets' index as 
         * the dataSrc
         */
        url: '/REST/partition/asset_list_dt/partition_id/1/format/json',
        dataType: 'json',
        success : function (data) {
            do_datatable(data);
        }
    });

    // Construct DataTables settings from AJAX source then initialize DataTable
    function do_datatable(data_src){
        // Start the columns array for DataTables, and add the static columns
        var columns = [
            { data: 'creator' },
            { data: 'created' }
        ];

        // Add the visible partition fields
        // ThomD - Notice its using the 'partition' key from the source
        $.each(data_src.partition.visible_fields, function(f_name, f_data){
            columns.push({data: f_name});
        });

        $('#data-table').DataTable( {
            ajax: {
                url: "/REST/partition/asset_list_dt/partition_id/1/format/json",
                // ThomD - Now using the 'assets' key from the same ajax source
                dataSrc: "assets"
            },
            columns: columns,
            deferRender: true,
            initComplete: function( settings, json ) {
                // Initialize the Editable, hand down the DT instance for event handling
                assets.editable(this);
            },
            createdRow: function( row, data, dataIndex ) {
                /** 
                 * ThomD - If I could somehow get access to the 'partition' key from this.ajax.url source, 
                 * then I could get rid of the entire $.ajax() call at the top of this script, which would 
                 * be one less AJAX call, which is awesome! since I have a lot...
                 */
                var visible = data_src.partition.visible_fields;

                $.each(columns, function( k, c ){
                    draw_editable($( row ).find( 'td' ).eq( k ),  visible[ c.data ]);
                });
            }
        } );
    }

    function draw_editable($td, field){
        // Draw the jQuery x-edotable link..
    }
});

And incase you need/want it, heres the FULL version of the script

ThomD

I think I understand what you are saying. Because you are using the DT "ajax" function (could there be a more confusing name?), DT is diving deep into your JSON data and ignoring the structure above it.

You wrote "I'm not using the data() option anymore", but I see it on line 32. I like how you've made the DT config dynamic, based on the returned data set.

I don't see a way around what you are currently doing since you want to use DT's native "ajax" function.

jLinux

You wrote "I'm not using the data() option anymore", but I see it on line 32

On line 32? Of the most recent post? Thats the $('#data-table').DataTable() line..

in the most recent post, theres no data, its now ajax

I like how you've made the DT config dynamic, based on the returned data set.

Yeah, the DataTable is basically a reflection of the MySQL table, the partition contains the MySQL table information, and the assets are the rows in the MySQL table.

And I kinda have to use the AJAX source, I want to be able to reload the table from the data source, so if someone else makes an update, then the updates will be reflected in the current table.

If you don't see a way around it, then thats fine, I don't either, was just hoping it was possible. I figure that DataTable must be calling the entire page when it makes the AJAX call, and then just grabs the dataSrc key, and since I use the rest of the content inside the page, it would be useful to use the rest of the AJAX result, as opposed to making another AJAX call for the exact same page.

ThomD

sorry, line 38: columns. You are passing in an object that defines the data elements

I'm still not sure how using the DT "ajax" data definition gets you much. I suppose that is is a bit easier to have ajax.reload handle the new data, but it shouldn't be that much work to use $.getJSON and clear the data, load a new set and addrows.. OTOH, I haven't done it so it may be harder than I think it is.

In your current setup, what does ajax.json show during rowCallback?

jLinux

@ThomD, Line 38 isn't specifying the data source.. its defining the columns.. totally different. Im not talking about how the table columns are defined, im talking about how the source of the data is pulled.

And using ajax.reload() is a lot easier, not a bit easier. When the table is reloaded via ajax.reload(), it pulls all of the new data, as well as runs the createdRow again for each row. And if you look at the new version of my JS, theres a lot of code that gets executed in there. The entire row is parsed and each cell is turned into an instance of the jQuery plugin x-editable. This basically accomplishes the same thing as DataTables Editable, except its fully open source, since this is going to be an open source project. (Id totally use DT Editable if the project wasn't OS :( )

The end result is a nice combo of x-editable and DataTables, fully ajaxed -> http://d.pr/i/16Wwy

jLinux

Man, I love DataTables...

I switched from DOM sourced to AJAX sourced.

DOM: http://d.pr/i/1cmCS

AJAX (w/ deferRender): http://d.pr/i/1eCJe

Granted, I did a load test with 5k rows, but thats still awesome! hah

ThomD

Nice performance improvement. I hope you don't mind if I ask some questions.

In http://d.pr/i/16Wwy line 136, it looks like you are setting the "id" attribute to the column name. That would be the same "id" on all td in the column. "id" should be unique in the DOM. You could add the asset_id to make it unique.

As far as the data load method, I would be seriously disappointed with DT if using row.add() didn't invoke createdRow.

Let me make your life more complicated :) by suggesting that rather redrawing the entire data set to pickup any changes, that you query only for db entries that have been modified since the current page was reloaded. Then you wouldn't be pulling as much data.

Line 88, parseInt. I recently learned that it is strongly recommended to always add the second parameter to define what base the data is in. If the data should start with a zero, some browsers will type it as octal,

cell_html = tools.date('%d-%M-%Y %H:%m:%s', parseInt(data.created,10));

It is an unlikely occurrence, but you never know.

ThomD

did you get a change to see what ajax.json shows during createdRow call back if you use the top level URL for your ajax call? I wonder if that would expose the higher level data and then use ajax.dataSrc to define the data objects.

And thanks for guilting me into learning markdown. :)

jLinux

@ThomD That link you sent is a screenshot of the table, not of the code.. what was in line 136?

Let me make your life more complicated :) by suggesting that rather redrawing the entire data set to pickup any changes, that you query only for db entries that have been modified since the current page was reloaded. Then you wouldn't be pulling as much data.

I would love to do that instead.. how can I update only selected rows? I asked that here, and allan said it wasnt possible... Maybe he mis-understood me? Or you have a way of doing it?

did you get a change to see what ajax.json shows during createdRow call back if you use the top level URL for your ajax call? I

No, thats a good idea, ill try it out, thank you!

jLinux

Dude... ajax.json() works.... (All data) You just made my fu#kin day!!! Id hug you if you were here right now. Are you within driving range of Phx, AZ? (kidding)

P.S. @ThomD - Thanks man, This saved me SOO much work!! lol.

And im glad I guilted you into using the syntax in your posts, it makes reading them soo much easier. When people post code with no syntax highlighting, it just scrambles it all up, and I dont even try to read it. And when you use the proper enclosures for things like createdRow, its so much easier to read, IMO

jLinux

@ThomD - Wanted to get your opinion on something..

You can see in HERE, that when I create the row, inside createdRow, I parse the type of "field" it is (from the JSON source), then replace the content with an a so I can initiate the jQuery x-editable plugin, the result is a table filled with the x-editable links (screenshot here)

However.... I just found the columns.render option, which it says...

Each column has an optional rendering control called columns.render which can be used to process the content of each cell before the data is used.

Heres the DataTables Example page that lead me to it

Do you think I should use columns.render to replace the data in each cell with a link? or is it better just how it is?..

EDIT: I don't think ajax.json is accessible inside columns.render, so that kinda voids the idea, since its required

ThomD

What is wrong with my copy/paste skills this week?

IN this http://code.linuxdigest.org/view/a8821666 link, this code,

            var editable_link = {
                'href'           : '#',
                'id'             : field.column_name,
                'data-title'     : "Enter " + field.name,
                'data-field-type': field.type,
                'placeholder'    : field.placeholder
            };

looks like it is setting the DOM element 'id' to a value that is column, but not td specific.

about that single line data refresh, if Allan says it can't be done with the native tools, I will not argue. I'm at home now, so I can't see my code at work, but I think I'm relying on a $getJSON call to get one item and then refresh the data for that one row.

ThomD

WRT to columns.render vs createdRow - I'm slowly coming to understand the DT often gives a couple of ways to get to the same goal. If it ain't broke, don't fix it.

From a maintenance perspective, I like createdRow because it provides one stop shopping for a lot of logic. You could end up with rendering options scattered everywhere.

jLinux

@ThomD

looks like it is setting the DOM element 'id' to a value that is column, but not td specific.

If you look at the x-editable documentation, it requires the ID, but I just took a look at it, and it makes it optional to use id or data-name, so ill probably switch it to data-name, since the id field should always be unique.

I figured that even though using the ID was "wrong", (since they wont be unique in this case), id leave it be, since it was working. But im going to try changing it.

Thanks!

EDIT: Updated the code from id to data-name, and it works fine :) I was just too lazy to do it before I guess, lol..

var editable_link = {
    'href'           : '#',
    'data-name'      : field.column_name,
    'data-title'     : "Enter " + field.name,
    'data-field-type': field.type,
    'placeholder'    : field.placeholder,
    'value'          : $td.text()
};

jLinux

Oh, in regard to this statement:

about that single line data refresh, if Allan says it can't be done with the native tools, I will not argue. I'm at home now, so I can't see my code at work, but I think I'm relying on a $getJSON call to get one item and then refresh the data for that one row.

Can you show me the code? :-D

jLinux

@ThomD, so I just uploaded my code to a server and executed it, and the ajax.reload actually does take longer than I thought, even if its just for 5 or 10 rows.

I sent you a PM with the login details for my app.

When I had it locally, it was pretty fast, and I guess since the ajax source is from the same location as the app, I figured it would still be decently fast, but it seems to be slow.

I added 5k assets to the table just to get an idea of how it would work under a heavy load.. so imagine if 100+ rows were showing, and ajax.reload executed... Probably crash the browser :(

So if you can let me check your code out, that would be awesome! hah

jLinux

Found this, looks interesting, might help a bit. id still like to see your code tho :)

Edit: Hm, I think that link is only for serverSide... Not just ajax :(

ThomD

When I edit an entry, I get back a fresh dataset for that item. I refresh the datatables content with this code in my ajax success call.

var rowkey = '#row' + returnData.d.Id;
dtTable.row(rowkey).data(returnData.d).draw(); 

Every row gets an id with this

createdRow: function (row, data, rowIndex) { 
    $( row ).attr('id','row' + data.Id);
}

It may not be obvious, but that # is the jQuery selector syntax for an element with that 'id' attribute.

jLinux

Hm, I see... Do you have a demo I can see of this somewhere?

When you edit that item, how do you get the returnData.d ?

jLinux

Do you know if its possible to re-poll the content from the datatables ajax source, without reloading the whole table? like if I wanted to do something like grab the cached content that DT uses, then get the latest content, then diff them via jQUery and reload only the rows that need to be updated (and find a way to delete/add the deleted/added rows)

Since im using deferRender, I guess I wouldnt have to parse all 5k rows... just whatevers showing.. (for some reason, that seems like more work.. lol)

ThomD

Sorry, demo is "safely" locked up behind our firewalls. I lied (OK, I simplified), when I save a change, I get back only an ACK. In the "save" ajax call, I use the success event to trigger a new ajax call that returns only that item that had been updated. I have lots of server side calculated fields, so that works out for me.

This is the more complete version. This is within the definition of my dtEditor setup.

 ajax: function( method, url, submitData, dtSuccessCallback, dtFailureCallback) {
    myAjaxURL = submitData.data.uri;
    myVersion = submitData.data.version;
    $.ajax({
        type: "POST",
        url: myAjaxURL,
        contentType: "application/json; charset=utf-8",
        dataType: "json",
        beforeSend:  function (xhr) {
            xhr.setRequestHeader("If-Match", myVersion );
            xhr.setRequestHeader("X-HTTP-Method", 'MERGE');
        },
        data: JSON.stringify({ ... all my data here..   }),
        cache: false,
        processData: true,
        success: function (data, status, xhr) {
              dataRefreshURI = myAjaxURL;
            $.ajax({
                dataType: "json",
                url: dataRefreshURI,
                success: function( returnData) {                    
                    dtSuccessCallback(returnData.d);
//Every row was given a unique key (id=...) when it was created                                     var rowkey = '#row' + returnData.d.Id;
//this updates the onscreen data with the returned values
                    dtTable.row(rowkey).data(returnData.d).draw(); 
                }
            }); 
        },
        error: function (xhr, jqAjaxerror, thrown) {
            dtFailureCallback( jqAjaxerror );
        }
    });
} 

ThomD

As far as polling for changes, you could use setInterval() to poll. Let's say you go with a 5 minute interval, you would need to be able ot query the database for items created/modified within the last 5 minutes. New items would be a straight up addRow, modified items would use the same logic I'm showing around line 25 in my last post.

If you get fancy, then you would want to give the user a way to not auto refresh and have a manual refresh button.

jLinux

Very interesting.. thank you, Few questions if you don't mind :-D

1. The anonymous function in the ajax value, what does that need to return? The JSON for the rows?
2. Is there any documentation for the values passed to your method, url, submitData, dtSuccessCallback, dtFailureCallback parameters? I didn't see anything in the references documentation, but I may have missed it
3. What is the submitData.data.version? I see you use it for the header If-Match, ive never used that before
4. Is the X-HTTP-Method something specific to Microsoft? I googled it and everything I see is in the MSDN. I know I can just look for it via PHP though
5. You have an AJAX call inside of an AJAX call, but they are using the same URL right? On line 2, you run myAjaxURL = submitData.data.uri and use myAjaxURL as the url value, then on line 17, you run dataRefreshURI = myAjaxURL and use dataRefreshURI as the new url value.. Just a little confused on that.
6. In your last post, you said As far as polling for changes, you could use setInterval() to poll, the setInterval shouldnt be inside of the anonymous function in the ajax value, right? (this might be obvious, just want to be sure)

jLinux

I can create a new RESTful resource that I can pass the current timestamp to, and it will return any "assets" that have been updated/added since that timestamp, im not positive how I should take care of the deleted ones though.. im sure ill figure it out. But is this basically how youd do it?

If you get fancy, then you would want to give the user a way to not auto refresh and have a manual refresh button.

Definitely planned on doing that already :)

ThomD

1 - That ajax in the first line is the Editor's ajax option, so it is a little different.
http://editor.datatables.net/reference/option/ajax

It requires that either the dtSuccessCallback or dtFailureCallback functions be called. Those are callbacks to Editor to let it know we are done. Nothing else needs to be returned because the anonymous function makes all the changes and then just signals to Editor the resulting status.

2 - All of that is in the Editor documentation (link above).

3 - Versioning is part of the SharePoint back end. It is how my back end deals with collisions.

4 - More SharePoint magic.

5 - Oddly, yes. I use the same URL for the POST and the GET. Just the way it is for my environment. Note that there is no "type" parameter on the second $ajax.

6 - I'd put the setInterval() in my initComplete. that way it only gets started if you've been able to get that initial data load.