User sorting after grouping while respecting groups

fox1

Hi everyone,

I am using datatables to have a table with 11 columns that is currently grouping entries of the same id together then sorting by date. If I were to enable user sorting, however, it breaks apart the groups. After looking for other examples on here, there are some that sort within the groups, but since my columns are fixed, this doesn't seem possible. I have the id, date, cost, revenue, profit, profitMargin, costPerAction, revenuePerAction, costPerView, countyName and stateName as my columns. It seems that I have to choose either to group and have the groups locked for user sorting or allow user sorting, but then I lose my groups. Do you have any suggestions?

colin

Hi @fox1 ,

Take a look at orderFixed - this allows you to set a column that gets ordered first before the user's ordering selection. See this example here.

Cheers,

Colin

fox1

Thank you. I actually already have this in my project. However, if I have this on, the user is unable to sort right now. Is there anything else you suggest? I have my groups working so that the entries with the same id are together and sorted by the date in a descending order. Is there something more that should be done? Basically the group column has other stuff in there like averages and totals, so I was hoping to be able allow the user to sort the grouped columns but not break up the groups, but the examples I keep seeing seem to either break up the groups or they are like mine where the groups are there, but the user is unable to sort. There was one example where the user can sort the contents within the groups, but not other attributes of the groups that would be in the group row.

Below is how I have the groups sorting currently which are great, but the users are unable to do other sorting. If I add a third column or more to sort, nothing happens.

orderFixed: [
                [groupColumn, 'asc'],
                [1, 'desc']
            ],

colin

Hi @fox1 ,

The orderFixed is definitely the way to go. I don't understand why your users are unable to sort with it set. As you can see in this example here, I've fixed the third column (it keeps the grouping), and have then added the second column. If you now order by Age, it flips the top two entries - "Nixon" and "Flynn", as they have the same job title.

If I've misunderstood something, please could you tweak that example (or create another) so that it demonstrates your problem.

Cheers,

Colin

fox1

Hi @colin , Thanks for your insights. It looks like you are doing this with a plugin to make the groups. I made my grouped rows with the drawCallback method from Datatables in order to not rely on plugins since I am performing calculations on the grouped row. It seems like when we make grouped rows with drawCallback to avoid depending on a plugin, we have the choice to either do orderFixed to keep the groups, or not use it and break up the groups based off all the examples I see. If I put the order and the orderFixed options on there, the orderFixed one of course is the dom's priority. It seems that we will have to not allow user sorting in order to keep the groups.

kthorngren

Colin's example is using the Datatables RowGroup extension. Its part of the Datatables development. If you don't want to use it maybe you can post a link to your page or a test case so we can see what you are doing and if we can help.

https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

fox1

Thanks, I have made a pen so you can see how this is working :https://codepen.io/csmithersslc/details/gjqaNR

As you can see here, the grouping is up

kthorngren

Interesting, thanks for the test case. If I remove orderFixed then sorting, on the surface, seems to work for some columns like Campaign but not others like Cost. Here is the version without orderFixed:
https://codepen.io/anon/pen/xJMVxr

This might be one for @allan or @colin to look at. Probably beyond my JS skills and internal Datatables knowledge

Kevin

fox1

Thanks @kthorngren , that's definitely something I noticed as well. And the more entries I have, the more the grouping is broken as you can see since it then shows the grouping done four times instead of the two.

allan

Personally I'd suggest that you use RowGroup (you are already using DataTables) since it is tried and tested for grouping. Then then I would say that - I wrote it .

There is a lot of code in your pen so I've not managed to assimilate all of it, but this:

                var rows = api.rows({
                    page: 'all'
                }).nodes();

And again letter on, is suspect to me. You would want to use order: 'current' since the default is to return the rows in index order which is unlikely to be the order you have applied (its possible, but unlikely!).

This example shows grouping being done - it uses page: 'current' but note that using that will also set order: 'current', while all will not.

That's also how RowGroup does it.

Allan

fox1

Thanks @allan. I've tried the suggestion on the current page and haven't noticed a difference on it sorting any differently. RowGroup looks promising, however it won't allow the rows to get toggled the way it currently is from what I've tried to show/hide the details. Otherwise, that looks nearly perfect!

colin

Hi @fox1 ,

Take a look at this thread, this example came from there and it does support expand/collapse with some tinkering.

Cheers,

Colin

fox1

Ah, that's just what I am looking for. Thanks for sharing.

fox1

Thanks everyone, I apologize again to bug you all again. This definitely is helping make this look far closer to how it was in mind.

fox1

The posting seems to be jumbling, so if this appears as another post instead of an edit, my bad. So close. It appears that the example sorts on the details in the group. What was in mind was to actually sort by values in the group, hence all the work in the rowCallback function before. I like the way this is being done in the examples, so the trick here is making the grouping like I have in the test case I made work but this time in RowGroup.

I'm not the most advanced JS person, so bear with me. If sorting happens by details, the groups don't get preserved for me since it reads those values which makes sense for these example though I'd need to do something different. In the example, Edinburgh is a group of nine in the first drawing, then it breaks into fewer entries if the user chooses something else to sort. In my version, it would have something like the totalled salaries for the office in the group and make that sortable. I see the dataSrc in the rowGroup seems to be where the data gets grouped on, right? I am trying to envision how this would work without the hard-coded data. I have my data source as the object working great for the details and then need to perform all the calculations for the summed data.

I tried to implement this but am not seeing how to make it work like I had in the drawCallback. I keep showing undefined as the value where group is in the part where we append. Guessing this is because I need to point to the data source then that's correct. Is this right? Not quite seeing how I can then make my new summary object work well with this because you have to declare what the data source is and I would need to perform the reductions and such before this which doesn't seem to work if I need to perform the calculations in the groups then sort

fox1

There's an example here: https://datatables.net/extensions/rowgroup/examples/initialisation/customRow.html that's similar to what I am hoping to do in the row for the calculations. Someone ran into this too and did something like I did with the drawCallback in the end since they needed multiple columns totalled. It still seems that you can't sort by the group values though and the grouping breaks when the user sorts.

colin

Hi @fox1 ,

You are right, you can't order by the group values, but you can keep the group by using orderFixed (see my examples above). The rowGroup.dataSrc is the group field, so yes, that does need to be declared.

Hope that helps,

Cheers,

Colin

fox1

Okay. Well, it's nice to have the user sort at the very least. I'll have to poke around and see if I can figure out a workaround to sort the groups, but this looks great. Thank you again for your patience and help, everyone.