Yajra Datatable not soring date correctly

[DevMushref]

I have datatable with "started date", when I order it by asc it goes:

Mar, Jan, Feb, Apr

When ordered by desc:

Apr, Feb, Jan, Mar

"Datatables might be reading the date as string, change it to a date"

I did, like this:

    ->parameters([
                    'columnDefs' => [
                        ['targets' => [4], 'type' => 'date'],
                    ],
                ]

And also in the controller like this:

    ->editColumn('startDateTime', function ($report) {
                        return $report->startDateTime ? with(new DateTime($report->startDateTime))->format('M d, Y H:i') : '';
                    })

I tried different methods in the controller like:

1. return date('d M, Y H:i', strtotime($report->startDateTime));
2. return $report->startDateTime->format('d M, Y H:i:s');

In the html aka blade form I use input type datetime-local where the default date insert is mm-dd-yyyy --:--:--

I changed the data type from datetime to timestamp in MySQL and vice versa to test.

In the model, I added this code:

    protected $dates = [
            'startDateTime',
            'endDateTime',
        ];

[yajra]

This is not applicable for server-side implementation:

->parameters([
                    'columnDefs' => [
                        ['targets' => [4], 'type' => 'date'],
                    ],
                ]

Can you provide your query? You might be using a collection that's why it was treated as string?

[DevMushref]

This is my entire datatable code

<?php

namespace App\DataTables\dataEntry;

use App\Models\Report;
use Yajra\DataTables\Html\Column;
use Yajra\DataTables\Services\DataTable;

class ReportDataTable extends DataTable
{
    /**
     * Build DataTable class.
     *
     * @param mixed $query Results from query() method.
     * @return \Yajra\DataTables\DataTableAbstract
     */
    public function dataTable($query)
    {
        return datatables()
            ->eloquent($query)
            ->toJson();
    }
    /**
     * Get query source of dataTable.
     *
     * @param \App\Models\dataEntry/Report $model
     * @return \Illuminate\Database\Eloquent\Builder
     */
    public function query(Report $model)
    {
        return $model->newQuery();
    }

    /**
     * Optional method if you want to use html builder.
     *
     * @return \Yajra\DataTables\Html\Builder
     */
    public function html()
    {
        return $this->builder()
            ->setTableId('reports-table')
            ->addCheckbox([
                'data' => 'Checkbox',
                'width' => '10px',
            ])
            ->columns($this->getColumns())
            ->minifiedAjax()
            ->dom('Bfrtip')
            ->stateSave(true)
            ->responsive()
            ->autoWidth(false)
            ->parameters([
                'scrollX' => true,
                'lengthMenu' => [
                    [10, 25, 50, 100, -1],
                    ['10 rows', '25 rows', '50 rows', '100 rows', 'Show all'],
                ],
                'buttons' => [
                    'pageLength',
                    'pdf',
                    'print',
                    'excel',
                    'copy',
                ],
                'select' => ['style' => 'os'],
            ])
            ->addTableClass('align-middle table-row-dashed fs-6 gy-5');
    }

    /**
     * Get columns.
     *
     * @return array
     */
    protected function getColumns()
    {
        return [
            Column::checkbox('select'),
            Column::make('irNumber')->title('IR Number'),
            Column::make('caseType')->title('Type'),
            Column::make('caseLocation')->title('Location'),
            Column::make('startDateTime')->title('Started'),
            Column::make('endDateTime')->title('Ended'),
            Column::make('cccEmployee')->title('CCC Employee'),
            Column::computed('action')
                ->exportable(false)
                ->printable(false)
                ->orderable(false)
                ->searchable(false)
                ->addClass('text-center')
        ];
    }

    /**
     * Get filename for export.
     *
     * @return string
     */
    protected function filename()
    {
        return 'dataEntry/Report_' . date('YmdHis');
    }
}

[yajra]

The code looks fine except for toJson since it requires a DataTable class to be returned.

public function dataTable($query)
    {
        return datatables()
            ->eloquent($query);
//            ->toJson(); // toJson is not needed here.
    }

Also, is the report model a database view or an actual table? There might be a conversion done from date to string data type.

[DevMushref]

The model is like this:

protected $table = 'cases_reports';

    protected $exportColumns = [
        'irNumber',
        'caseType',
        'caseLocation',
        'startDateTime',
        'endDateTime',
        'cccEmployee',
    ];

    protected $printColumns = [
        'irNumber',
        'caseType',
        'caseLocation',
        'startDateTime',
        'endDateTime',
        'cccEmployee',
    ];

    protected $fillable = [
        'irNumber',
        'caseType',
        'caseLocation',
        'startDateTime',
        'endDateTime',
        'caseDesc',
        'actionsTaken',
        'caseDetails',
        'caseNotes',
        'caseRecommendations',
        'cccEmployee',
    ];

    protected $dates = [
        'startDateTime',
        'endDateTime',
    ];

[schonhoff]

Hello, DataTables is sorting your date column alphabetically and not date/datetime specific. I'm not used to use the Html Builder of this package, but maybe an example of my implementation will help you to figure out your problem:

My php code:

->editColumn('updated_at', function (Model $model) {
   return [
       'display' => $model->updated_at->format('d.m.Y'),
       'timestamp' => $model->updated_at->timestamp,
   ];
 })

My JQuery code in Columns:

{
  data : {'_': 'updated_at.display', 'sort': 'updated_at.timestamp'},
  name: updated_at.timestamp',
  orderable: true
},

The sort is able to be used as a sorting parameter (timestamps) but the _ will be used to display the value (the date).

One thing I did remember after reading your code 3 times. Did you add the "Ultimate datetime sorting plugin" on your site? DataTables Docs: https://datatables.net/reference/option/columns.type https://datatables.net/blog/2014-12-18 I don't know if the DataTables HTML Builder is automatically adding this plugin to the script sections but if it is missing, maybe that is the problem.

Hope I could help you a little bit.

[DevMushref]

@schonhoff Thank you for your support.

The issue I have is I can't use jquery at all. When I try to I get an error saying

"DataTables warning: table id=reports-table - Cannot reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3"

So I guess I'm stuck with php...Yes I tried your solution before but instead of getting the date I get [Object Object]

[yajra]

@DevMushref can you make sample project to replicate the issue?

[github-actions[bot]]

This issue is stale because it has been open for 30 days with no activity. Remove stale label or comment or this will be closed in 7 days.

[github-actions[bot]]

This issue was closed because it has been inactive for 7 days since being marked as stale.