Sorting RowGroup on group values

halioris

I am fairly new to datatables but have found it to be awesome. I stumbled across the RowGroup functionality and it is great. I ultimately want to sort by the number of elements in each group but was playing around with different functionality and unable to get it to do what I want and looking for a little help. I tried to follow the example that was posted here but could use a little help. First of all I tried to strip it down to the basics so I got rid of collapsing stuff and a little more. My table is a list of pro tennis players that played in college. I have a regular table that just lists them in order (found on my website here) and I am creating another version grouped by college so you can see how many from each college are in the Top 1000 pros. The live test case for my attempt that is failing to group by college with sorting is located here (I sure hope that link persists as this is my first attempt using live.datatables.net). I would like to do 2 things:

1. Be able to sort the table by number of players per college. I'd honestly be ok if the user could sort nothing after that and maybe just add collapsing to it.
2. Just because I saw the example of sorting on grouped totals I wanted to learn and get it to work but it is not working. You will notice that I attempted to add a total Points per college but it is only showing the first row, not the total for the group. I'd like the group row to show the total for the group and then when you click on the Points header have it sort on Group Points not the individual rows.

There are a few things I don't understand that would be great if someone could explain as well.

1. What does the statement pointTotal = $.fn.dataTable.render.number( ',', '.', 0).display( pointTotal ); do? I feel like it must be saying that you take pointTotal and make it an object whose value is still the number represented by pointTotal but whose display characteristic is the format as indicated. Is that correct? I saw the docs for render.number but not for .display so not really sure what that does. I'd love to hear an explanation of that whole statement broken down to get a clear understanding.
2. As long as I have orderFixed in my options I don't think I can sort like I want on points, but when I take it out things go haywire and I have no idea why. Keep clicking Points and it does all kinds of weird stuff.

Any help greatly appreciated.

halioris

OK, so I don't know if this is the best way to handle it, but when trying to sort my RowGroup by the number of rows in each group I decided to add a calculated column to each row that held the number of records in the group that row was in and then order by the number, group. This now works as I was hoping but I don't really like the way I had to access the original data array. The code for the working table is below.

var playerData = [[18,"John Isner",1940,"-","-","Georgia"],[53,"Tennys Sandgren",923,"-","-","Tennessee"],[59,"Cameron Norrie",885,"-","-","TCU"],[302,"John Patrick Smith",130,"-","-","Tennessee"]]
$(document).ready(function() {
  var points = {};
  var groupColumn = 5;
  var playersTable =
  $('#playersTable').DataTable({
    paging: false,
    data: playerData,
    orderFixed: {
      pre: [[6, 'desc'], [5, 'asc']]
    },
    rowGroup: {
      dataSrc: groupColumn,
      endRender: null,
      startRender: function(rows,group) {
        if (points[group] === undefined) {
          var pointTotal = rows
            .data()
            .pluck(2)
            .reduce(function(a,b) { return a + b; }, 0);
            //pointTotal = $.fn.dataTable.render.number( ',', '.', 0).display( pointTotal );
            points[group] = pointTotal;
        }
        return $('<tr/>')
          .append('<td colspan="2">' + group + ' (' + rows.count() + ')</td>')
          .append('<td>' + points[group] + '</td>')
          .append('<td colspan="3"/>');
      }
    },
    columns: [
      { title: "Rank" },
      { title: "Player" },
      { title: "Points",
        //render: function(data, type, row, meta) {
        //  return (type !== 'sort' || points[row[groupColumn]] === undefined)? data : points[row[groupColumn]].split(' ')[0];
        //}
      },
      { title: "Current Tourney" },
      { title: "Next Tourney" },
      { title: "College(s)", visible: false },
      { title: "Count",
       visible: false,
        render: 
          function(data,type,row) {
            return playerData.reduce(function(total,val) {if (val[5] === row[5]) total++; return total;}, 0);
          }
      }
    ]
  });
});

and the link for the livetables test case that goes with it is here.
Please note that in the example code above I have greatly shortened the number of elements in the playerData array for the sake of brevity. The full array is in the livetables test case. The part I don't like is on line 45 where I refer to the original javascript data array playerData. I would like to refer to the data through a datatable API because if I replace the data source with an ajax source instead of a static javascript array then this will no longer work. When I tried to do this with a reduce on column().data() I did not get the desired result, I got a running total as if the expression was being evaluated as each row was populated so after the first row for a group was put in the table it was 1, the second one was 2, etc. The column render I used to attempt to do that was:

render: 
  function(data,type,row) {
    return $('#playersTable').DataTable().column(5).data()
      .reduce(function(total,val) {if (val === row[5]) total++; return total;}, 0);
} 

So my question is how can I refer to the entire set of data in the source through a datatables API in a column renderer?

Also, if the way I went about doing this was not correct I'm more than open to a better way of doing it was well, but thought this was a pretty ingenious way of sorting on the number of elements in each group.

Thanks in advance,
Chris

colin

I did not get the desired result, I got a running total as if the expression was being evaluated

Yep, that's because columns.render is called as each row is drawn for the first time. It would be best if you move the logic into initComplete as all rows would have been added at that point. You would get the column data with column().data() still, but then update the count column with cell().data().

Colin

halioris

Thanks for the tip Colin. I had read many posts about doing things in initComplete which is after the data is loaded. I had not tried since I have not used it before and have a couple questions:
1. Will I still need a data: null on the column to define something there? Assuming I will since that column is not in the source javascript array.
2. How can I refer to the table itself in the initComplete function? Will this refer to the table?
3. You gave me hints on the api's to call but need a tad bit more guidance, hopefully a one time lesson here will prove valuable. Do I have to somehow loop through every row in the table in the initComplete and then for each row in the loop use column().data() with a reduce to get the count of records matching that row, and then somehow set the value with cell().data()? As I sat down and tried to do this I had no idea how to refer to the cell I want to set.

I'm thinking maybe something like this if you could help fill in the details/correct me:

initComplete: function(settings,json) {
  ???.rows().every( function(rowIdx, tableLoop, rowLoop) {
    x = ???.column(5).data().reduce(function(total,val) {
      //check if value matches if so increment count
  });
}

I could really use some help with the above. Can I use this for the first ????
Should I use the rows().every()?
How do I use cell().data() where I have x?
How do I refer to the table where the second ??? is?

I feel like I've way over complicated this but once I get a good example I think I can be rolling. Thanks,
Chris

kthorngren

First great work in trying to build a solution for this. It seems like the approach of reduce to iterate the array for each row is inefficient whether used in columns.render or initComplete. When faced with a problem like this I build a global object variable that is keyed from the data point I'm interested in. This way the loop is executed once. I will use columns.render to access the global object. Here is my example from your above code:
http://live.datatables.net/povunusa/1/edit

To answer your initComplete questions:

1. Will I still need a data: null on the column to define something there? Assuming I will since that column is not in the source javascript array.

I think you will need columns.defaultContent instead.

1. How can I refer to the table itself in the initComplete function? Will this refer to the table?

Not sure this is documented but you can get the API using this.api(). You can see this in this example.

Do I have to somehow loop through every row in the table in the initComplete and then for each row in the loop use column().data() with a reduce to get the count of records matching that row, and then somehow set the value with cell().data()?

You can use row().data() or cell().data(). If you opt for the initComplete solution see this example:
http://live.datatables.net/fimokulu/1/edit

Kevin

halioris

Thanks for the quick response and awesome feedback Kevin (and Colin). You know I hadn't really thought about the fact that executing the reduce on every row was literally iterating the entire table for every row (x^2). Pretty dumb on my part, so thanks for pointing that out (and I thought I was being the cool kid trying to use map, reduce, filter, etc. haha). The initComplete example was just what I needed to see based on my questions but not exactly the best approach. I think you've actually led me to a hybrid approach. I'm not super fond of the global array approach (outside of the table) because I'm not sure how I would do that if my data source was ajax. In that case I think I need to use initComplete so I think I should go that route but then fill in the global array in the initComplete and use it to set each row. I'll have to iterate the data source twice but that's way better then x^2. At least that's what I'm thinking before going to implement it which I will try later tonight. If it does indeed work out that way I'll post up the final version in case anyone else wants to see as this seems like a pretty common use case. Crediting both of you with giving me answers. Thanks again.

kthorngren

I think I should go that route but then fill in the global array

You could do that using the json parameter.

You can do the same with the ajax data source. If using ajax you can use ajax.dataSrc as a function and build the object there. The same can be done in the success function of the jQuery Ajax method. You won't need to worry about the async nature of Ajax since you will be building the object before the data is applied to Datatables.

Kevin