How to configure jqGrid (4.x) to be a TreeView

Question

I have taken the demo code and tried to modify it to suit my needs, but unfortunately I am unable to get the desired results.

The tree grid is loading and 'binding' but there seems to be an issue with the hierarchy during expand and collapse (I'd prefer that it stay expanded by default), looks like it's making another ajax request for the data which it shouldn't be doing.

The first call to get the data retrieves everything needed to function.

DTO:

[Serializable]
public class Company
{
    public string CompanyId { get; set; }

    public string CompnayName { get; set; }

    public bool expanded { get; set; }

    public bool isLeaf { get; set; }

    public string level { get; set; }

    public string parent { get; set; }

    public static Company FromDataReader(OracleDataReader r)
    {
        return new Company
        {
            CompanyId = r["companyid"] is DBNull ? null : r["companyid"].ToString(),
            CompnayName = r["companyname"] is DBNull ? null : r["companyname"].ToString(),
            level = r["company_level"] is DBNull ? null : r["company_level"].ToString(),
            parent = r["parent_company_id"] is DBNull ? null : r["parent_company_id"].ToString(),
            isLeaf = Convert.ToInt64(r["child_count"]) == 0 ? true : false,
            expanded = true
        };
    }

}

Controller:

[HandleJsonException]
public JsonResult CompanyService()
{
    var profiler = MiniProfiler.Current;

    using (profiler.Step("Companies"))
    {
        var q = Repository.GetCompanies(SimpleSessionPersister.User.Id).ToList();

        var result = (from c in q
                      select new string[]
                             {
                                 Convert.ToString(c.CompanyId),
                                 c.CompnayName,
                                 c.level,
                                 c.parent,
                                 Convert.ToString(c.isLeaf),
                                 Convert.ToString(c.expanded)
                             }).ToArray();

        var jsonData = new
        {
            rows = result
        };

        return Json(jsonData, JsonRequestBehavior.AllowGet);
    }


}

Grid initialization:

var companyGrid;

$(document).ready(function () {
var col_names = ['CompanyId', 'Company'];
var col_model = [
                    { name: 'CompanyId', index: 'CompanyId', width: 1, hidden: true, key: true },
                    { name: 'Company', index: 'Company', width: 300, }
                ];

companyGrid = $('#treegridCompanies');

companyGrid.jqGrid({
    url: '/xxx/yyy/zzz/',
    datatype: 'json',
    ajaxGridOptions: { contentType: "application/json" },
    jsonReader: {
        id: "CompanyId",
        cell: "",
        root: function (obj) { return obj.rows; },
        page: function () { return 1; },
        total: function () { return 1; },
        records: function (obj) { return obj.rows.length; },
        repeatitems: true
    },
    colNames: col_names,
    colModel: col_model,
    height: '100%',
    rowNum: 10000,
    sortable: false,
    treeGrid: true,
    ExpandColumn: 'Company'
});

Grid Json Data:

{"rows":[["754568001","BrownStone Root","1","1736451491","False","True"],["2974914503","American Hotel Register - Administration","2","754568001","True","True"],["2976495739","Andre Dev Catalogs","2","754568001","True","True"],["2129814647","DARCY CO.","2","754568001","True","True"],["1736764899","EDITEST","2","754568001","False","True"],["1736764905","BrownStone - Kirsten Luisi","2","754568001","False","True"],["2977223369","TEST NEW BrownStone 2","2","754568001","True","True"],["2977223203","TESTING NEW BrownStone COMPANY","2","754568001","True","True"],["2161775149","B2BApp -  JONES","3","1736764905","False","True"],["2975929355","B2BApp - Bill Hirsch","3","1736764905","True","True"],["2975929365","B2BApp DEMO - Bill Hirsch","3","1736764905","True","True"],["1736764898","BrownStone - TBD - Dir of Accounting","3","1736764899","False","True"],["1736764903","BrownStone - Tom Packer","3","1736764905","False","True"],["2051802334","B2BApp - Alan Champagne","4","1736764903","True","True"],["2059110298","B2BApp - Jack Noriega","4","1736764903","True","True"],["2051800719","B2BApp - Steve Werner","4","1736764903","True","True"],["2129810951","KIRSTEN DEMO","4","2161775149","False","True"],["1736764897","BrownStone - Matt Fanning","4","1736764898","False","True"],["1737913153","BrownStone - Ramon Boatwright","4","1736764903","True","True"],["1736764996","BrownStone Hotels - Administration","4","1736764903","False","True"],["1766184823","ASSET MANAGED","5","1736764897","True","True"],["2977073604","CHECKBOOK GL PERIOD TEST","5","2129810951","True","True"],["2006901783","D&C Project Managers","5","1736764897","True","True"],["2976570081","DEMO CHECKBOOK","5","2129810951","False","True"],["2220489862","DEMO REPORTS","5","2129810951","True","True"],["2078446743","DISABLED BrownStone ACCOUNTS","5","1736764897","True","True"],["1736764902","BrownStone - Bob Alter","5","1736764996","False","True"],["1736764695","BrownStone Corporate Office","5","1736764897","True","True"],["2005626695","BrownStone HOTELS","5","1736764897","False","True"],["1736765065","BrownStone HOTELS","5","1736764897","False","True"],["2010587742","BrownStone HOTELS","5","1736764897","False","True"],["1736765062","BrownStone HOTELS","5","1736764897","False","True"],["1766200934","BrownStone HOTELS","5","1736764897","False","True"],["1736765059","BrownStone HOTELS","5","1736764897","False","True"],["1736765060","BrownStone HOTELS","5","1736764897","False","True"],["1736022491","BrownStone RVPS","5","1736764897","False","True"],["2977230825","TESTDEFAULTREGION1","5","2129810951","True","True"],["1735853749","COURTYARD  OLD TOWN SAN DIEGO","6","1736765060","True","True"],["1735854977","COURTYARD AIRPORT FRESNO","6","1736765062","True","True"],["1735879751","COURTYARD BY MARRIOTT OXNARD VENTURA","6","1736765062","True","True"],["1735854022","COURTYARD LAX","6","1736765062","True","True"],["1735853966","Courtyard Marriott -  Riverside","6","1736765060","True","True"],["1735952557","COURTYARD MARRIOTT LYNNWOOD","6","1736765062","True","True"],["1735879861","COURTYARD SANTA FE","6","1736765062","True","True"],["1766207732","Crowne Plaza Englewood","6","1736765059","True","True"],["1766210117","Crowne Plaza Ft. Magruder","6","1736765059","False","True"],["2131080915","DOUBLETREE HOTEL NASHVILLE","6","1736765060","True","True"],["1766212404","DOUBLETREE MINNEAPOLIS","6","1736765059","True","True"],["1766210705","EMBASSY SUITES CHICAGO","6","1736765059","True","True"],["1735853689","EMBASSY SUITES LAX","6","1736765062","True","True"],["1735952640","HAMPTON INN DENVER SE","6","2010587742","True","True"],["1735952441","HAMPTON INN PUEBLO","6","2010587742","True","True"],["1735880135","HAWTHORN SUITES KENT","6","1736765062","True","True"],["1735879490","HAWTHORN SUITES SACRAMENTO","6","1766200934","True","True"],["1766204777","HILTON DEL MAR","6","1736765060","True","True"],["1735953002","HILTON GARDEN INN LAKE OSWEGO","6","1766200934","True","True"],["1766205907","HILTON HUNTINGTON","6","1736765059","True","True"],["1735855012","HOLIDAY INN BOISE","6","1736765060","True","True"],["1735952366","HOLIDAY INN CRAIG","6","1736765065","True","True"],["1735854051","HOLIDAY INN EXPRESS OLD TOWN","6","1736765060","True","True"],["1735854128","HOLIDAY INN HOLLYWOOD","6","1736765062","True","True"],["1735854687","HOLIDAY INN PRICE","6","1736765060","True","True"],["1735854586","HOLIDAY INN PROVO","6","1736765060","True","True"],["1735854730","HOLIDAY INN ROCHESTER","6","1736765059","True","True"],["1735853861","Holiday Inn San Diego Downtown","6","1736765060","True","True"],["1735853545","HOLIDAY INN SAN DIEGO MISSION VALLEY STADIUM","6","1736765060","True","True"],["1735952275","HOLIDAY INN SELECT RENTON","6","1736765062","True","True"],["2058220502","HYATT NEWPORTER","6","2005626695","True","True"],["1735854840","Kahler Hotel - Rochester","6","1736765059","True","True"],["1740354058","Kahler Inn & Suites","6","1736765059","True","True"],["2977230876","KIRSTEN DEMO","6","2976570081","True","True"],["1735854224","Marriott - Ogden - DO NOT SEND PO\u0027s TO THIS ACCOUNT","6","1736765060","True","True"],["1735854556","Marriott - Park City","6","1736765060","True","True"],["1735854251","Marriott - Provo","6","1736765060","True","True"],["1735854354","Marriott - Salt Lake City","6","1736765060","True","True"],["1766213001","MARRIOTT GREENSPOINT","6","1736765060","True","True"],["1735879564","MARRIOTT NAPA VALLEY","6","1736765060","True","True"],["1766219965","MARRIOTT PHILADELPHIA WEST","6","2005626695","True","True"],["1735854205","MARRIOTT PORTLAND","6","1766200934","True","True"],["1735854270","MARRIOTT PUEBLO","6","1736765065","True","True"],["1735854104","MARRIOTT RIVERSIDE","6","1736765060","True","True"],["1735854900","MARRIOTT ROCHESTER","6","1736765059","True","True"],["1766220488","MARRIOTT TROY","6","2005626695","True","True"],["1766224945","MARRIOTT TYSONS CORNER","6","2005626695","True","True"],["2263401804","MIREPOIX RESTAURANT","6","1736765065","True","True"],["2977223544","NEWMANAGEMENTCOMPANY9","6","2976570081","True","True"],["2977231310","NEWSHUSTERMANAGEMENT2","6","2976570081","True","True"],["2977064515","NO CB Fiscal test 2","6","2976570081","True","True"],["2977064482","NO CB Fiscal Years test","6","2976570081","True","True"],["2977064560","NO FISCAL 3","6","2976570081","False","True"],["2977223526","NO FISCAL COMPANY 9","6","2976570081","True","True"],["1736772841","Ogden Eccles Conference Center","6","1736765060","True","True"],["1775491350","ONTARIO AIRPORT MARRIOTT","6","1736765060","True","True"],["1736765061","PJ SNOWDEN","6","1736022491","True","True"],["2085767632","RESIDENCE INN BEVERLY HILLS","6","1736765062","True","True"],["2010453862","RESIDENCE INN MANHATTAN BEACH","6","1736765062","True","True"],["1735854993","RESIDENCE INN OXNARD","6","1736765062","True","True"],["2134841291","RESIDENCE INN ROCHESTER","6","1736765059","True","True"],["1735879595","RESIDENCE INN SACRAMENTO","6","1766200934","True","True"],["1736020746","RESIDENCE INN SAN DIEGO","6","2010587742","True","True"],["1735854665","SHERATON CITY CENTRE SALT LAKE CITY","6","1736765060","True","True"],["2733122057","SHERATON HOTEL CERRITOS","6","1766200934","True","True"],["2010609773","SUE GREGORY","6","1736022491","True","True"],["1736764901","BrownStone - Mark Mance","6","1736764902","False","True"],["2529863176","TEST","6","2976570081","True","True"],["2977223362","TESTING77","6","2976570081","True","True"],["1736020559","TEXTILE CARE SERVICES ROCHESTER","6","1736765059","True","True"],["1736020575","TEXTILE CARE SERVICES SALT LAKE CITY","6","1736765060","True","True"],["1766225201","VALLEY RIVER INN","6","2005626695","True","True"],["1766211379","WYNDHAM GREENSPOINT","6","1736765060","True","True"],["2977064674","NO FISCAL 4","7","2977064560","False","True"],["1736764900","BrownStone - Evan Studer","7","1736764901","True","True"],["2977223101","TESTING 777","7","2977064560","True","True"],["2977223196","TESTING11","7","2977064560","True","True"],["2977223169","TESTING7","7","2977064560","True","True"],["2977231370","TESTNEWCROWN","7","1766210117","True","True"],["2977222940","TESTING3","8","2977064674","False","True"],["2977222949","TESTING4","8","2977064674","True","True"],["2977222965","TESTING6","8","2977064674","True","True"]]}

Thank you, Stephen

After Code Review

[Serializable]
public class Company
{
    public string CompanyId { get; set; }

    public string CompanyName { get; set; }

    public bool isLeaf { get; set; }

    public string level { get; set; }

    public string parent { get; set; }

    public static Company FromDataReader(OracleDataReader r)
    {
        return new Company
        {
            CompanyId = r["companyid"] is DBNull ? null : r["companyid"].ToString(),
            CompanyName = r["companyname"] is DBNull ? null : r["companyname"].ToString(),
            level = r["company_level"] is DBNull ? null : r["company_level"].ToString(),
            parent = r["parent_company_id"] is DBNull ? null : r["parent_company_id"].ToString(),
            isLeaf = Convert.ToInt64(r["child_count"]) == 0 ? true : false
        };
    }

}

companyGrid.jqGrid({
 url: '/xxx/yyy/zzz/',
 datatype: 'json',
 ajaxGridOptions: { contentType: "application/json" },
 jsonReader: {
     id: "CompanyId",
     cell: "",
     root: function (obj) { return obj.rows; },
     page: function () { return 1; },
     total: function () { return 1; },
     records: function (obj) { return obj.rows.length; },
     repeatitems: true
 },
 beforeProcessing: function (data) {
    var rows = data.rows, i, l = rows.length;
    for (i = 0; i < l; i++) {
        rows[i][5] = true; // set expanded column
        rows[i][6] = true; // set loaded column
    }
 },
 colNames:  ['CompanyId', 'Company'],
 colModel: [
                { name: 'CompanyId', index: 'CompanyId', width: 1, hidden: true, key: true },
                { name: 'Company', index: 'Company', width: 300, }
            ],
 height: '200px',
 autowidth: true,
 rowNum: 10000,
 sortable: false,
 treeGrid: true,
 treeGridModel: 'adjacency',
 ExpandColumn: 'Company'
});

{"rows":[["754568001","BrownStone Root","1","1736451491","False","True"],["1736764899","EDITEST","2","754568001","False","True"],["1736764898","BrownStone - TBD - Dir of Accounting","3","1736764899","False","True"],["1736764897","BrownStone - Matt Fanning","4","1736764898","False","True"],["1736022491","BrownStone RVPS","5","1736764897","False","True"],["1736765061","PJ SNOWDEN","6","1736022491","True","True"],["2010609773","SUE GREGORY","6","1736022491","True","True"],["1736764695","BrownStone Corporate Office","5","1736764897","True","True"],["1736765059","BrownStone HOTELS","5","1736764897","False","True"],["1735854730","HOLIDAY INN ROCHESTER","6","1736765059","True","True"],["1735854840","Kahler Hotel - Rochester","6","1736765059","True","True"],["1735854900","MARRIOTT ROCHESTER","6","1736765059","True","True"],["1736020559","TEXTILE CARE SERVICES ROCHESTER","6","1736765059","True","True"],["1740354058","Kahler Inn & Suites","6","1736765059","True","True"],["1766205907","HILTON HUNTINGTON","6","1736765059","True","True"],["1766207732","Crowne Plaza Englewood","6","1736765059","True","True"],["1766210117","Crowne Plaza Ft. Magruder","6","1736765059","False","True"],["2977231370","TESTNEWCROWN","7","1766210117","True","True"],["1766210705","EMBASSY SUITES CHICAGO","6","1736765059","True","True"],["1766212404","DOUBLETREE MINNEAPOLIS","6","1736765059","True","True"],["2134841291","RESIDENCE INN ROCHESTER","6","1736765059","True","True"],["1736765060","BrownStone HOTELS","5","1736764897","False","True"],["1735853545","HOLIDAY INN SAN DIEGO MISSION VALLEY STADIUM","6","1736765060","True","True"],["1735853749","COURTYARD  OLD TOWN SAN DIEGO","6","1736765060","True","True"],["1735853861","Holiday Inn San Diego Downtown","6","1736765060","True","True"],["1735853966","Courtyard Marriott -  Riverside","6","1736765060","True","True"],["1735854051","HOLIDAY INN EXPRESS OLD TOWN","6","1736765060","True","True"],["1735854104","MARRIOTT RIVERSIDE","6","1736765060","True","True"],["1735854224","Marriott - Ogden - DO NOT SEND PO\u0027s TO THIS ACCOUNT","6","1736765060","True","True"],["1735854251","Marriott - Provo","6","1736765060","True","True"],["1735854354","Marriott - Salt Lake City","6","1736765060","True","True"],["1735854556","Marriott - Park City","6","1736765060","True","True"],["1735854586","HOLIDAY INN PROVO","6","1736765060","True","True"],["1735854665","SHERATON CITY CENTRE SALT LAKE CITY","6","1736765060","True","True"],["1735854687","HOLIDAY INN PRICE","6","1736765060","True","True"],["1735855012","HOLIDAY INN BOISE","6","1736765060","True","True"],["1735879564","MARRIOTT NAPA VALLEY","6","1736765060","True","True"],["1736020575","TEXTILE CARE SERVICES SALT LAKE CITY","6","1736765060","True","True"],["1736772841","Ogden Eccles Conference Center","6","1736765060","True","True"],["1766204777","HILTON DEL MAR","6","1736765060","True","True"],["1766211379","WYNDHAM GREENSPOINT","6","1736765060","True","True"],["1766213001","MARRIOTT GREENSPOINT","6","1736765060","True","True"],["1775491350","ONTARIO AIRPORT MARRIOTT","6","1736765060","True","True"],["2131080915","DOUBLETREE HOTEL NASHVILLE","6","1736765060","True","True"],["1736765062","BrownStone HOTELS","5","1736764897","False","True"],["1735853689","EMBASSY SUITES LAX","6","1736765062","True","True"],["1735854022","COURTYARD LAX","6","1736765062","True","True"],["1735854128","HOLIDAY INN HOLLYWOOD","6","1736765062","True","True"],["1735854977","COURTYARD AIRPORT FRESNO","6","1736765062","True","True"],["1735854993","RESIDENCE INN OXNARD","6","1736765062","True","True"],["1735879751","COURTYARD BY MARRIOTT OXNARD VENTURA","6","1736765062","True","True"],["1735879861","COURTYARD SANTA FE","6","1736765062","True","True"],["1735880135","HAWTHORN SUITES KENT","6","1736765062","True","True"],["1735952275","HOLIDAY INN SELECT RENTON","6","1736765062","True","True"],["1735952557","COURTYARD MARRIOTT LYNNWOOD","6","1736765062","True","True"],["2010453862","RESIDENCE INN MANHATTAN BEACH","6","1736765062","True","True"],["2085767632","RESIDENCE INN BEVERLY HILLS","6","1736765062","True","True"],["1736765065","BrownStone HOTELS","5","1736764897","False","True"],["1735854270","MARRIOTT PUEBLO","6","1736765065","True","True"],["1735952366","HOLIDAY INN CRAIG","6","1736765065","True","True"],["2263401804","MIREPOIX RESTAURANT","6","1736765065","True","True"],["1766184823","ASSET MANAGED","5","1736764897","True","True"],["1766200934","BrownStone HOTELS","5","1736764897","False","True"],["1735854205","MARRIOTT PORTLAND","6","1766200934","True","True"],["1735879490","HAWTHORN SUITES SACRAMENTO","6","1766200934","True","True"],["1735879595","RESIDENCE INN SACRAMENTO","6","1766200934","True","True"],["1735953002","HILTON GARDEN INN LAKE OSWEGO","6","1766200934","True","True"],["2733122057","SHERATON HOTEL CERRITOS","6","1766200934","True","True"],["2005626695","BrownStone HOTELS","5","1736764897","False","True"],["1766219965","MARRIOTT PHILADELPHIA WEST","6","2005626695","True","True"],["1766220488","MARRIOTT TROY","6","2005626695","True","True"],["1766224945","MARRIOTT TYSONS CORNER","6","2005626695","True","True"],["1766225201","VALLEY RIVER INN","6","2005626695","True","True"],["2058220502","HYATT NEWPORTER","6","2005626695","True","True"],["2006901783","D&C Project Managers","5","1736764897","True","True"],["2010587742","BrownStone HOTELS","5","1736764897","False","True"],["1735952441","HAMPTON INN PUEBLO","6","2010587742","True","True"],["1735952640","HAMPTON INN DENVER SE","6","2010587742","True","True"],["1736020746","RESIDENCE INN SAN DIEGO","6","2010587742","True","True"],["2078446743","DISABLED BrownStone ACCOUNTS","5","1736764897","True","True"],["1736764905","BrownStone - Kirsten Luisi","2","754568001","False","True"],["1736764903","BrownStone - Tom Packer","3","1736764905","False","True"],["1736764996","BrownStone Hotels - Administration","4","1736764903","False","True"],["1736764902","BrownStone - Bob Alter","5","1736764996","False","True"],["1736764901","BrownStone - Mark Mance","6","1736764902","False","True"],["1736764900","BrownStone - Evan Studer","7","1736764901","True","True"],["1737913153","BrownStone - Ramon Boatwright","4","1736764903","True","True"],["2051800719","B2BApp - Steve Werner","4","1736764903","True","True"],["2051802334","B2BApp - Alan Champagne","4","1736764903","True","True"],["2059110298","B2BApp - Jack Noriega","4","1736764903","True","True"],["2161775149","B2BApp -  JONES","3","1736764905","False","True"],["2129810951","KIRSTEN DEMO","4","2161775149","False","True"],["2220489862","DEMO REPORTS","5","2129810951","True","True"],["2976570081","DEMO CHECKBOOK","5","2129810951","False","True"],["2529863176","TEST","6","2976570081","True","True"],["2977064482","NO CB Fiscal Years test","6","2976570081","True","True"],["2977064515","NO CB Fiscal test 2","6","2976570081","True","True"],["2977064560","NO FISCAL 3","6","2976570081","False","True"],["2977064674","NO FISCAL 4","7","2977064560","False","True"],["2977222940","TESTING3","8","2977064674","False","True"],["2977222949","TESTING4","8","2977064674","True","True"],["2977222965","TESTING6","8","2977064674","True","True"],["2977223101","TESTING 777","7","2977064560","True","True"],["2977223169","TESTING7","7","2977064560","True","True"],["2977223196","TESTING11","7","2977064560","True","True"],["2977223362","TESTING77","6","2976570081","True","True"],["2977223526","NO FISCAL COMPANY 9","6","2976570081","True","True"],["2977223544","NEWMANAGEMENTCOMPANY9","6","2976570081","True","True"],["2977230876","KIRSTEN DEMO","6","2976570081","True","True"],["2977231310","NEWSHUSTERMANAGEMENT2","6","2976570081","True","True"],["2977073604","CHECKBOOK GL PERIOD TEST","5","2129810951","True","True"],["2977230825","TESTDEFAULTREGION1","5","2129810951","True","True"],["2975929355","B2BApp - Bill Hirsch","3","1736764905","True","True"],["2975929365","B2BApp DEMO - Bill Hirsch","3","1736764905","True","True"],["2129814647","DARCY CO.","2","754568001","True","True"],["2974914503","American Hotel Register - Administration","2","754568001","True","True"],["2976495739","Andre Dev Catalogs","2","754568001","True","True"],["2977223203","TESTING NEW BrownStone COMPANY","2","754568001","True","True"],["2977223369","TEST NEW BrownStone 2","2","754568001","True","True"]]}

Oleg · Accepted Answer

You have some problems in the server part which will be forwarded to the JSON.

The first simple problem is in the Company class. You made typing error and used CompnayName instead of CompanyName. As the results the data of the first revision of your code can't read the CompanyName. After changing to string[] and repeatitems: true the company name will be read.

The next problem is that you forgot to include treeGridModel: 'adjacency' option of jqGrid and the input data will be interpreted in the wrong way.

The next problem in JSON data is that you should additionally set loaded column of the grid to true. The hidden loaded column is the next column after the expanded. If you want always to load all data you can fix the problem in the beforeProcessing

beforeProcessing: function (data) {
    var rows = data.rows, i, l = rows.length;
    for (i = 0; i < l; i++) {
        rows[i][6] = true; // set loaded column
    }
}

In the same way you can set expanded column to true for example (rows[i][5] = true) it you want reduce the size of the transferred data.

The last problem which I can see in your JSON data is the order of nodes. You should understand that Tree Grid works very simple. It places all the rows in exactly the same order in the grid how you post the data. The children can be just hidden, but all children have to be directly after its parent node. So you should change the order of the data placed in the server response.

How to configure jqGrid (4.x) to be a TreeView

Answers (1)

Related Questions