Issue in date sorting of Yajra Datatable

Sorting is not considering it as the date. This is the screenshot of how it is shown in descending order http://prntscr.com/nl7kjh

My Controller file

    public function getReminderTableData()
    {

        if (Auth::user()->privilage == 1){
            $data_rem = Reminder::where('user_id','1')
            ->where('is_done','0')
            ->where('is_temp_delete','0')
            ->get();
        }
        if (Auth::user()->privilage == 0){
            $data_rem = Reminder::where('user_id',Auth::user()->id)
            ->where('is_done','0')
            ->where('is_temp_delete','0')
            ->get();
        }


        return DataTables::of($data_rem)
        ->addColumn('action', function ($data_rem) {

            if(!$data_rem->is_strike) {
                $html = '
                <a href="/reminders/'.$data_rem->id.'/done"><button class="button is-primary" type="submit" data-toggle="tooltip" title="Marked as done"><i class="fa fa-check-square-o" aria-hidden="true"></i></button>
                </a>
                <a href="/reminders/'.$data_rem->id.'/strike">
                <button class="button btn-warning" type="submit">Strike</button>
                </a>
                <a href="/reminder/edit/'.$data_rem->id.'">
                <button class="button is-primary button_edit" type="submit" data-toggle="tooltip" title="Edit"><i class="fa fa-pencil" aria-hidden="true"></i></button>
                </a>';
            } 
            else {
                $html = '
                <a href="/reminders/'.$data_rem->id.'/done"><button class="button is-primary" type="submit" data-toggle="tooltip" title="Marked as done"><i class="fa fa-check-square-o" aria-hidden="true"></i></button>
                </a>
                <a href="/reminders/'.$data_rem->id.'/strike">
                <button class="button btn-info" type="submit">Unstrike</button>
                </a>
                <a href="/reminder/edit/'.$data_rem->id.'">
                <button class="button is-primary button_edit" type="submit" data-toggle="tooltip" title="Edit"><i class="fa fa-pencil" aria-hidden="true"></i></button>
                </a>';
            }
            return $html;
        })
        ->editColumn('category', function($data_rem) {
            return $data_rem->reminderCategory->name;
        })
        ->editColumn('created_at', function($data_rem) {
            return date('d F Y', strtotime($data_rem->created_at));
        })
        ->editColumn('data', function($data_rem) {
            if(!$data_rem->is_strike) {
                $html = $data_rem->data;
            }
            else {
                // fix this
                $html = '<del>'.$data_rem->data.'</del>';
            }
            return $html;
        })
        ->rawColumns(['data','action'])
        ->make(true);
    }

Nakov

7 years ago

Best Answer

Level 73

The problem here is sorting using a string value instead of a timestamp. So you are sorting given your formatted date.

If you are not using ajax in order to render your datatable, then you can use the data-sort or data-order attributes on the cell containing your date to let the library know which value to use for sorting, and you should give the timestamp of the date. More on this here

As I can see you are using ajax, so in that case your date column should return the timestamp like this:

...
->editColumn('created_at', function($data_rem) {
            return $data_rem->created_at->timestamp; // I guess the created_at is a Carbon instance
        })

Then in your JavaScript, I override the rowCallback function on the DataTable and I format the date there:

rowCallback: function (row, data, index) {
    let dateCell = data.created_at;

    if (dateCell !== undefined && dateCell > 0) {
        let date = moment.unix(dateCell).format('d F Y'); // I am not sure that the format is the same using moment js. Or you can use your format here
        $('td:eq(3)', row).html(date); // 3 here is equal to the cell in which the date should be placed in the table.
    }
}

Hope this helps.

2 likes

gurvindersingh

7 years ago

Level 1

@NAKOV - Thanks

Please or to participate in this conversation.