server side datatable with saveState error if current data is less than previously save page number

alamadcs

I have a php server side script to generate data for datatable
my data table has saveSate=true for every thing means if I an going to page 5 it will save page 5 for that page.
next time when I am coming to this page it will try to get data with offset 50 considering pagelength=10
so it is generating error at PHP side. when applying offset 50 on less than 50 records

how can we handle this situation

allan

I don't quite understand. Are you saying that the number of records is changing between loads? i.e. it used to have more than 50, but now is less? Or that it isn't saving the page length?

Allan

alamadcs

I have listing page containing 51 records of pending requests.
If and am coming at page 6 there will be one record, and saveState will save my current page as page#6
not I opened that one record at 6th page, and approved it, that record is no more pending so it will not come again in pending requests. after approving it will be redirected to listing page and this time total records are 50 instead of 51 and page#6 was saved as per previous saveState but with 50 records there is no 6th page.
so it should show 5 page (or 1st page. I am not sure about all scenarios may be in some cases there will be 1 or zero records)

alamadcs

?

kthorngren

so it is generating error at PHP side

What is the error?

Are you using a Datatables supplied server side processing script?

I would look at adding code to the server script to handle the error condition. You can then decide whether to return the 5th page or the 1st page based on your requirements.

Kevin

alamadcs

its simple, client side requesting 6th page of 10 records, but server has only 20 records
Line 68 has error
The problem is explained from line 70 to 86

<?php
public function loaddata()
 {
    $cols = [
        'id',
        'empno',
        'emp_name',
        'user_name',
        'status_name',
        'project_name',
        'donor_name',
        'owner_name',
        'manager_name',
        'issue_date',
        'expiry_date' 
    ];
    $this->viewBuilder()->setLayout('ajax');
    $qs = $this->request->getQueryParams(); 

    //ordering
    $orderby = [];
    if (isset($qs['iSortCol_0'])) {
        for ($i = 0; $i < intval($qs['iSortingCols']); $i++) {
            if ($qs['bSortable_' . intval($qs['iSortCol_' . $i])] == "true") {
                $orderby[$cols[intval($qs['iSortCol_' . $i])]] = ($qs['sSortDir_' . $i] === 'asc' ? 'asc' : 'desc');
            }
        }
    }
    $this->paginate['order'] = [$qs['mDataProp_1'] => $qs['iDisplayLength']];


    //where conditions
    $conds = [];
    $datatypes = [];
    if (isset($qs['sSearch']) && $qs['sSearch'] != "") {
        for ($i = 0; $i < count($cols); $i++) {
            $conds[$cols[$i] . " LIKE"] = "%" . $qs['sSearch'] . "%";
            $datatypes[$cols[$i]] = 'string';
        }
    }

    /* Individual column filtering */
    $colConds = [];
    for ($i = 0; $i < count($cols); $i++) {
        if (isset($qs['bSearchable_' . $i]) && $qs['bSearchable_' . $i] == "true" && $qs['sSearch_' . $i] != '') {
            $colConds[$cols[$i] . " LIKE"] = "%" . $qs['sSearch_' . $i] . "%";
            $datatypes[$cols[$i]] = 'string';
            //sWhere .= "`".$aColumns[$i]."` LIKE '%".mysql_real_escape_string($qs['sSearch_'.$i])."%' ";
        }
    }


    $query =    $this->EMPTABLE->find()
        ->select($cols);

    if (!empty($conds)) {
        $conds = ['OR' => $conds];
    }
    if (!empty($colConds)) {
        $conds = array_merge($conds, $colConds);
    }


    $filteredQuery = $query->where($conds, $datatypes);

    $filteredCount = $filteredQuery->count();

    $data = $this->paginate($filteredQuery);//error on this line

    //previous in previos state page_number was 5 (each page contain 10 records) so 51 to 60
    //but this time there are only 20 records so it becomes select * from table limit 10 offset 50 (while total records are 20 only)

    //I tried thing and partially fixed, not it is showing records instead of error but no page is selected and it says 
    //"showing 51 to 10 of 20 entries"
    //by alterign $this->paginate['page'] = last_possible_page;  here at server side but as client side still have page 5 thats why it says "showing 51 to 10 of 20 entries" and in pagination 2 items are there but none is selected as it looks for 5 which is not there
    /*      
        $totalPages = ceil($filteredCount / $qs['iDisplayLength']);
        if ($qs['iDisplayStart'] >= $filteredCount && $totalPages > 0) { 
            $lastPageStart = ($totalPages - 1) * $qs['iDisplayLength'];
            $qs['iDisplayStart'] = $lastPageStart;
            $this->paginate['page'] = $totalPages;
        }
     */
    //is there any way to pass pagenumber from here to datatable?
    //bcz client side requested 6th page of 10 records
    //while database have only 20, so I will page 11 to 20 records and also correct client side paginagtion 

    $totalCount = $this->EMPTABLE->find('all')->count();

    $dtData = array(
        "draw" => intval($qs['sEcho']),
        'recordsTotal'      =>  $totalCount,
        'recordsFiltered'   =>  $filteredCount,
        'data'              =>  $data
    );

    echo json_encode($dtData);
    exit;
}