Passing data from controller to view in laravel to filter data using "daterangepicker" from datatable

Question

I am using bootstrap "daterangepicker" for datatable which filters the table according to today, yesterday, all dates. Initially all the rows from the database is loaded into datatable.

I don't need all the rows to loaded from database initially. Instead load only today data first and
If i click yesterday it should load yesterday data from the database rather than filtering yesterday data from the datatable.

i have code as follows:

 $(document).ready(function() {
        var oTable=$("#example").DataTable({ "bInfo": false,
            "bLengthChange": false,
            "bSort": false,
            "responsive": true,
        });

        var startdate;
        var enddate;

    $('#reportrange').daterangepicker({
            ranges: {
              

                "Today": [moment(), moment()],

                'Yesterday': [moment().subtract(1, 'days'), moment().subtract(1, 'days')]

            },
        format: 'DD/MM/YYYY',

    },
    function(start, end,label) {
// Parse it to a moment
        var s = moment(start.toISOString());
        var e = moment(end.toISOString());
        startdate = s.format("YYYY-MM-DD");
        enddate = e.format("YYYY-MM-DD");
    });

    $('#reportrange').on('apply.daterangepicker', function(ev, picker) {



        startdate=picker.startDate.format('YYYY-MM-DD');
        enddate=picker.endDate.format('YYYY-MM-DD');
        oTable.draw();
    });
    $.fn.dataTableExt.afnFiltering.push(
        function( oSettings, aData, iDataIndex ) {
            if(startdate!=undefined){
// 0 here is the column where my dates are.
//Convert to YYYY-MM-DD format from DD/MM/YYYY
                var coldate = aData[2].split("/");
                var d = new Date(coldate[2], coldate[1]-1 , coldate[0]);
                var date = moment(d.toISOString());
                date =    date.format("YYYY-MM-DD");

//Remove hyphens from dates
                dateMin=startdate.replace(/-/g, "");
                dateMax=enddate.replace(/-/g, "");
                date=date.replace(/-/g, "");

console.log(dateMin, dateMax, date);

// run through cases to filter results
                if ( dateMin == "" && date <= dateMax){
                    return true;
                }
            else if ( dateMin =="" && date <= dateMax ){
                    return true;
                }
            else if ( dateMin <= date && "" == dateMax ){
                    return true;
                }
            else if ( dateMin <= date && date <= dateMax ){
                    return true;
                }

// all failed
                return false;
            }
        }
    )
        $("#example").show();
    });

<div class="input-prepend input-group" class="pull-right" style="background: #fff; cursor: pointer; padding: 5px 10px; border: 1px solid #ccc; width:auto">
                                <i class="glyphicon glyphicon-calendar fa fa-calendar one"></i>&nbsp;

                                <input type="text" style="width: 200px" name="reportrange" id="reportrange" class="form-control"  />
                                <span></span> <b class="caret"></b>
                            </div>
       
         <table id="example" class="table table-striped table-bordered table-condensed" cellspacing="0" width="100%" style="display: none">
                                <thead>
                                <tr>
                                    <th>Id</th>
                                    <th>User</th>
                                    <th>Created_at</th>
                                    <th>Field</th>
                                    <th>Status</th>

                                </tr>
                                </thead>
                                <tbody>
                                @foreach($auditMd5 as $md5r)
                                    <tr>
                                        <td>{{$md5r->id}}</td>
                                        <td>{{$md5r->user}}</td>
                                        <td>{{$md5r->created_at->format('d/m/Y')}}</td>
                                        <td>{{$md5r->field}}</td>
                                        <td>{{$md5r->status}}</td>


                                    </tr>
                                @endforeach
                                </tbody>
                            </table>
       

Is it possible to filter datatable using the daterangepicker directly from the database.

Answer 1

In your controller, you will create a method that will return the JSON data into to put into the table:

public function dt_data() {
    $auditMd5 = TableName::select('id',
        'column1',
        'column2',
        'column3',
        DB::raw('If you need explicit HTML, you will have to do it here.' AS html_element))
        ->orderBy('id')->get();

    //To the DataTable understand the data must be named 'aaData'
    return response()->json(['aaData' => $numeros]);
}

In the view, you will tell the DataTable that it will be receiving the data through JSON and that it will paginate all the information so it will only draw one page at a time, saving load time:

<script>
    $('#datatable').DataTable({
        ajax: '{{ asset('/route_to_controller/dt_data') }}',
        dataSource: 'lista',
        columns: [
            {"data": "column1", "class": "you-can-add-class-as-parameter"},
            {"data": "column2"},
            {"data": "column3"},
            {"data": "html_element"},
        ],
        paging: true,
        responsive: true,
    )};
</script>

Optionally if you need to jump to a specific page can add the page.jumpToData() DataTable plugin to the initialization options and it will jump to its corresponding page.

initComplete: function (settings, json) {
    table.page.jumpToData(column1, 0);
}

Hope it helps!!