DataTables from Ajax source order-by data-order and display formatted date

Question

Basically I want to pull data from ajax into my columns, but I want the cells in the columns to have the data-order attribute on them with the value from the ajax call and use moment.js to format the data in the cell.

I'm assuming this is the best way to make it pretty AND orderable. I found the plugin for datetime-momentJS, but it will only order the date, not format it as well.

var dataTable = $('#products').DataTable( {
  'processing': true,
  'ajax': '/products',
  'columns': [
    {
      'data': 'updated_at',
      'className':'date'
    }
  ]
});

right now I have this as the final result:

<td class="date">2015-11-08T11:00:00.000Z</td>

but the result I want is:

<td class="date" data-order="2015-11-08T11:00:00.000Z">11/08/2015</td>

Can I use the render option to do this somehow?

The moment code to format it like I want would be moment('2015-11-08T11:00:00.000Z').format('DD/MM/YY').

Consolation

Answer 1

You can use createdRow callback in order to apply any custom logics after row creation:

$('#products').dataTable({
  /* */
  'createdRow': function(row, data, dataIndex) {
      var $dateCell = $(row).find('td:eq(0)'); // get first column
      var dateOrder = $dateCell.text(); // get the ISO date
      $dateCell
          .data('order', dateOrder) // set it to data-order
          .text(moment(dateOrder).format('DD/MM/YY')); // and set the formatted text
  }
});

Note that td:eq(0) selector assumes that the column with date is the first column. You need to change 0 to another value, if it's not.

Debouch

@Ezra I have removed the semicolon, but data is a working jQuery function :) Nice to hear that it works. – Debouch

working or not, .data() did not work in ur answer. – Consolation

might be because I'm using .attr() elsewhere in my code, but ur right it IS a valid function and 'technically' should work. – Consolation

@Ezra Interesting :) Of course, you can simply use attr if data isn't working for you. – Debouch

Answer 2

You can achieve the same result directly by sending orthogonal data through Ajax.

The timestamp formatting you will be doing on server side and you won't need any other JavaScript callbacks and plugins.

Code sample form the link above

JavaScript:

$(document).ready(function() {
    $('#example').DataTable( {
        ajax: "data/orthogonal.txt",
        columns: [
            { data: "name" },
            { data: "position" },
            { data: "office" },
            { data: "extn" },
            { data: {
                _:    "start_date.display",
                sort: "start_date.timestamp"
            } },
            { data: "salary" }
        ]
    } );
} );

Ajax:

{
  "data": [
    {
      "name": "Tiger Nixon",
      "position": "System Architect",
      "salary": "$320,800",
      "start_date": {
        "display": "Mon 25th Apr 11",
        "timestamp": "1303686000"
      },
      "office": "Edinburgh",
      "extn": "5421"
    },
    {
    ...
    }
}

Condense

Answer 3

I faced a very similar issue with a Laravel-based project of mine. Bogdan's answer got me on the right track; however, this link managed to help me completely solve my particular issue - https://www.gyrocode.com/articles/laravel-datatables-and-sortable-datetime-carbon-objects/.

The author is using yajra/laravel-datatables package for back-end processing - it is a fantastic package, and one I enjoy using. The official link for the DataTables JQuery plugin is https://datatables.net (you can also install this with Bower and Node Package Manager). I am yet to explore DataTables in depth; however, there are numerous add-ons that DataTables also provides for extra functionality.

In the author's example, he wanted to display his dates in a human-readable format, yet be able to sort it by a sortable value (e.g. a numerical value for a timestamp).

Below are code samples taken directly from source:

PHP Code in his Laravel controller

public function indexData()
{
    $users = User::select(['id', 'name', 'email', 'created_at']);

    return Datatables::of($users)
        ->editColumn('created_at', function ($user) {
           return [
              'display' => e(
                 $user->created_at->format('m/d/Y')
              ),
              'timestamp' => $user->created_at->timestamp
           ];
        })
        ->filterColumn('created_at', function ($query, $keyword) {
           $query->whereRaw("DATE_FORMAT(created_at,'%m/%d/%Y') LIKE ?", ["%$keyword%"]);
        })
        ->make(true);
}

Here, the author is adding 'display' and 'timestamp' values to be included with each 'created_at' data value. For the 'display' value, he is altering the created_at data value in a human-friendly format. The 'timestamp' value is a numerical representation of the created_at data value - this is the value the author plans to sort the data on.

Example JSON-formatted data

The below sample of data is an example of what could be returned from the above controller:

{   
    "draw": 1,
    "recordsTotal": 1,
    "recordsFiltered": 1,
    "data": [
        {
            "id":         "1",
            "name":       "Tiger Nixon",
            "email":      "[email protected]",
            "created_at": {
                "display": "12/31/2016",
                "timestamp": "1483142400"
            }
        }
    ]
}

Here, you can see 'display' and 'timestamp' values for the 'created_at' piece of data. As in Bogdan's answer, and mentioned earlier, the display value is for viewing by the user, and the 'timestamp' value is for sorting by the DataTables front-end.

DataTables Plugin Usage

To process the JSON-formatted data, the author has set-up the plugin as follows:

$('#users-table').DataTable({
    processing: true,
    serverSide: true,
    ajax: '/app/users',
    columns: [
        { data: 'id', name: 'id' },
        { data: 'name', name: 'name' },
        { data: 'email', name: 'email' },
        {
           data: 'created_at',
           type: 'num',
           render: {
              _: 'display',
              sort: 'timestamp'
           }
        }
    ]
});

Note: the author is using an AJAX call to retrieve his data in this example. You can replace ajax: '/app/users' to data: yourJsonData - using a variable which holds JSON data you want to process. The JSON data has been formatted specifically for use with DataTables.

Orthogonal data, with respect to DataTables, has solved quite a few future problems for me in a project I'm working on. I can see this as being very powerful, especially for usability and being practical. I hope Bogdan's answer and this example helps any future readers out there - I'm certainly glad I came across them :).

Careworn