Laravel DataTables and sortable DateTime/Carbon objects

There is an example of using DateTime/Carbon object however this example shows date and time in Y/m/d format which is sortable but most often you would want to use date/time format suitable for your country or a maybe human-readable format, such as 5 days ago.

jQuery DataTables uses the term orthogonal data to describe a field having multiple values for different operations, such as sorting, filtering or display. In our case, our data field can have one value for display such as 12/31/2016 and another value 1483142400 that would be used for ordering.

Response for this data in JSON format may look as shown below:

{   
    "draw": 1,
    "recordsTotal": 1,
    "recordsFiltered": 1,
    "data": [
        {
            "id": "1",
            "name": "Tiger Nixon",
            "email": "tiger.nixon@company.com",
            "created_at": {
                "display": "12/31/2016",
                "timestamp": "1483142400"
            }
        }
    ]
}

To produce data in multiple formats you need to use editColumn method and return associative array consisting of formats for display (display) and sorting (timestamp). Optionally you may want to use filterColumn method to allow searching in the same format that is used for display (m/d/Y).

PHP:

public function index()
{
    return view('app.users');
}

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);
}

On the front-end you need to use columns.render option and specify data property for each operation.

JavaScript:

$('#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'
           }
        }
    ]
});

You May Also Like

jQuery DataTables: Row selection using checkboxes and Select extension

jQuery DataTables: Row selection using checkboxes

jQuery DataTables: Why click event handler does not work

jQuery DataTables: Alphabetical Search