Symfony 5 Search on a DateTimeColumn datatables

yinyang

Hello,

I've been looking for a way to filter my "start" and "end" fields from my datatable for hours now. These are two DateTimeColumn type fields.

I am using the Symfony DataTables Bundle.

I understood apparently that we cannot filter on a DateTimeColumn because the returned data is of type DateTime Object, so I cannot compare a string and an object.

So, how to do ?

Can't we make sure to convert my DateTime objects to be able to do the filtering?

Or transform the value typed in the search field into DateTime?

Thanks for your help.

Here is a screen:

My code (PHP) :

->createAdapter(ORMAdapter::class, [
    'entity' => Event::class,
    'query' => function (QueryBuilder $builder) use ($eventStatus) {
        $builder
            ->select('e')    
            ->addSelect('ca')
            ->addSelect('ci')
            ->addSelect('u')  
            ->from(Event::class, 'e')
            ->join('e.category', 'ca')
            ->join('e.city', 'ci')
            ->join('e.user', 'u')
            ->andWhere('e.status = :status')
            ->setParameter('status', $eventStatus)
            ->orderBy('e.id', 'DESC')
        ;
    },
])

startDate column:

->add('startDate', DateTimeColumn::class, ['label' => 'Début', 'field' => 'e.startDate', 'format' => 'd/m/Y',  'render' => function($value, $context) {
    return sprintf(
        '%s<br>
        %s',
        $context->getStartDate()->format('d/m/Y'),
        $context->getStartAt()->format('H\hi'),
    );
}])

(JS) :

$('#events').initDataTables({{ datatable_settings(datatable) }}, { 
    // processing: true,
    // serverSide: true,
    searching: true, 
    ordering: false,
    searchDelay: 200,
    dom: 'Bfrtip',
    buttons: [
        {
            text: 'Réinitialiser',
            action: function (e, dt, node, config) {
                location.reload();
            }
        },
        {
            extend: 'columnsToggle',
            columns: [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
        }
    ],
    initComplete: function(settings, json) {
        var api = this.api();

        // Get number of total records
        var recordsTotal = api.context[0].fnRecordsTotal();
        $('#events_list h5 span').text(recordsTotal);

        // Hide some columns
        api.columns([4,9]).visible(false);

        // Create tr filter
        var tr = $('<tr id="filter_search"></tr>');
        // Count number of cells in a row
        var nbCells = document.getElementById('dt').rows[0].cells.length;
        // Generate cells to #filter_search row 
        for (var i = 0; i < nbCells; i++) {
            if (i == 1 || i == 8 || i == 11) {
                tr.append('<th></th>');
            } else {
                tr.append('<th><input type="search" onclick="stopPropagation(event);" placeholder="Rechercher"></th>');
            }
        }

        var firstHeaderRow = $('tr', api.table().header());
        tr.insertAfter(firstHeaderRow);

        $("#filter_search th").eq(5).find('input').datepicker({
            autoclose: true,
            todayHighlight: true,
            language: "fr",
            dateFormat: "dd/mm/yy",
        });

        $("#filter_search input").on('keyup change', function(e) {
            if (e.keyCode == 13) {
                api
                    .column($(this).parent().index()+':visible')
                    .search(this.value)
                    .draw();
            }
        });

        $('.buttons-columnVisibility').each(function(index, element) {
            $(element).click(function() {
                if (api.column(index).visible() === true) {
                    $('#filter_search th').eq(index).show();
                } else {
                    $('#filter_search th').eq(index).hide();
                }
            });
        });
    },
});

Thank you.

colin

You can convert fields, in columns.render. and define the type in columns.type,

Colin

yinyang

Hi @colin,

I did :

columnDefs: [ { 
            "targets": 6,
            "type": "date",
            "data": "startDate",
            "render": {
            "_": "plain",
            "filter": "filter",
            "display": "display"
            }
        }],

But I have this error when the table loads :

tangerine

Follow the link in the error message for diagnostic steps.

yinyang

Okay so I did :

->add('startDate', DateTimeColumn::class, ['label' => 'Début', 'field' => 'e.startDate', 'format' => 'd/m/Y'])

(I removed the render in this side)

And :

columnDefs: [ {
            targets: 6,
            type: "date",
            data: "startDate",
            render: function (data, type, row) {
                return data;
            }
        }],

The data returned contains for example 19/08/2020.

But when I search "19/08/2020" it can't find me anything :

yinyang

Please can someone help me ?

colin

Can you link to your page so we can take a look, please,

Colin

yinyang

Hi, I found a solution,

The value must match with yyyy-mm-ddT00:00:00+00:00 to make it work.

So this is my code :

// If we are on datetime column
if ($(this).parent().index() == 6 || $(this).parent().index() == 7) {
    var dateSplit = this.value.split('/');
    //2020-08-19T00:00:00+00:00
    val = dateSplit[2] + '-' + dateSplit[1] + '-' + dateSplit[0] + 'T00:00:00+00:00';
}

api
    .column($(this).parent().index()+':visible')
    .search(val)
    .draw();