Show entries based on Groups not Rows

marshall_99

Hello, I have a question... I have row grouped datatable (even with collapsible function). On first page is 20 entries and 3 groups and on second page is 10 entires and 8 groups... I have this issue, that I want to show for example 2 groups on one page (and it wouldnt matter how many entries are there). Because it looks weird if I have only 3 closed groups on one page and 8 closed groups on second page.
I hope you understand.
Thank you call

kthorngren

You can use the rowGroup().dataSrc() API to change the data source, ie, change the number of groups shown. You can use it in the draw event so that each table draw you check to see how many groups you want and use -api rowGroup().dataSrc()to apply the grouping. You may also need to use-api order()` to make sure the table is properly ordered.

Not sure how you want to determine the grouping but you could use rows().every() with a selector-modifier of {page: 'current'} to iterate the rows on the page.

If you want help with this then start by building a running example and specify how to determine the number of groups to show.

Kevin

CucumberOwl

I am trying to show entries based on Groups using the RowGroup extension for my groups. This is the only post I came across that was similar to what I'm looking for and there doesn't seem to be a clear conclusion on how to do this. Seeing an example would be very helpful. This is also an older post so I'm wondering if anything has changed. Let me know If I should make a new post for this.

Below is the current table I have. It has nested groupings with a total, and count aggregate. Since I am setting up the grouping using rowGroup extension, I'm not sure how I would use the rowGroup().datasrc() in the draw api and then determine grouping by rows.every(). Any help would be appreciated. I'm still somewhat new to using datatables.

pastebin https://pastebin.com/RCfgAnbV

or code below:

$('#mytable').DataTable({
    order: [[0, 'desc']],                            
    rowGroup: {
        startRender: function (rows, group, level) {
            if (level == 0) {
                //need to filter on group because if you do not it will only total what is display on the page
                //if group is spilt between a page it will show wrong total for the group.
                var total = $('#mytable').DataTable()
                    .rows()
                    .data()
                    .filter(function (data, index) {                                                
                        return data[0].display == group ? true : false;
                    })
                    .pluck(3)
                    .reduce(function (a, b) {
                        return a + Number(b);
                    }, 0);
                //rows.count() does not total count in group, only with what is shown on page
                //so use count of group by filtering.
                var count = $('#mytable').DataTable()
                    .rows()
                    .data()
                    .filter(function (data, index) {                                                
                        return data[0].display == group ? true : false;
                    }).count();

                return $('<tr/>')
                    .append('<td>' + group + '</td>')
                    .append('<td class="text-right">' + total.toFixed(2) + '</td>')
                    .append('<td/>')
                    
                    .append('<td>' + count  + '</td>')
                    .append('<td colspan="6"></td>');
            }
            return $('<tr/>')
                .append('<td colspan="12">' + group + '</td>');
            
        },
        dataSrc: ['0.display', 1, 2]                                
    },
    columnDefs: [{
        targets: [1,2],
        visible: false
    }]
});

kthorngren

@CucumberOwl Your rowGroup.dataSrc is ['0.display', 1, 2]. I'm not sure what 0.display is. Probably should just be [0, 1, 2]. The docs state this about the usage:

If your table is using array based data, this should be the column index that contains the data you want to use to group the table. For object-based data sources, it should be the property name in the data source object.

The order option will need to set the order of the groups your are using. Something like this: order: [[0, 'desc'], [1, 'desc'], [2, 'desc']],. See this example of multilevel grouping.

I think the OP was looking for a way to control how many groups are displayed per page. Are you asking for the same?

If you still need help please provide a link to your page or a test case replicating the issue so we can see what you have to offer suggestions.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

CucumberOwl

Yes, I am looking for a way to show 10 groups (first group level) per page instead of 10 entries. I need to keep the groups together on one page. It is confusing if a group gets split between pages.

Thanks for the link to the test case. See my test case here: live.datatables.net/wimumubu/1/edit

To answer other questions:
I am using ['0.display', 1, 2] as the grouping specifically '0.display' because I am using the data-sort option on the first group for the Invoice period. I need to display the date in a certain format but need to be able to order by the actual date. using data-sort makes that field an object, so I need to specify grouping by the display of the date or it does not group properly.

I'm not sure about the sorting. By default, I care about the sort by date. I suppose I could add order to the other groups.

Thanks in advance.

kthorngren

I'm not sure about the sorting. By default, I care about the sort by date. I suppose I could add order to the other groups.

If you don't sort the columns you are grouping then the groups could be split. See this example where I changed one Type 1 to Type 2.
http://live.datatables.net/wimumubu/3/edit

Your data is in the proper order to work without sorting. RowGroup relies on the ordering to be correct to properly display the groups.

I am using ['0.display', 1, 2] as the grouping specifically '0.display'

Thats neat, didn't know that would work.

I am looking for a way to show 10 groups (first group level) per page instead of 10 entries. I need to keep the groups together on one page

I think this would require some creative coding Per your example each group may have different number of rows. The page.len() will need to be set to display 10 groups with all the rows. Any search, sort or paging could change this so it would need checked for each draw except the draw() executed with page.len(). Probably possible to do but seems pretty complicated.

Kevin