DataTables grouping with group sorting and sub group sorting

deadend5001

I have a table I have added grouping to with sub-totals and I have gotten sorting working, but it doesn't sort within each group.

I have a sample here of this: http://live.datatables.net/weyanuci/1/

If I choose to group by say, defense, I then want to be able to sort on win rate % for example. And it sort by the group then sort internally via the same column so I can see which player, for example, has the highest winrate in that group.

Any help is MOST appreciated. I have been working on trying to figure this out for a while now...

kthorngren

I made a couple changes when grouping by defense in this example:
http://live.datatables.net/jotamasi/1/edit

I noticed that if you start with No Group then order by Player Name, hold shift then order by % the table orders as you describe. But the same doesn't happen if you group the rows. I didn't take the time to debug why but the columns.render function you have for the % column doesn't seem to be working properly for the sort operation. So I commented it out. I also added orderFixed for both:

            "order": [[groupColumn, 'asc']],
            "orderFixed": [groupColumn, 'asc'],

I left the order option in as the sorting icons aren't updated properly when toggling from no group to group by defense.

Kevin

deadend5001

The toggle is there because that’s how the users want it. They want to be able to pick the groupings on the fly and then sort accordingly. If I group by defense the goal is to have the % rate at the top and then within the group sort highest to lowest. Right now the sorting in my version just stops at the group level and doesn’t sort the children of the group.

This is the requirement and orderFixed doesn’t work the way the users expect it. It leaves the defenses sorted in alpha and then just sorts inside each group.

kthorngren

Right now the sorting in my version just stops at the group level and doesn’t sort the children of the group.

Did you see my note about this and did you try removing the columns.render option to test?

Does the example I provided work the way you want? I only fixed the defense group. My understanding of what you want is to check the Group by Defense option then be able to sort the % column within each group which I believe my example accomplishes.

Kevin

deadend5001

When I remove the render when you sort it loses the groupings. Sure it sorts properly but the display it gives is useless.

The example you gave me is not what I am looking for.

I want to be able to click group by defense then sort descending in the % column it should bring the highest percentage to the top and say there are 3 players in that group, it sorts all 3 players from highest % to lowest % within the group.

kthorngren

I think I now understand what you are asking for.

I want to be able to click group by defense then sort descending in the % column it should bring the highest percentage to the top

I didn't catch this requirement earlier. So you want to sort the groups by the total percentage you display using startRender. That's what you are using winRatesAR[row[groupColumn]] for. It would help and save us time if you gave some details of what you are doing in your code. This is a clever idea but I didn't pay attention to it earlier.

See if this example is closer to what you want:
http://live.datatables.net/jotamasi/3/edit

I created another hidden column that contains the values from winRatesAR. Remove the orderFixed and used orderData on the% column. This way it will first sort by the hidden column (winRatesAR values) then by the percent value in the % column. I also changed the % column columns render to set the sort and type detection operations to a number. Otherwise it uses the renderer.

Kevin

deadend5001

This looks to be it! Let me test here locally and see if this resolves all of it.

deadend5001

Confirmed that does fix the sorting issue. Looks like I'll need to add another column for W and L and do the same logic?

deadend5001

Here's the fully working version for all who care to see.

http://live.datatables.net/jotamasi/4/

You can group by player or defense and then sort by W, L or % correctly.

kthorngren

Looks good. Building the arrays with the RowGroup totals is a good idea.

Kevin

deadend5001

Something new is going on. Check out this example.

http://live.datatables.net/duyivinu/1/

When you group by defense it does it perfectly as expected. If I sort by % descending things are falling out of groupings. Example...defense placement "Khmun Orion Miho" gets split into two different groups, the first one with 2 records in it, the second with 8 records in it. If you use the filter and search for "miho" they show up back in the same group.

Definitely something funky going on and I have spent the last 4 days trying to figure it out. Please help!

kthorngren

I'm not sure what this is doing in column 8:

                { "visible": false, 
                 data: null,
                    render: function(data, type, row, meta) {
                          return winRatesAR[row[groupColumn]] === undefined ? 0 : winRatesAR[row[groupColumn]];
                  }
                }

However it doesn't seem to be what you want to sort on for the row groups to work. I changed the orderData your percent column to use the groupColumn and seems to have fixed the grouping issue.

                { "width": "7%", 
                  "orderData": [ 1, 5 ],
                  render: function(data, type, row, meta) {
                        // Make sure to do type detection on the numeric value so type is num for sorting.
                        if (type === 'sort' || type === 'type') {
                          return data.replace('%', '') * 1;  // Use number for sorting and type detection
                        }
                        // Format data for display
                        return $.fn.dataTable.render.number( ',', '.', 1, '', "%" ).display(data);
                  }
                },

Here is the updated example:
http://live.datatables.net/duyivinu/3/edit

Kevin

deadend5001

I just tested your version and it's not sorting properly. If you sort by % it should be taking the highest/lowest % and moving it to the top/bottom accordingly while retaining the grouping. Additionally it should be sorting within the group.

kthorngren

Which I guess is the need for using this as part of the sorting:

{ "visible": false,
 data: null,
    render: function(data, type, row, meta) {
          return winRatesAR[row[groupColumn]] === undefined ? 0 : winRatesAR[row[groupColumn]];
  }
}

For some reason sorting by this column seems to be separating the group by 0% and any other numeric value. I spent a little time looking but didn't track down why. If I get a chance later I'll look again.

Kevin

deadend5001

Thanks Kevin. In a perfect world the sorting would sort the group first then sort within each group according to the direction. All without losing the grouping of course

kthorngren

If you sort by % it should be taking the highest/lowest % and moving it to the top/bottom accordingly

Forgot about that requirement

Change to "orderData": [ 8, 1, 5 ],. Does this work the way you want?
http://live.datatables.net/fayaraqo/1/edit

Kevin

deadend5001

That appears to do what I want it to do. So this is just multi-dimensional sorting? Sorting by the group hidden % first, then by defense then by non-hidden %?

kthorngren

Sorting by the group hidden % first, then by defense then by non-hidden %?

Yes it is.

EDIT: You may or may not need to do this for you other columns. The key is to get the grouped column involved in the sorting so the rows don't become separated.

Kevin

middeugger

@deadend5001 @kthorngren You guys solution is working fine.

May I know how we manage these things if you have two rowgroup ?

For me when there is only a single aggregation(1 rowgroup/groupColumn), it works fine. The image I attached have two aggregation(2 group column).Actually it displays in correct order. But you can see that rowgroup 'SEA' is splitted.The last SEA should be included in the first group.

Please help me to solve this

colin

This example shows how to have multi-level grouping. If that doesn't get you going, we're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

middeugger

Hi @colin ,

Actually it is not about the multilevel. As per this discussion when we sort descending/ascending the rowgroup and the contents should change the order.
@deadend5001 and @kthorngren done it [here].(http://live.datatables.net/fayaraqo/1/edit "here")

For me the rowgroup is splitting in multilevel group. I don't want to split. It should show in the rowgroup itself. How we can manage that ?

The picture I uploaded on my recent post, actually all the **SEA **should come together.

kthorngren

The picture I uploaded on my recent post, actually all the **SEA **should come together.

Sounds like your table order is not correct. Use the order option or order() API to set the order. You will need to order first by the column with UAE then by the column that has Sea. If this doesn't help then please provide a link to your page or a test case replicating the issue so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

middeugger

@kthorngren

The rows should order by the value in the revenue. But you can see the row has splitted.

The order I given is 12,1,2,9

12 - Duplicate column
1- Country
2 - Service
9 - Revenue

So here the grouping by Country and Service [1,2].

Is there anything wrong in the data ?

kthorngren

As I said you need to set the the first column order to the first rowGroup.dataSrc and the second to the second dataSrc. Try this:

1- Country - the column with Taiwan
2 - Service - the column with Air and Sea
12 - Duplicate column
9 - Revenue

Columns 12 and 9 should be sorted in that order within the groups.

Is there anything wrong in the data ?

If you still need help we will need to see a test case showing an example of your data and configuration in order to help.

Kevin