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

Comments

  1. It works great if you have single date to be displayed. However, it fails if you have more than one dates to be displayed. It sorts only the first date.

  2. It works, but there is an issue with sorting and relationships

    If you search after you have sorted with a relationship field you get an sql error

    Integrity constraint violation: 1052 Column ‘created_at’ in where clause is ambiguous

    Can’t really figure out why though

    1. Most likely you’re joining two tables and need to include specific table when referring to column created_at, for example: users.created_at. I am going to update the article, since it is no longer relevant.

Leave a Reply

(optional)

This site uses Akismet to reduce spam. Learn how your comment data is processed.