Search for a value between

[Exellent1988]

So i have the following scenario, i have a table which obviusly is to large for clientside filtering. So i want to implement multiple searches. Which workes fine. But in one column i have an timestamp and i would like to search not one one specific timestamp but for a range. normaly i would SQL something like this

FROM eventList WHERE  `date` 
BETWEEN '2013-03-26 00:00:01' AND '2013-03-26 23:59:59'

I allready tryed it with:

 var start = picker.startDate.format('YYYY-MM-DDT00:00:00+00:00');
   var end = picker.endDate.format('YYYY-MM-DDT00:00:00+00:00');
    $('#dt').DataTable().column(7).search([start,end]);

but without luck. wht do i have to do to get it to work?

[curry684]

This is a pretty advanced scenario and is therefore to my knowledge not natively supported. It is however possible to just create your entirely own filter form with any feature you want, and then add its contents to every request so you can append whatever you need to the QueryBuilder. I just posted some relevant sample code in another issue: https://github.com/omines/datatables-bundle/issues/282#issuecomment-1351274313

In your case you would want to serialize the filter form in the prexhr event, and you can then even use Symfony Form handler to append the values to the table type, simplified example:

        $form = $this->createForm(MyFilterType::class)->handleRequest($request);
        $datatable = $dataTableFactory->createFromType(MyTableType::class, [
            'form' => $form,
        ])->handleRequest($request);

        if ($datatable->isCallback()) {
            return $datatable->getResponse();
        }

        return $this->render('mytemplates/list.html.twig', [
            'filterForm' => $form->createView(),
            'datatable' => $datatable,
        ]);

[Exellent1988]

okay so what i've tryed so far is to to pass an array to a column serach like this in frontend:

$('#reportrange').on('apply.daterangepicker', function(ev, picker) {
   var start = picker.startDate.format('YYYY-MM-DDT00:00:00+00:00');
   var end = picker.endDate.format('YYYY-MM-DDT00:00:00+00:00');
    $('#dt').DataTable().column(10).search(JSON.stringify({'start': start,'end':end}));
    $('#dt').DataTable().draw();
});

And in the Backend, i tryed it like this:

  $term = $state->getSearchColumns();
                    $vars = ['start' => '', 'end'=> ''];
                    if ( isset($term['timestamp']) ){
                        $vars= json_decode($term['timestamp']['search'],true);
                        $state->setColumnSearch($term['timestamp'],'',false);
                        $builder->add('where', $builder->expr()->between(
                             'a.timestamp',
                             ':from',
                             ':to'
                         ),true
                     )->setParameters(array('from' => $vars['start'], 'to' => $vars['end']));
                     };

And so far the data gets transmitted as i would like to have it. But the query dosn't get build right. and i don't know how to add my between query correctly... so as far as i see it, your solution dos not help me any futher because it only takes care of transmit my data somehow. But how do i receive them backendsite and add it to dthe serachcriteria or querybuilder?

[curry684]

I personally have no experience whatsoever with column searches so can't really help you there. In practice I always just create a custom filter form, much more powerful and flexible.

[Exellent1988]

Okay just to help others: I got it working like this:

Backend:

 ->add('timestamp', DateTimeColumn::class, [
                'label' => 'Erfassung',
                'className' => 'width_10 ',
                'orderable' => true,
                'searchable' => true,
                'format' => 'd.m.Y',

            ])
->createAdapter(ORMAdapter::class, [
                'entity' => ArtikelArtikel::class,
                'query' => function (QueryBuilder $builder, DataTableState $state) {
                    $term = $state->getSearchColumns();
                    $vars = ['start' => '', 'end'=> date('Y-m-d\TH:i:s+00:00')];
                    if ( isset($term['timestamp']) ){
                        $vars= json_decode($term['timestamp']['search'],true);
                        $state->setColumnSearch($state->getDataTable()->getColumn(7),'',false);  // Set the correct COLUMN NUMBER

                    };
                    $builder
                        // ->addSelect('a')
                        // ->addSelect('s')
                        ->select('a')
                        ->from(ArtikelArtikel::class, 'a')
                        ->leftJoin('a.stammartikelid', 's') // IRRELEVANT
                        ->leftJoin('a.kategorieid', 'k') // IRRELEVANT
                        ->leftJoin('a.status', 'state') // IRRELEVANT
                        ->andWhere('a.timestamp BETWEEN :timestart AND :timeend')
                        ->setParameter('timestart',$vars['start'])
                        ->setParameter('timeend',$vars['end'])
                        ;

Frontend:

   var start = picker.startDate.format('YYYY-MM-DDT00:00:00+00:00');
   var end = picker.endDate.format('YYYY-MM-DDT00:00:00+00:00');
 $('#dt').DataTable().column(7).search(JSON.stringify({'start': start,'end':end})); // Use Correct Column Number and the 

[aniskasmi]

@Exellent1988 Do you have your complet JS Code because when the DataTable is init first time with 'initDataTables', i store the datatable on var, but when i want to search on, the error popup 'DataTables warning: Non-table node initialisation (DIV).' is return, i import correctly all plugins files, if you can just send your js code with initDataTables code thx

  var datatable = $('#data-table-sse');

        var start = new Date();
        var end = new Date();

        datatable.initDataTables({{ datatable_settings(datatable) }}, {
            responsive: true,
            fixedColumns: true,
            dom: 'Bfrtip',
            buttons: [
                {
                    text: 'Exporter en Excel',
                    action: $.fn.initDataTables.exportBtnAction('excel', {{ datatable_settings(datatable) }})
                }
            ]
        });

        datatable.DataTable().column(2).search('2121');
        datatable.draw();

 <script type="text/javascript" src="https://cdn.datatables.net/v/dt/jq-3.2.1/dt-1.10.16/datatables.min.js"></script>
    <script src="{{ asset('bundles/datatables/js/datatables.js') }}"></script>
    <script src="https://cdn.datatables.net/1.11.5/js/dataTables.bootstrap5.min.js"></script>
    <script src="https://cdn.datatables.net/responsive/2.2.9/js/dataTables.responsive.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.2.2/js/dataTables.buttons.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.2.2/js/buttons.print.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.2.2/js/buttons.html5.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/vfs_fonts.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.53/pdfmake.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.1.3/jszip.min.js"></script>

[curry684]

initDataTables returns a promise with the datatables instance, please refer to the docs: https://omines.github.io/datatables-bundle/#javascript