How to store the results of a date range in cache so that when I leave a page to another (pagination) Laravel yajra datatable sever side no longer launches the query in the database to display the results

Hello, I want that when I select a date range that laravel yajra datatable store the data in cache so that when I do the paging that yajra no longer run the query in the database.

COntroller:

public function paydrc_daterange(Request $request){

        $transactions = [];
        if(request()->ajax()) {
            $transactions = [];
            if(!empty($request->start_date)) {

                $dateStart = date('Y-m-d', strtotime($request->start_date));
                $dateEnd = date('Y-m-d', strtotime($request->end_date));
                $curl_post_data = ["dateStart"=>$dateStart,"dateEnd"=>$dateEnd,"action"=>$request->action];
                        
                $url ="http://127.0.0.1:8086/services/paydrc/daterange";
                $data = json_encode($curl_post_data);
                $ch=curl_init();
                curl_setopt($ch, CURLOPT_URL, $url);
                curl_setopt($ch, CURLOPT_CONNECTTIMEOUT ,0); 
                curl_setopt($ch, CURLOPT_TIMEOUT, 400); //timeout in seconds
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                curl_setopt($ch, CURLOPT_MAXREDIRS, 10);
                curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
                curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
                curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
                curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type:application/json'));
                $curl_response = curl_exec($ch);

                if ($curl_response == false) {
                    $transactions = [];
                }
                else {
                    $transactions = json_decode($curl_response,true);
                }
            } 
            return datatables()->of($transactions)->make(true);

        }
        if (Auth::user()->is_user == 3) {
            return view('_finance.transaction.paydrc_daterange');
        }
    }
    ```
    JS
    ```
    <script>
    $(function() {
        $("#start_date").daterangepicker({
                singleDatePicker: true,
                showDropdowns: true,
                minYear: 1901,
                maxYear: parseInt(moment().format("YYYY"),12)
            }
        );
        $("#end_date").daterangepicker({
                singleDatePicker: true,
                showDropdowns: true,
                minYear: 1901,
                maxYear: parseInt(moment().format("YYYY"),12)
            }
        );
    });

    var CSRF_TOKEN = $('meta[name="csrf-token"]').attr('content');
    $(document).ready(function(){

        load_data();
        function load_data(dateStart = '', dateEnd = '' , action = ''){
            $('#empTable').DataTable({
                processing: true,
                serverSide: true,
                ajax: {
                    url:"{{  route('finance.paydrc.daterange') }}",
                    data:{start_date:dateStart, end_date:dateEnd, action:action}
                },
                columns: [
                    // {class : "text-left ps-4",data: 'created_at', name: 'created_at'},
                    {class : "text-left ps-4", data: 'id', name: 'id'},
                        {data: 'merchant_code', name: 'merchant_code'},
                        {data: 'customer_details', name: 'customer_details'},
                        {data: 'amount', name: 'amount'},
                        {data: 'currency', name: 'currency'},
                        {data: 'action', name: 'action'},
                        {data: 'method', name: 'method'},
                        {data: 'status', name: 'status'},
                        {data: 'thirdparty_reference', name: 'thirdparty_reference'},
                        {data: 'paydrc_reference', name: 'paydrc_reference'},
                        {data: 'switch_reference', name: 'switch_reference'},
                        {data: 'telco_reference', name: 'telco_reference'},
                        {data: 'created_at', name: 'created_at'},
                        {data: 'updated_at', name: 'updated_at'},
                        {data: 'status_description', name: 'status_description'},
                ]
            });
        }
        $('#generate').click(function(){
            var dateStart=document.getElementById('start_date').value;
            var dateEnd=document.getElementById('end_date').value;
            var action = $(".action:checked").val();

            if(dateStart != '' && dateEnd != '' &&  action != ''){
                $('#empTable').DataTable().destroy();
                load_data(dateStart, dateEnd , action);
            } else{
                alert('Both item is required');
            }
        });
        $('#reset').click(function(){
            $('#start_date').val('');
            $('#end_date').val('');
            $('.action:checked').prop('checked', false);
            $('#empTable').DataTable().destroy();
            load_data();
        });

    });

</script>

LaryAI

3 years ago

Level 58

To store the results of a date range in cache so that when you leave a page to another (pagination) Laravel yajra datatable server-side no longer launches the query in the database to display the results, you can use Laravel's cache system. Here's how you can do it:

First, you need to add the use Illuminate\Support\Facades\Cache; statement at the top of your controller file.
Then, you can modify your paydrc_daterange function to use the cache. Here's an example:

public function paydrc_daterange(Request $request)
{
    $transactions = [];
    if (request()->ajax()) {
        $transactions = Cache::remember('transactions_' . md5(json_encode($request->all())), 60, function () use ($request) {
            $transactions = [];
            if (!empty($request->start_date)) {
                $dateStart = date('Y-m-d', strtotime($request->start_date));
                $dateEnd = date('Y-m-d', strtotime($request->end_date));
                $curl_post_data = ["dateStart" => $dateStart, "dateEnd" => $dateEnd, "action" => $request->action];

                $url = "http://127.0.0.1:8086/services/paydrc/daterange";
                $data = json_encode($curl_post_data);
                $ch = curl_init();
                curl_setopt($ch, CURLOPT_URL, $url);
                curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 0);
                curl_setopt($ch, CURLOPT_TIMEOUT, 400); //timeout in seconds
                curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
                curl_setopt($ch, CURLOPT_MAXREDIRS, 10);
                curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false);
                curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
                curl_setopt($ch, CURLOPT_POSTFIELDS, $data);
                curl_setopt($ch, CURLOPT_HTTPHEADER, array('Content-Type:application/json'));
                $curl_response = curl_exec($ch);

                if ($curl_response == false) {
                    $transactions = [];
                } else {
                    $transactions = json_decode($curl_response, true);
                }
            }
            return $transactions;
        });
        return datatables()->of($transactions)->make(true);
    }
    if (Auth::user()->is_user == 3) {
        return view('_finance.transaction.paydrc_daterange');
    }
}

In this example, we're using the Cache::remember method to store the results of the query in the cache for 60 seconds. The cache key is generated based on the request parameters using the md5 function. If the cache already contains the data, it will be returned from the cache instead of running the query again.

Note that you can adjust the cache time to suit your needs. Also, make sure to clear the cache when necessary, for example, when new data is added to the database.

That's it! With this solution, the results of the query will be stored in the cache, and when you leave the page and come back, the data will be retrieved from the cache instead of running the query again.

Please or to participate in this conversation.