Show foreign key text in jqGrid

Question

I have a jqGrid that I bind to a JSON datasource (WCF web service). The WCF method returns a list of ids. Below is an example of the JSON that is returned. As you can see it is a relationship of a user to a branch to a role i.e. A user can have different roles in different branches.

    [{"entityHashCode":null,"BranchId":25,"SysRoleId":1,"SysUserId":1},
{"entityHashCode":null,"BranchId":25,"SysRoleId":2,"SysUserId":1},
{"entityHashCode":null,"BranchId":26,"SysRoleId":1,"SysUserId":1]

Displaying this data in jqGrid is fine, but obviously I want to show the user the Branch and Role names rather than their ids. Unfortunately, changing the WCF so that it returns the data as JOINS is not an option because the WCF method may not change.

I also have access to 2 web service methods GetBranches and GetRoles both of which return arrays with the full details - I have to javascript arrays that I store this info into.

Is there a way that I can tell jqGrid to bind to my original array but somehow tell it to get the Branch and Role name from different datasources (the GetBranches and GetRoles arrays)?

LeftyX · Answer 1 · 2015-12-04T15:25:47.333

0

Why don't you just call GetBranches and GetRoles in your WCF method and build all the matches (relations), generating a new array (List of) object which contains BranchId, BranchName, SysRoleId, SysRoleName, SysUserId ?
You can return your new List of to jqGrid with just one call. It would be faster and easier to implement.

edited Dec 04 '15 at 15:25

answered May 30 '11 at 14:19

LeftyX

35,328
21
132
193

@LeftyX, thanks for your reply. This would be the way I would ordinarily do this but as I pointed out in my post, changing the WCF method is not an option. – pedrodg May 30 '11 at 14:24
@pedrodg: I guess your jqgrid lives in a asp.net page or asp.net MVC, right? – LeftyX May 30 '11 at 14:26
@LeftyX: Yes, it lives in an ASP.Net page – pedrodg May 31 '11 at 06:35
@pedrodg: Ok, so I guess you can call your WFC method from your action and there, in the action, you can do the transformation and join the collections to obtain a new one (as I suggested in my answer), and return it to your jqGrid. – LeftyX May 31 '11 at 06:56
@LeftyX: Thanks again for your reply. While this works and it is along the lines of what I'm currently doing, what I really wanted to know is if there is a native way in jqGrid. I've posted my solution below. What I'm suggesting is that maybe we extend jqGrid to do some mapping the way I have done. – pedrodg Jun 01 '11 at 06:33
@pedrodg: I am glad you've found a solution even if I do not agree in doing these things in jqGrid. I reckon that jquery is a grid, gets data and displays it. All the fetching and transformation must be done somewhere else, on the server-side.It's faster and easier to do that. – LeftyX Jun 01 '11 at 07:38
@LeftyX: I beg to differ. The main point you are missing in my question is as I stated "Unfortunately, changing the WCF so that it returns the data as JOINS is not an option because the WCF method may not change". As developers we do not always have the option to go in and modify the datasource. I agree that doing this on the server may be more efficient, however as developers working on enterprise projects we may not always have access to modifying the service layer. In my solution jqGrid is doing exactly what you say, it's gets data and diplays it. – pedrodg Jun 02 '11 at 06:52

score 0 · Accepted Answer · answered Jun 01 '11 at 07:24

I thing with the conversations I've had with LeftyX there doesn't seem to be a native way to do this in jqGrid, so I have created a method for doing a "JOIN" between objects in an array. The function is as follows:

function joinJSONFK (entities, fkProperties, fkLookupArrays) {

    function findValInAry(ary, idfield, value) {
        for (var i = 0; i < ary.length; i++) {
            if (value == ary[i][idfield]) {
                return ary[i];
            }
        }
        return null;
    };

    function applyFKProperties(entity, fkProperties, fkLookupArrays) {
        for (var i = 0; i < fkProperties.length; i++) {
            entity[fkProperties[i] + "Source"] = findValInAry(fkLookupArrays[i], fkProperties[i], entity[fkProperties[i]]);
        }
        return entity;
    }

    var entityary = [];
    if (!entities instanceof Array) {
        entities = applyFKProperties(entities);
        return entities[0];
    }
    else {
        for (var i = 0; i < entities.length; i++) {
            entities[i] = applyFKProperties(entities[i], fkProperties, fkLookupArrays);
        }
        return entities;
    }
}

You would use it as follows:

userRoleData = joinJSONFK(result, ["SysRoleId", "BranchId"], [GlobalRoles, GlobalBranches]);

Where "result" is an array of JSON objects with the following format:

[{"entityHashCode":null,"BranchId":25,"SysRoleId":1,"SysUserId":1},
{"entityHashCode":null,"BranchId":25,"SysRoleId":2,"SysUserId":1},
{"entityHashCode":null,"BranchId":26,"SysRoleId":1,"SysUserId":1]

And ["SysRoleId", "BranchId"] is an array of the foreign keys that need to be "JOINED" and [GlobalRoles, GlobalBranches] is an array containing the "lookup" data for the foreign keys.

GlobalRoles would look something like this:

[{"Name":"Admin","SysRoleId":1,"Description":"Some description"},
{"Name":"Role 2","SysRoleId":2,"Description":"Some description"},
{"Name":"A new role","SysRoleId":3,"Description":"Some description"},
{"Name":"Another Role","SysRoleId":4,"Description":"Some description"}]

And GlobalBranches would look something like this:

[{"BranchName":"Branch 25","BranchId":25,"Description":"describe the branch"},
{"BranchName":"Branch 26","BranchId":26,"Description":"describe the branch"},
{"BranchName":"Branch 27","BranchId":27,"Description":"describe the branch"}]

After calling the function, the "userRoleData" will lok something like this:

[{"entityHashCode":null,"BranchId":25,"SysRoleId":1,"SysUserId":1, "SysRoleIdSource":{"Name":"Admin","SysRoleId":1,"Description":"Some description"}, "BranchIdSource":{"BranchName":"Branch 25","BranchId":25,"Description":"describe the branch"}},
{"entityHashCode":null,"BranchId":25,"SysRoleId":2,"SysUserId":1}, "SysRoleIdSource":{"Name":"Role 2","SysRoleId":2,"Description":"Some description"}, "BranchIdSource":{"BranchName":"Branch 25","BranchId":25,"Description":"describe the branch"}},
{"entityHashCode":null,"BranchId":26,"SysRoleId":1,"SysUserId":1, "SysRoleIdSource":{"Name":"Admin","SysRoleId":1,"Description":"Some description"}, "BranchIdSource":{"BranchName":"Branch 26","BranchId":26,"Description":"describe the branch"}}]

This way have a nicely structured collection of objects.

Whilst this works and I am satisfied with it as an answer, if anybody can give other suggestions that don't involve changing the WCF - because it cannot change - I will gladly remove this as the correct answer. — pedrodg, Jun 02 '11 at 07:00

Show foreign key text in jqGrid

2 Answers2

Linked