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

Related posts

Comments

Leave a Reply

You may use simple HTML to add links or lists to your comment. Also use <pre><code class="language-*">...</code></pre> to mark up code snippets. We support language-js, language-markup and language-css for comments.
(Optional)