Yajra Datatable Date Range Filters

Question

when i change date from date picker it hit ajax request but not filtering my data
This my Lead Listing listing view where i am showing datatable:

<script>
$(document).ready(function() {
    $('#startDate').datepicker();
    $('#endDate').datepicker();

    var table = $('#myDataTable').DataTable({
        processing: true,
        serverSide: true,
        dom: 'Bfrtip',
        buttons: [
            'copy', 'csv', 'excel', 'pdf', 'print'
        ],
        ajax: '{{ route('leadData') }}',
        columns: [
            {data: 'first_name', name: 'first_name'},
            {data: 'last_name', name: 'last_name'},
            {data: 'number', name: 'number'},
            {data: 'email', name: 'email'},
            {data: 'studio_id', name: 'studio_id'},
            {data: 'created_at', name: 'created_at'},
        ]
    });

    $.fn.dataTable.ext.search.push(
        function( settings, data, dataIndex ) {
            console.log('data');
            var min = $('#startDate').datepicker('getDate');
            var max = $('#endDate').datepicker('getDate');
            var startDate = new Date(data[5]);
            if (min == null && max == null) return true;
            if (min == null && startDate <= max) return true;
            if (max == null && startDate >= min) return true;
            if (startDate <= max && startDate >= min) return true;
            return false;
        }
    );

    $('#startDate, #endDate').change( function() {
        table.draw();
    });
});

This is my lead controller method for showing datatable results :

    /**
 * Display a listing of the resource.
 *
 * @return \Illuminate\Http\Response
 */
public function leadData() {

    $leads = Lead::with('studio')->get();
    // dd($leads[0]->created_at);
    return Datatables::of($leads)->editColumn('studio_id', function($leads){
        $leadname = $leads->studio->name;
        return $leadname;
    })->make(true);
}

This is my Accessor for changing date format:

    /**
 * Display a listing of the resource.
 *
 * @return \Illuminate\Http\Response
 */
public function getCreatedAtAttribute($value) {
    return date('m/d/Y', strtotime($this->attributes['created_at']));
}

My table is
leads:
id, first_name, last_name, number, email, studio_id, created_at, updated_at

score 1 · Answer 1 · edited Nov 18 '20 at 05:29

You don't need change format in server side, you can do this in javascript

// set a common class for both id
$('.datepicker').datepicker({
  dateFormat: 'yy-mm-dd'
});

var myDataTable = $('#myDataTable').DataTable({
  dom: 'Bfrtip',
  "oSearch": {"bSmart": false},
  processing: true,
  serverSide: true,
  ajax: {
    url: "{{ route('leadData') }}",
    type: 'GET',
    data: function (d) {
      // read start date from the element
      d.from = $('#startDate').val();
      // read end date from the element
      d.to = $('#endDate').val();
    }
  },
  columns: [
  {data: 'first_name', name: 'first_name'},
  {data: 'last_name', name: 'last_name'},
  {data: 'number', name: 'number'},
  {data: 'email', name: 'email'},
  {data: 'studio_id', name: 'studio_id'},
  {data: 'created_at', name: 'created_at'},
  ],
});

// set on change date picker
$('.datepicker').on('change', function() {
  var from = $("#startDate").val();
  var to = $("#endDate").val();
  if(from && to) {
    myDataTable.draw();
  }
});

The server side you could use like this

$startDate = $request->get('from');
$endDate = $request->get('to');

$query = Lead::with('studio');

if($startDate && $endDate) {
    $query->whereDate('lead.created_at', '>=', $startDate)
    ->whereDate('lead.created_at', '<=', $endDate);
}

$leads = $query->get();

Yajra Datatable Date Range Filters

1 Answers1