Guriddo » Topic: Searching & Grouping

Searching & Grouping

Guriddo
Searching & Grouping

This topic has 3 replies, 2 voices, and was last updated 4 years, 8 months ago by guriddo.support.

Creator

Topic
July 12, 2020 at 10:22 am #127444

rayhal
Participant

When using search in the pager jqGrid sends fields ‘sidx’ and ‘sord’ to the server. We use these fields in our SQL, example:

“SELECT…. ORDER BY sidx sord”

This works fine. However, when using ‘Group by..’ in colMenu the field ‘sidx’ also contains the sort order. I can see why you do this because you can group on multiple fields. However, it breaks MySQL because the query looks like this:

“SELECT…. ORDER BY sidx sord sord”

We now check for ‘asc’ or ‘desc’ in field ‘sidx and if found don’t use field ‘sord’. However, it would be much better to do this in jqGrid, ie. leave ‘sord’ empty for grouping.

Rayhal
Creator

Topic

Viewing 3 replies - 1 through 3 (of 3 total)

Author

Replies

July 13, 2020 at 8:37 am #127446

rayhal

Participant

Update to this issue. Go to this section of code:

if(ts.p.grouping){
 $(ts).jqGrid('groupingSetup');
 var grp = ts.p.groupingView, gi, gs="";
 for(gi=0;gi<grp.groupField.length;gi++){
   var index = grp.groupField[gi];
   $.each(ts.p.colModel, function(cmIndex, cmValue){
     if (cmValue.name === index && cmValue.index){
      index = cmValue.index;
     }
   });
   gs += index +" "+grp.groupOrder[gi]+", ";
  }
  prm[pN.sort] = gs + prm[pN.sort];
}

if(ts.p.grouping){

$(ts).jqGrid('groupingSetup');

var grp = ts.p.groupingView, gi, gs="";

for(gi=0;gi<grp.groupField.length;gi++){

var index = grp.groupField[gi];

$.each(ts.p.colModel, function(cmIndex, cmValue){

if (cmValue.name === index && cmValue.index){

index = cmValue.index;

}

});

gs += index +" "+grp.groupOrder[gi]+", ";

}

prm[pN.sort] = gs + prm[pN.sort];

}

The line gs += index +” “+grp.groupOrder[gi]+”, “; will result in ‘sidx’ like this id asc, (always comma at end). If inserted into SQL command this results in an error. I changed this line as follows:

if(gi>0){gs+=", "+index+" "+grp.groupOrder[gi];}else{gs+=index+" "+grp.groupOrder[gi];}

1	if(gi>0){gs+=", "+index+" "+grp.groupOrder[gi];}else{gs+=index+" "+grp.groupOrder[gi];}

Now you get correct ‘sidx’ like this id asc or id asc, name asc for multiple grouping. I also added prm.sord=’ ‘; after prm[pN.sort] = gs + prm[pN.sort]; so that sort order is only in ‘sidx’ and not in both ‘sidx’ and ‘sord’.

There is also an issue with grouping if you first sort a field (asc or desc) and then try to group. The ‘sidx’ is like this id asc, id (it picks up the field name that was sorted before grouping).

At the moment I’m not sure where this field name is coming from.

Rayhal

July 13, 2020 at 8:56 am #127447

rayhal

Participant

This seems to be the fix, add one line after if(ts.p.grouping){

if(ts.p.grouping){
    prm[pN.sort]="";
    $(ts).jqGrid('groupingSetup.............
    ........................................

if(ts.p.grouping){

prm[pN.sort]="";

$(ts).jqGrid('groupingSetup.............

........................................

This reply was modified 4 years, 8 months ago by rayhal.

Rayhal

July 13, 2020 at 11:55 am #127449

guriddo.support

Participant

Hello,

Thank you very much finding this problem.

I need to note that this problem exist only if the option sortname in grid options is not set. In case it is set there it is working correct. The sortname in grid options is used as a last sort column.

The fix provided here IMHO is not correct, since if the user want to add additional sorting it will be not send to the server.

We have fixed the problem in GitHub and you can try it.

The fix look like this

var tmpordarr = []; // add temp array to store the group sorting
for(gi=0;gi<grp.groupField.length;gi++) {
    var index = grp.groupField[gi];
    $.each(ts.p.colModel, function(cmIndex, cmValue) {
        if (cmValue.name === index && cmValue.index){
            index = cmValue.index;
        }
    });
    tmpordarr.push(index +" "+grp.groupOrder[gi]); // column push the sort string
}
gs = tmpordarr.join(); // build the grouping sort string
if( $.trim(prm[pN.sort]) !== "") { // if sortname is set add it with the sortorder
    prm[pN.sort] = gs + " ,"+prm[pN.sort];
} else { // is not remove the sort order in order to prevent any inconsistency
    prm[pN.sort] = gs;
    prm[pN.order] = "";
}

var tmpordarr = []; // add temp array to store the group sorting

for(gi=0;gi<grp.groupField.length;gi++) {

var index = grp.groupField[gi];

$.each(ts.p.colModel, function(cmIndex, cmValue) {

if (cmValue.name === index && cmValue.index){

index = cmValue.index;

}

});

tmpordarr.push(index +" "+grp.groupOrder[gi]); // column push the sort string

}

gs = tmpordarr.join(); // build the grouping sort string

if( $.trim(prm[pN.sort]) !== "") { // if sortname is set add it with the sortorder

prm[pN.sort] = gs + " ,"+prm[pN.sort];

} else { // is not remove the sort order in order to prevent any inconsistency

prm[pN.sort] = gs;

prm[pN.order] = "";

}

Kind Regards,
Will

Guriddo Support Team

Author

Replies

Viewing 3 replies - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.

Registered Users: 12,254
Forums: 7
Topics: 10,950
Replies: 23,150
Topic Tags: 324
Empty Topic Tags: 9

18 Users Online

Users: 5 Guests, 13 Bots

Stay connected with us in your favorite flavor!

Searching & Grouping

Search Forum

Recent Replies

Forum Statistics

UserOnline