Select max of a group

Cooler123

I have a datatable where i have users and their donations.
I want to group by users and then for every user show only the row where his donation is highest.

In sql terms i want to group by user and select max of donation

But how do i accomplish this in datatable. I know about rowgroup but it shows all results

kthorngren

Is this a dynamic option the user can select?

RowGroups might work for this. The last example in this thread will show how to default with all the RowGroups collapsed. In startRender you can display the User and the max donation and whatever else is pertinent.

Kevin

Cooler123

how do i get maximum of each group. Also once i know which row has maximum for a group, i want to fill the other cells of that group row with max row values. But how do i get to know which row had maximum value?
Finally about sorting, i want sorting to happen on the basis of values on rowgroup. will that be possible?

kthorngren

how do i get maximum of each group. Also once i know which row has maximum for a group, i want to fill the other cells of that group row with max row values. But how do i get to know which row had maximum value?

The rowGroup.startRender or ``-option rowGroup.endRenderwould be the place to find iterate the rows for each group. It has arows` parameter that contains the rows within the group. See this example that calculates average. You would need to build some JS code to find the row with the maximum donation value. I don't have anything off the top of my head for this.

Finally about sorting, i want sorting to happen on the basis of values on rowgroup. will that be possible?

The RowGroup rows are not part of the Datatables data cache so they aren't sortable. To group the rows properly you will need to make sure to always sort by the dataSrc column (the user in your case). One option is to use orderFixed. Then you can sot within each group by the donation value. It would be difficult to get the groups to be sorted by both the rowGroup.dataSrc and the donation max value.

Take a look at this example:
http://live.datatables.net/hopomube/1/edit

It uses RowGroups when displaying all the rows and turns it off when displaying only the max donation. It also changes the sorting appropriately. The key for this example is to find a way to determine the max donation for each person before the data is applied to the table. This can be done at the server or in Javascrip. I didn't write code to do this but mocked it up with the max property. Setting max to 1 indicates the max for that person. It uses a simple Search Plugin to filter the non-max rows.

I can explain in more detail if this approach interests you.

Kevin

Cooler123

Your solution is interesting and i feel i dont even need the rowgroup. I could set max as 1 and then simply filter only those rows where max =1.
This approach is really neat and somewhat comes close to what i need and yes i would like to get more details as this can accomplish what i need as below.

I have a table whose every column has either dropdown filter or text filter
User-- Donation -- type(dropdown filter) --- institute (text filter)

Type declares the type of donation (health, food, shelter, blood)
Lets say my original dataset is x
lets say i have applied the dropdown filter on column 'type' and selected 'health'. Lets say the resultant dataset is y where only those rows are shown where type=health.
Now i want that we get maximum rows after all filters have been applied i.e. on dataset y.
I think we will have to first reset max everytime we change a filter

kthorngren

i feel i dont even need the rowgroup. I could set max as 1 and then simply filter only those rows where max =1.

Thats true. One thing I should have addd to the example is to hide the Name column when the data is grouped. Doing this saves seeing repeated names, for example:
http://live.datatables.net/hopomube/3/edit

I think we will have to first reset max everytime we change a filter

You can use drawCallback for this and iterate all the rows using rows().every().

The difficult part of this solution is to create the Javascript code to find the max for each person. You can use my example and update it with more columns, the select filter and workout your JS code. This way if you have questions we have a running example to look at.

Kevin

Cooler123

can you guide me a little. This is how i am thinking of implementing the max(donation) group by kind of search.

In Html i have full table with all results. I have header filters. whenever a header filter is changed then first i reset the max field. then i order the data according to userid. then i apply the header filter on the data and on the resultant data i try to find max donation for each user and set it to 1. then i set the filter max=1. and order the table according to default order.
I dont want entire code but i am new to datatable, so can you tell me which datatable functions will be used to acheieve this

Cooler123

Ok i was able to set max=1 for each person. Can you tell me how can i programmatically work with a subset of data. As in, the datatable has the full data. When i apply header filter a subset of that data is shown. Now i want to calculate max in that subset, but it always calculates it on full table. Any workaround?

another problem is that to make sure the rows.every loops in a sorted data by user, i have to draw the table.
First i tried this
table.order([1, 'desc']) where index 1 has user id
and if after this i use rows.every then the loop doesnt goes through a sorted data

but if i try
table.order([1, 'desc'])
table.draw
and then use rows.every then it works. i dont want to draw table multiple times. any workaround?

kthorngren

Good job in getting close to a solution!

Using table.order([1, 'desc']) doesn't reorder the data until you perform the draw(). I updated my example to show one approach for this without the need to order the table in a certain way.
http://live.datatables.net/qeqefale/1/edit

To illustrate I set all the initial max values to 0. First check the Show Max checkbox and you will see no rows. Click the Find Max button and you will see the 3 Max rows.

I know you weren't asking for code to be written but I had to write it to make sure I knew all the steps to make it work

Kevin

Cooler123

well yes i got it working fully. Just one question is there a way to get datatable instance that is currently on screen. What i mean is that lets say i have applied a header filter and the data shown is now filtered. I want to now work on this filtered data.
One solution is to get the value of each applied filter in header and use datatable.coloumn.search in the code
but is there any other way as i have many columns

kthorngren

You can use the selector-modifier to filter the rows, columns, or cells returned based on filter, ordering, selected, etc.

Kevin

Cooler123

I am using it like this:
table2.order([1, 'desc']).draw();
var table = table2.rows({search:'applied'});

after this i am only searching 'table' for setting max. But apparently its using the whole data instead of subset (generated by search : applied)

and finally i am using table.draw

kthorngren

after this i am only searching 'table' for setting max. But apparently its using the whole data instead of subset (generated by search : applied)

Please provide a link to your page or a test case showing this issue. You can update mine.

Kevin

Cooler123

http://live.datatables.net/savicomu/1/edit

In the above code, i create another column by the name of type. In the search bar I type 'b', then i click show max.
In the code i have used another table variable t2 which gets the rows of the applied search.
Therefore, max should be found for the rows that are in the result set(on display) when we type "b" in search.
But that is not the case

colin

I'm not clear what you're trying to do. The test case had several errors, fixed here: http://live.datatables.net/savicomu/2/edit

But when you search for 'b', nothing is being matched. Can you give steps on how to reproduce, and say what you would expect to happen instead.

Colin

kthorngren

That link is giving this error:

DataTables warning: table id=example - Requested unknown parameter 'type ' for row 0, column 1. For more information about this error, please see http://datatables.net/tn/4

Looks like you have a space in data: 'type ',.

table variable t2

I don't see this anywhere.

I updated your test case a bit here:
http://live.datatables.net/zoluguma/1/edit

I left the highest max value with the type of a and set the others to b. Run the test case then click the Find Max button followed by Show Max. Same result as my first example.

I added {search: 'applied'} to this statement:
table.rows({search: 'applied'}).every( function ( rowIdx, tableLoop, rowLoop ) {

Uncheck Show Max then find b. Click Find Max followed by Show Max. Now you will see the max value of the filtered rows.

If you still have issues please provide a test case showing the issue. You may need to watch your live.datatables.net URLs as they may change or you may need to Clone to save a fresh copy.

Kevin