jqgrid client side sorting with server side paging - data disappears

Asked 27/1, 2012 at 7:45 Answered 21/11, 2012 at 12:0

it states in jqgrid documentation that the code below should allow local sorting with server side paging; the grid data disappears on paging; this question has been asked before with no clear answer - suggestions to use loadonce:true means that paging is turned off - I need paging

EDITED LATER TO SHOW COMPLETE html page and json response (Im now running this from a php/mysql backend).

my full html page

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>JQGrid Test</title>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" type="text/css" href="../dojoproject/jquery-ui-1.8.16.custom/css/start/jquery-ui-1.8.16.custom.css">
<link rel="stylesheet" type="text/css" href="jquery.jqGrid-4.3.1/css/ui.jqgrid.css">
<style type="text/css">
html, body {
    margin: 0;
    padding: 0;
    font-size: 90%;
}
</style>
<script type="text/javascript" src="../dojoproject/jquery-ui-1.8.16.custom/js/jquery-1.6.2.min.js" ></script>
<script type="text/javascript" src="../dojoproject/jquery-ui-1.8.16.custom/js/jquery-ui-1.8.16.custom.min.js" ></script>
<script type="text/javascript" src="jquery.jqGrid-4.3.1/js/i18n/grid.locale-en.js" ></script>
<script type="text/javascript" src="jquery.jqGrid-4.3.1/js/jquery.jqGrid.min.js" ></script>
<script type="text/javascript" src="../dojoproject/jqGrid-4.1.2/js/JSON-js/json2.js" ></script>

<script>

 $(function() {
$('#table').jqGrid({
   jsonReader : {
    repeatitems: false,
    cell:"",
    id:"0"
   },   
    height:'auto',
    url:'/jqgrid/orderdetails.php',
    postData:{test:'value'},
    datatype: 'json',
    mtype: 'POST',
    rownumbers:true,
    rownumWidth:35,
    colNames:['OrderID','UnitPrice','Quantity','Discount','ProductName'],
    colModel :[ 
      {name:'OrderID', index:'OrderID',search:false,sorttype:'integer'}, 
      {name:'UnitPrice', index:'UnitPrice',editable:true,sorttype:'float'}, 
      {name:'Quantity', index:'Quantity',sorttype:'int'}, 
      {name:'Discount', index:'Discount',sorttype:'int'},
      {name:'ProductName', index:'ProductName'}   
    ],
    sortname: 'OrderID ',
    rowNum:5,
    sortorder: 'asc',
    width:'100%',
    height:'200',
    viewrecords: true,
    gridview: true,
    caption: 'NorthWind Orders',
    scrollOffset:18,
    multiselect:true,
    pager:'pager'
    ,cellEdit:true,
    cellsubmit:'clientArray',
    afterSaveCell:function(rowid, cellname, value, iRow, iCol){
    },
       onPaging: function() {
        $("#table").setGridParam({datatype:'json'}).trigger("reloadGrid");
        },               
    loadComplete: function (data) {  
        $("#table").setGridParam({datatype:'local'}).trigger("reloadGrid");
        } 
    });
 });

</script>

</head>
<body>

<table id='table'></table>
<div id='pager'></div>
</body>
</html>

response on 1st load is

{"page":"1","total":431,"records":2155,"rows":[{"OrderID":"1024811","UnitPrice":"14.0000","Quantity":"12","Discount":"0"},{"OrderID":"1024842","UnitPrice":"9.8000","Quantity":"10","Discount":"0"},{"OrderID":"1024872","UnitPrice":"34.8000","Quantity":"5","Discount":"0"},{"OrderID":"1024914","UnitPrice":"18.6000","Quantity":"9","Discount":"0"},{"OrderID":"1024951","UnitPrice":"42.4000","Quantity":"40","Discount":"0"}]}

response from page 2:

{"page":"2","total":431,"records":2155,"rows":[{"OrderID":"1025041","UnitPrice":"7.7000","Quantity":"10","Discount":"0"},{"OrderID":"1025051","UnitPrice":"42.4000","Quantity":"35","Discount":"0.15"},{"OrderID":"1025065","UnitPrice":"16.8000","Quantity":"15","Discount":"0.15"},{"OrderID":"1025122","UnitPrice":"16.8000","Quantity":"6","Discount":"0.05"},{"OrderID":"1025157","UnitPrice":"15.6000","Quantity":"15","Discount":"0.05"}]}

Bibcock answered 27/1, 2012 at 7:45 Comment(7)

The reason of the problem could be not only a bug in jqGrid, but also bug in the data provided by the server part, formatters which you use in the colModel and so on. Could you include more full definition of the jqGrid and also two JSON response returned from the server: the first one will be respond on the request of the first page and the second JSON response from the request of the second page. Having all the data one can reproduce your problem. Nevertheless I personally has less sense in combination of the local sorting and server side paging. – Piaffe 27/1, 2012 at 7:58

I prefer client sorting to avoid need to implement server side sorting, plus for performance - unless someone suggests a pain free multi column server sort (and multi column search :-)) using linq to sql – Bibcock 27/1, 2012 at 8:17

The JSON response which you posted is not full, so it can't be used to reproduce the problem. I see now that you use scroll:1 which in not paging of data. It's virtual scrolling. I think you should remove the option to make the program working. Sorting of data on the server side is much more effective from the performance point of view. Moreover if the user request to get first page of data sorted by come column you have to sort the data first and then get the first page of the results to provide correct response. – Piaffe 27/1, 2012 at 9:8

Which multi column sorting you need to have in the example? – Piaffe 27/1, 2012 at 9:9

Thanks oleg, I just added the scroll after 1st posting the question, normal paging vs scroll makes no difference to my sort issue. I will modify to request smaller payload so full response is not ridiculuosly large here. I wdnt mind and will probably in a later stage build server side sorting, just looking for an elegant server side solution to search and sort by variable columns (using linq to sql - I don't like seeing too many if..conditionals checking for search and sort parameters) – Bibcock 27/1, 2012 at 15:0

To use server side paging you need mostly just add ORDER BY in the SQL statement. If you use SqlCommand it will be mostly easy. If you use Entity Framework it will be just OrderBy(sidx + " " + sord). The whole sorting with paging will be

MyCoxtext.Skip ("it." + sidx + " " + sord, "@skip", new ObjectParameter ("skip", (page - 1) * rows)).Top ("@limit", new ObjectParameter ("limit", rows))

(see here). If you use Linq to SQL you can use can use LambdaExpression or Dynamic LINQ. – Piaffe 27/1, 2012 at 17:50

Hi Oleg, I added the full page and responses - do you see something wrong here? – Bibcock 28/1, 2012 at 13:22

First of all I want repeat that I don't recommend you to use local sorting and the server side paging. I find that the user can wrong interpret the result of sorting.

Nevertheless, if your customer agree with restriction which have the combination of local sorting and the server side paging and if you really need to implement that, I can suggest you the following solution:

onPaging: function() {
    $(this).setGridParam({datatype: 'json'}).triggerHandler("reloadGrid");
},
loadComplete: function (data) {
    var $this = $(this);
    if ($this.jqGrid('getGridParam', 'datatype') === 'json') {
        // because one use repeatitems: false option and uses no
        // jsonmap in the colModel the setting of data parameter
        // is very easy. We can set data parameter to data.rows:
        $this.jqGrid('setGridParam', {
            datatype: 'local',
            data: data.rows,
            pageServer: data.page,
            recordsServer: data.records,
            lastpageServer: data.total
        });

        // because we changed the value of the data parameter
        // we need update internal _index parameter:
        this.refreshIndex();

        if ($this.jqGrid('getGridParam', 'sortname') !== '') {
            // we need reload grid only if we use sortname parameter,
            // but the server return unsorted data
            $this.triggerHandler('reloadGrid');
        }
    } else {
        $this.jqGrid('setGridParam', {
            page: $this.jqGrid('getGridParam', 'pageServer'),
            records: $this.jqGrid('getGridParam', 'recordsServer'),
            lastpage: $this.jqGrid('getGridParam', 'lastpageServer')
        });
        this.updatepager(false, true);
    }
}

If you would don't use repeatitems: false the code which fills data parameter of jqGrid will be a little longer, but it will work.

Piaffe answered 28/1, 2012 at 17:17 Comment(4)

just one small glitch in this solution - on sorting pages other than page 1, the pager skips back to page 1 but the data is ok – Bibcock 29/1, 2012 at 1:20

@chohi: the problem with the page number could be very easy fixed. See the updated code of my answer. – Piaffe 29/1, 2012 at 10:12

thanks Oleg - this works perfectly; I also take your advice and will later use server sorting – Bibcock 29/1, 2012 at 17:22

@chohi: You are welcome! You can additionally post the reference to my answer on the page where you found the code which you use. I think other that for other users the solution could be interesting. Best wishes! – Piaffe 29/1, 2012 at 17:34

The above solution works fine except in the case where in if we are at the last page of the grid, say i have 3 rows being displayed in the last page although the page can accommodate 5 rows.

Now if I try to do client side sort, the last page will be filled with 2 additional rows and the total 5 rows will be sorted. I would say, may be the last fetched records are stored in buffer so this is occurring. As fix for this, onPagination, clear the grid before making the grid as "json", like

clickOnPagination = function() { $(this).jqGrid("clearGridData"); $(this).setGridParam({datatype: 'json'}).triggerHandler("reloadGrid"); }

and in the source code comment the lines
$t.p.records = 0;$t.p.page=1;$t.p.lastpage=0; in clearGridData function so that the next pagination will work properly.

Pear answered 21/11, 2012 at 12:0 Comment(1)

Nice catch, but make changes at the source code is not a good idea for maintenance. – Sitting 30/10, 2014 at 17:26

Recommended topics

Hot tags