Is there some way to do column grouping/averaging?

tedstrauss

I'm wondering if any of the existing datatables plug-ins or techniques can help
to provide a grouping function, which can be activated on any column of the table,
for exapmle with a grouping button that appears where col sorting arrows are.
For example, if the starting table looks like this:
[code]
Name -A-- -B-- -C-- -Size-
===================
MILK 2.00 3.00 1.00 S
JUIC 7.00 4.00 6.00 M
MILK 2.00 3.00 9.00 L
JUIC 5.00 3.00 2.00 M
===================
[/code]
And then the user grouped by column 'Name', the outcome would look like this:
[code]
Name -A-- -B-- -C-- -Size-
===================
MILK 2.00 3.00 5.00 S,L
JUIC 6.00 3.50 4.00 M,M
===================
[/code]
Where numeric fields are averaged, and text fields are grouped.
That's analogous to an MySQL statement like
> select name,avg(a),avg(b),avg(c),group_concat(size) from table group by name;
But I'm looking to perform the averaging/grouping on the client side only.

Question: Do columns need to be tagged with their data type that decides the type of
grouping operation to be performed on the values?
Answer: In my use case, I can easily pass the data type to the table, and no type detection
code is required.

Any ideas and suggestions towards implementing something like this would greatly appreciated.

If you're able to post a working example on JSFiddle, I would be willing to pay you for that.
Feel free to post your contact info if you'd like to talk about that.

Thanks

timtucker

Here's one approach that pops into my head:

- Start with a table that gets populated by aaData (JavaScript array of objects)

- Add an extra column to the underlying html for your table

Using a somewhat simplified version of your case you would start with something like:
[code]
[
{"name": "MILK", "a": 2, "size": "S"},
{"name": "JUIC", "a": 7, "size" : "M"},
{"name": "MILK", "a": 2, "size" : "L"},
{"name": "JUIC", "a": 5, "size" : "M"}
]
[/code]

- Before passing your data to the table, iterate over once for each column to produce a second array for each combined column:
(Any calculations you'd want to perform could be included here)
[code]
[
{ "groupBy": "name", "name": "MILK", "a": 2, "size": "S,L"},
{ "groupBy": "name", "name": "JUIC", "a": 6, "size": "M,M"},

{ "groupBy": "size", "name": "MILK", "a": 2, "size": "S"},
{ "groupBy": "size", "name": "JUIC", "a": 6, "size": "M"},
{ "groupBy": "size", "name": "MILK", "a": 2, "size": "L"}
]
[/code]

Combine the two arrays together and use that result as what you add to the table, i.e.:
[code]
[
{"name": "MILK", "a": 2, "size": "S"},
{"name": "JUIC", "a": 7, "size" : "M"},
{"name": "MILK", "a": 2, "size" : "L"},
{"name": "JUIC", "a": 5, "size" : "M"},

{ "groupBy": "name", "name": "MILK", "a": 2, "size": "S,L"},
{ "groupBy": "name", "name": "JUIC", "a": 2, "size": "M,M"},
{ "groupBy": "size", "name": "MILK", "a": 2, "size": "S"},
{ "groupBy": "size", "name": "JUIC", "a": 6, "size": "M"},
{ "groupBy": "size", "name": "MILK", "a": 2, "size": "L"}
]
[/code]

In your aoColumnDefs, set the visibility of the groupBy column to empty.

You may need to set sDefaultContent to an empty string or something similar (or add an empty groupBy value to each row in the original array as well).

Using the built-in filtering (or existing plugins), when you want to show only the data grouped by name, set a filter on the groupBy column to only show rows where the groupBy column is equal to the filter (i.e.: groupBy = "size")

Obviously it'd take a little more to implement, but it should work as a general approach.

tedstrauss

Thanks for this thorough response.
I'll consider this proposal carefully.
I guess one question is what the overhead will be as the number of columns goes up, and it gets to be a pretty big array.

avioli

Well, if the array is big - just do it on the server and fetch the results, simple as that. What Tim suggested would be fine for any table with less than 1000 rows on an average machine/browser.

timtucker

Adding a little more, it comes down to where you want the overhead:
1. On the server, before you send the data
2. On the client, when you set things up for the table
3. On the client, on every draw

A compromise between 2 and 3 (a variation method I mention above) would be to only calculate the grouping rows for a column and add them to the table the first time the user clicks on whatever UI element you have created to show groupings for that column.

At the cost of a bit more code complexity, that would save you the overhead of generating the rows in cases where no one requests them and would let you do your processing one column at a time.

tedstrauss

@avioli, the initial reason for this post is that doing the calculations on the server is not working because of an esoteric limitation of Apache Solr, that relates to the web app i'm building. But clearly this kind of function should be processed server side.
@timtucker, thanks for elaborating on the performance options. I guess performance would come down to how many columns, and how many rows. So testing will be key.