5

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"}]}
Oleg
  • 220,925
  • 34
  • 403
  • 798
Pete_ch
  • 1,301
  • 2
  • 28
  • 39
  • 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. – Oleg Jan 27 '12 at 07: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 – Pete_ch Jan 27 '12 at 08: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. – Oleg Jan 27 '12 at 09:08
  • Which multi column sorting you need to have in the example? – Oleg Jan 27 '12 at 09:09
  • 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) – Pete_ch Jan 27 '12 at 15:00
  • 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](http://stackoverflow.com/a/5501644/315935)). If you use Linq to SQL you can use can use `LambdaExpression` or `Dynamic LINQ`. – Oleg Jan 27 '12 at 17:50
  • Hi Oleg, I added the full page and responses - do you see something wrong here? – Pete_ch Jan 28 '12 at 13:22

2 Answers2

7

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.

Oleg
  • 220,925
  • 34
  • 403
  • 798
  • 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 – Pete_ch Jan 29 '12 at 01:20
  • @chohi: the problem with the page number could be very easy fixed. See the updated code of my answer. – Oleg Jan 29 '12 at 10:12
  • thanks Oleg - this works perfectly; I also take your advice and will later use server sorting – Pete_ch Jan 29 '12 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! – Oleg Jan 29 '12 at 17:34
0

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.

venugopal
  • 427
  • 6
  • 13