Getting and sorting from JSON on jqGrid

Question

I'm new with jQuery and jqGrid and I'm getting two types of JSON content from another system with the following formats:

Option #1

{
    "@timestamp": "2012-03-27T16:19:26Z",
    "@toplevelentries": 40000,
    "items": [
        {
            "@entryid": "1-B933790B1DC265ED8025725800728CC5",
            "@unid": "B933790B1DC265ED8025725800728CC5",
            "@noteid": "1E76E",
            "@position": "1",
            "@read": true,
            "@siblings": 40000,
            "$17": "Aaron, Adam",
            "InternetAddress": "consurgo@compleo.net",
            "OfficeCountry": "Namibia"
        },
        {
            "@entryid": "2-9D93E80306A7AA88802572580072717A",
            "@unid": "9D93E80306A7AA88802572580072717A",
            "@noteid": "19376",
            "@position": "2",
            "@read": true,
            "@siblings": 40000,
            "$17": "Aaron, Dave",
            "InternetAddress": "gratia@incito.co.uk",
            "OfficeCountry": "Brazil"
        },
        {
            "@entryid": "3-FAFA753960DB587A80257258007287CF",
            "@unid": "FAFA753960DB587A80257258007287CF",
            "@noteid": "1D842",
            "@position": "3",
            "@read": true,
            "@siblings": 40000,
            "$17": "Aaron, Donnie",
            "InternetAddress": "vociferor@nequities.net",
            "OfficeCountry": "Algeria"
        }
    ]
}

Here the jqgrid I have is defined like this:

$().ready(function(){
    jQuery("#list2").jqGrid({
        url:'./xGrid2.xsp/peoplejson',
        datatype: "json",
        colNames:['#','InternetAddress','Name','OfficeCountry'],
        colModel:[
            {name:'@position',index:'@position', width:50, sortable:true},
            {name:'InternetAddress',index:'InternetAddress', width:200, sortable:true},
            {name:'$17',index:'$17', width:200, sortable:true},
            {name:'OfficeCountry',index:'OfficeCountry', width:200, sortable:true}
        ],
        jsonReader: {
            repeatitems: false,
            root: "items",
            id: "@position",
            records: "@toplevelentries", 
            page:2,
            total:5
        },
        sortname: '@position',
        sortorder: "desc",
        height:500,
        rowNum:50,
        rowList:[50,100,150],
        caption:"JSON Example",
        pager: '#pager2'
    });
});

I get the data but sorting and paging is not working.

Option 2

[
    {
        "@entryid": "1-B933790B1DC265ED8025725800728CC5",
        "@unid": "B933790B1DC265ED8025725800728CC5",
        "@noteid": "1E76E",
        "@position": "1",
        "@read": true,
        "@siblings": 40000,
        "@form": "Person",
        "$17": "Aaron, Adam",
        "InternetAddress": "consurgo@compleo.net",
        "OfficeCountry": "Namibia"
    },
    {
        "@entryid": "2-9D93E80306A7AA88802572580072717A",
        "@unid": "9D93E80306A7AA88802572580072717A",
        "@noteid": "19376",
        "@position": "2",
        "@read": true,
        "@siblings": 40000,
        "@form": "Person",
        "$17": "Aaron, Dave",
        "InternetAddress": "gratia@incito.co.uk",
        "OfficeCountry": "Brazil"
    },
    {
        "@entryid": "3-FAFA753960DB587A80257258007287CF",
        "@unid": "FAFA753960DB587A80257258007287CF",
        "@noteid": "1D842",
        "@position": "3",
        "@read": true,
        "@siblings": 40000,
        "@form": "Person",
        "$17": "Aaron, Donnie",
        "InternetAddress": "vociferor@nequities.net",
        "OfficeCountry": "Algeria"
    }
]

here the jqgrid I have is defined like this:

$().ready(function(){
    jQuery("#list2").jqGrid({
        url:'./xGrid4.xsp/peoplejson',
        datatype: "json",
        colNames:['InternetAddress','Name', 'OfficeCountry'],
        colModel:[
            {name:'InternetAddress',index:'InternetAddress', width:200},
            {name:'$17',index:'$17', width:200},
            {name:'OfficeCountry',index:'OfficeCountry', width:200}
        ],
        jsonReader: {
            repeatitems: false,
            id: "InternetAddress",
            root: function (obj) { return obj; },
            page: function (obj) { return 1; },
            total: function (obj) { return 1; },
            records: function (obj) { return obj.length; }
        },
        caption:"JSON Example",
        height:500,
        sortable:true,
        rowNum:250,
        rowList:[250,500,750,1000],
        pager: '#pager2'                            
    });
});

Again not sure if I'm defining correctly my jqrig object since here I don't have a root element on JSON.

In both options sorting is not working and I cannot get populated correctly the total of records and pages on Pager element.

Any help will be appreciated.

so what is the doubt?Always Select code segment and press Ctrl+K to format the code for better readability — Rajesh, Mar 27 '12 at 16:41

score 4 · Accepted Answer · answered Mar 27 '12 at 20:39

You have two main problems. The first one: the sorting. It's very easy to solve. jqGrid with datatype: 'json' ask the get only one page of sorted data. If the user change the sorting order or go to the next page for example the new request with another parameters will be send to the server.

If you want that the data will be loaded only once from the server and then will be locally sorted or paged you need just add loadonce: true option to the grid. You have to returns still correctly sorted data to have correct sort order initially.

For performance reason you should always include gridview: true to the list of used options.

The next your problem with two formats of JSON data returned from the server you can solve in the simple way. You can modify the jsonReader to the following:

jsonReader: {
    repeatitems: false,
    id: "InternetAddress",
    root: function (obj) {
        if ($.isArray(obj)) {
            return obj;
        }
        if ($.isArray(obj.items)) {
            return obj.items;
        }
        return [];
    },
    page: function () { return 1; },
    total: function () { return 1; },
    records: function (obj) {
        if ($.isArray(obj)) {
            return obj.length;
        }
        if ($.isArray(obj.items)) {
            return obj.items.length;
        }
        return 0;
    }
}

The corresponding demos: the first one and the second one use the same code, but read different formatted JSON data (the data which you posted). Both produce the same results. I changed rowNum to rowNum: 2 to demonstrate that local paging work. Additionally you can consider to use height: 'auto' instead of fixed value height: 500. The rowNum which you use will define the height of the grid.

Additionally I included in the second demo the line

$("#list2").jqGrid('filterToolbar',
    {stringResult: true, defaultSearch: 'cn', searchOnEnter: false});

to demonstrate the power of local data filtering. I added one more option ignoreCase: true to have case insensitive filtering/searching of data.

Excellent answer @Oleg... just FYI; what I had before was because I found some answers from you to others developers :) — PSolano, Mar 28 '12 at 09:51
Is there any reason why your two examples http://www.ok-soft-gmbh.com/jqGrid/PSolano1.htm and http://www.ok-soft-gmbh.com/jqGrid/PSolano2.htm don't display of on pager section? It works fine if I'm not using JSON datatype. — PSolano, Apr 02 '12 at 18:09
@PSolano: Both examples use `loadonce: true`. The setting `loadonce: true` change the `datatype` to `'local'` and work later only the the data which are loaded at the beginning. So the number of items and the number of pages will be fixed based on the number of items really loaded from the server. — Oleg, Apr 02 '12 at 18:52
Tks for quick reply. I was referring to the text "View X - Y of " here the code for records on JSONReader`records: function (obj) { if ($.isArray(obj)) { return obj.length; } if ($.isArray(obj.items)) { return obj.items.length; } return 0; }` Also if I do an alert(obj.length) I get the the total of records from my JSON object. — PSolano, Apr 02 '12 at 20:02
@PSolano: If one use `loadonce: true` **all the values** will be recalculated based on the the number of items revived from the server at the first load and the current jqGrid settings like `rowNum` (the number of rows per page). — Oleg, Apr 02 '12 at 20:07

Getting and sorting from JSON on jqGrid

1 Answers1