DataTables and colspan

sihing_steve

I've been banging my head on the keyboard for this one.

My table has two distinct areas that have different testing values for a particular device, and I would like to indicate that as such. (I.e. Encapsulated testing values vs. Un-Encapsulated testing values)

However, when I attempt to place a row in my DataTable that spans more than one column the table will not paginate.

Instead, I receive the error: (Under FF Firebug)

sData is undefined
var Char;for(i=0;i

allan

Hi sihing_steve,

I'm sorry to say that DataTables does not support colspan at the moment. The reason for this is that it is very much a non-trivial problem in how this would interact with filtering and sorting (for example how would you sort a column which has elements that span multiple columns - which column would that data belong to? The first one, or all of them? - The same question hangs over filtering).

I do hope to support colspan attributes at some point when I have an answer to these questions and what impact it will have on the implementation (your input is most welcome!), but for the moment colspan is unsupported.

Regards,
Allan

iks

Hi. Sorry for my bad english. I try to answer a question, how would you sort a column which has elements that span multiple columns. Maybe there is no sorting or filtering on cell with colspan, but possible to sort or filter cells, under this one?

allan

Hi iks,

No, I'm sorry to say that colspan is not supported at all by datatables. As noted in my comment above, it is not an easy task to support colspan when sorting columns, although your idea of simply skipping them is certainly one option. This is something I might look at for future releases.

Allan

jdyndale

A suggestion for how to handle colspan:

This goes for using a colspan to span all columns, useful for sub-headings or otherwise grouping data. I agree supporting spanning of [x] of [total] columns is definitely a challenge.

A row with colspan shouldn't affect filtering other than as follows: A new table is rendered with the new limited data set based on the filter - and if one or more rows that were under the same sub-header row (the one with the colspan) satisfy the filter, they should appear beneath that sub-header row in the new table. Basically the filtered results are grouped by their corresponding sub-headers.

As for sorting, well, the sub-header rows would serve as groups - and as such, there would need to be a loop that sorted the rows in each group separately, and then, if desirable (an option maybe?), the groups themselves could be sorted (based on their string value probably, which could be their text contents (i.e. the group name)).

I hope that was clear enough to understand. I don't think implementing something like this should be too difficult, and it would be REALLY useful :O)

Maybe take a look at how this is implemented in the AdvancedDataGrid control in Flex? I'll probably have to implemenet some kind of hack for this functionality in the datatables plugin for my project, because I'm trying to get the same functionality I have in a Flex project using the AdvancedDataGrid with javascript. Your DataTables plugin was the best replacement I found, though it lacked this ability to have colspans.

Tom

Usually, if I am using colspan it is for a "child" row (often with an embedded table of its own) that usually displays data related to the record in the parent row.

I think support for that would be helpful for many situations. When you're declaring your dataset, set a subarray containing the child data set, and for each of the parent rows with a child array, create a row beneath it with an embedded table to present that data.

Only sort/filter the table by parent peer rows, ignore the child rows (the embedded tables could even have their own independent searching and sorting, though that would probably be overkill for most cases).

Adam Nelson

Hmm, this is actually a big problem. Here's my suggestion:

Ignore all rows with colspan.

That should be easy to do for now. At the least can you put it in big letters that colspan isn't supported (and maybe in the license area of the js file). I also banged my head against this for over an hour before realizing.

In the future, there are a thousand ways to handle it more gracefully, but for now, just ignoring colspan rows would be a huge help. It may even be easier to send all the colspan rows to the bottom or the top - that would still be preferable.

Thanks for a great product though.

allan

@jdyndale: Your idea sounds good, assuming that you want to use colspans for grouping data, which isn't always necessarily the case (which is part of the reason why this is difficult to do). However, applying a filter to column headings retrospectively after filtering and sorting is actually quite hard to do (and will take up quite a bit of processing). There is also the question of should these 'heading' columns be counted as part of the display or not (i.e 1 to 10 rows, including (or excluding) headers). I think there are quite a few unresolved questions here in order to make this generic enough to include in the main distribution.

@Tom: If you want to use a child row which spans the whole table, then you can use fnOpen() and fnClose(). This allows for the display of detailed information about a particular row. This is build into DataTables (since 1.0 in fact) and does what you are looking for I think.

@Adam: Ignoring rows with colspan doesn't seem that desirable to me. If rows where a cell had a colspan tag simply disappeared, then I would consider that a major problem. Simply moving them around isn't really an option either - since it's the data processing which doesn't know how to deal with them.

My own preference for dealing with a colspan is to have the data from that row put into the first column's data. The columns after that would then have blank data put into them. This means that sorting would not work on those columns though and those rows would end up being either grouped at the top or the bottom of the table (since they have empty data). An alternative (but similar idea) is to write the same column data into all of the data elements that are covered by a colspan. Sorting and filtering would then work. Does that sound like a reasonable idea? I'm not quite sure about the implementation yet... ;-)

Allan

allan

Here is a screenshot from a demo I've just prototyped which follows my suggestion (same data for each column over which the cell spans): http://www.datatables.net/development/colspan.png . This works reasonably well, with two issues:

1. The completely destroys the class for the sorting column
2. It can be used for 'grouping' information - if you wanted to do this, a hidden column would need to contain all of the information for that group in the 'header' cell. However, this is that only way that this would work without doing some very major work on the filtering algorithm.

What do you all think?

Allan

mimic

I have one idea. This could be done with a special type of a column. So one column can be specified as "grouping" column and then all rows which would have the same value in that column with be grouped together, that column would be hidden and at the top there would be this colspaned row with content of that column. Filtering and sorting would still be done normally on that original column (in this way you could define the order of those groups). So it is only the question of displaying this data. It would be still be stored as a vertical column.

This technique would then be possible to (mis)use also for other cases where there is a need for colspaned rows (you could for example disable sorting and filtering on that column and then you would be able to easily define whatever data you would want there).

This would be then well-defined and internally same as it is currently. Only display would change.

(In theory it would be even possible to define cascade of such groups.)

So for example, original data could be:

--------------------------------
| TypeCol | ColA | ColB | ColC |
--------------------------------
| First | Data | Data | Data |
--------------------------------
| First | Data | Data | Data |
--------------------------------
| First | Data | Data | Data |
--------------------------------
| Second | Data | Data | Data |
--------------------------------
| Second | Data | Data | Data |
--------------------------------

This would be then displayed as:

----------------------
| ColA | ColB | ColC |
----------------------
| First |
----------------------
| Data | Data | Data |
----------- ----------
| Data | Data | Data |
----------------------
| Data | Data | Data |
----------------------
| Second |
----------------------
| Data | Data | Data |
----------------------
| Data | Data | Data |
----------------------

Using fnRender callback (or maybe fnGroupRowRender) those rows could also be transformed afterwards. Sorting would be defined as first always sort by TypeCol and then others (but could programmatically be changed, for example reverse order). Filtering could be also possible (even by groups, so an user could define a combobox for example and filter by TypeCol to display only one group).

As those rows are used only for display they would not be counted towards entry count. They would be displayed immediately at top of a group if there is at least one entry from that group displayed.

Probably the easiest way would be even not to define automatic hiding of that grouping column. So if an use would set TypeCol column to "grouping" then it would get:

--------------------------------
| TypeCol | ColA | ColB | ColC |
--------------------------------
| First |
--------------------------------
| First | Data | Data | Data |
--------------------------------
| First | Data | Data | Data |
--------------------------------
| First | Data | Data | Data |
--------------------------------
| Second |
--------------------------------
| Second | Data | Data | Data |
--------------------------------
| Second | Data | Data | Data |
--------------------------------

So internally there would be no change. Only display would change - those rows would be added (and fnGroupRowRender would be called on them). And user will then hide that TypeCol and this would be it.

The only really tricky thing would be to allow for progressive enhancement. So that dataTables would reconstruct this internal representation if the initial table would be shown with this rows. Then it should automatically reconstruct this grouping column from them.

allan

I've I've prototyped an idea, based on the suggestions here (thanks for the detailed information mimic) and a few ideas of my own. What I've done is to use the first column as the grouping information, and have that set to hidden. Then the fnDrawCallback() is used to modify the DOM each time the table is drawn, inserting the grouping cells as needed. The only change that has been made to DataTables to support this is the addition of a "aaSortingFixed" parameter, which will force sorting to always occur on that column first (therefore keeping the grouped rows together). I'm quite happy with this change, as it's something I've been meaning to do for a while.

http://datatables.net/development/grouping/

What do you all think? It's suitably flexible for any application, and it does what is required. It's not ideal from a progressive enhancement point of view since it still won't read colspan data from the DOM, but the grouping information is actually there as a column (at least in the example case) so I don't see this as too much of a problem.

The one quirk with my demo is that the table bounces around a bit when you page. This is easily solved by putting a wrapper around the table with a height. I think at some point a few more demos with a capability of DataTables on display is required (with a more flashy look where suitable.. :-) ).

Regards,
Allan

Adam Nelson

The demo is awesome - and I really like the grouping concept.

My big concern is that I need to use this to spruce up crufty old tables (and by crufty, I mean bad - like mixed in with other tables for layouts - yuk). Anyway, it's a real world problem for me if this package is too smart. Maybe there should be two methods for handling colspan cells:

1. Smart grouping like you have.
2. Use the value of the colspan cell in the left-most column sorting and make it null for the other columns.

And some sort of variable to choose the behavior.

mimic

Great. It really looks great. I do not have any other comments than: progressive enhancement. It would be really great if there would be some switch which would enable reconstruction of this first column from table structured in predefined way (you define it in which way, but with those colspaned rows).

allan

Hi guys,

Thanks for the replies!

@Adam: DataTables is really designed for use on clean and well structured tables. If it works on other tables, then it's a bonus... I'm not to sure what you mean by your second point though. The left most column is being sorted upon in my example (I've just chosen to hide it).

@mimic: I'm afraid I'm not quite sure what you mean. Ho do you mean reconstruction of the first column? The first column is still there and available, I've just hidden it since it is effectively redundant data. To some extent this must actually be the true meaning of progressive enhancement, the data is there for low-impact (non-JS) browsers, while high-impact browsers get a better experience.

The one thing it doesn't do is allow grouping for the low-impact - is this what you are getting at? If so, then perhaps the best way of dealing with it is on a per case basis. You could have a table which has the grouping in the raw HTML, then before initialising DataTables, remove those rows. Then initialise DataTables and use a method something like this to do the grouping. How does that sound?

Regards,
Allan

mimic

Yes, I was thinking that it will probably be common that you will want to do grouping also for low-impact browsers. Only those will not have sorting, filtering ... So there should be some way to be able to read that table and reconstruct from there that first column (which otherwise would not be directly seen in a table for low-impact browsers).

I think that the easiest (and also most portable way) would be to make a callback function which would be called with a complete DOM structure when dataTables reads it from the page and will be allowed to transform this DOM structure. I think this is better than doing this manually before initializing dataTables because it can happen (on browsers somewhere in between in JS and jQuery support) that the code for table reconstruction works but later on it fails when initializing dataTables. So it is better if this can be done in one step.

allan

@mimic: I'm not sure I see the difference between having a callback function to re-order the DOM and just doing it before initialisation DataTables? An alternative is to have DataTables do this parsing, but I'm really not keep on this idea as it would introduce non-generic edge case code to DataTables. I think it's much cleaner to simply require the developer to parse the grouping information in as they wish. For example, they could assign a 'title' attribute with the group that they want to give each column (which is quite attractive) and inject a hidden column into the table based on that, equally manipulating the DOM from the static grouped version to have that information in a column would also be trivial.

I'll release the next beta of 1.5 shortly for everyone to 'play' with :-)

Allan

mimic

OK, I agree. It is probably really not necessary to complicate. :-)

sihing_steve

Wow, thanks guys! I just came back to this post and found the flurry of activity here.

Awesome stuff! Grabbing the new beta now.

MikeS

I'm getting a 404 error when I try to navigate to http://datatables.net/development/grouping/

Where can I see this example?

allan

Hi Mike,

Yup - it's got a new home now: http://datatables.net/1.5-beta/examples/advanced_init/row_grouping.html

:-)
Allan

MikeS

Thanks Allan.

Is there any way to make datatables behave such that when I click the "grouped" row, it would show the rows that belong to the group? i.e., the grouped rows would be initially hidden except for their group heading row, then when the latter is selected (clicked on), the grouped rows get displayed and the table expands vertically. This is kinda like the hidden row details example except instead of having a different table structure for the hidden content, it would be exactly the same columns as the table itself. Not sure if I've explained it well here, but something like:

|Year| Region | Sales
|2000| <- This would be the group header row
|2000| A | $123456
|2000| B | $456321 <-- These rows would be initially hidden until the header row is clicked.
|2000| C | $9034
|2004|
|2004| A | $123456
|2004| B | $456321
|2008|
|2008| A | $9034
|2008| B | $65034

allan

Probably the best way of doing this would be to use fnAddData() and fnDeleteRow(). Don't have the data in the table initially, and then when you click the header, add it to the table using fnAddData()... That would give the effect that you are looking for, while preserving pagination etc.

The other option (off the top of my head) is to use filtering on a hidden column. If a particular row is clicked on then you alter the filter such that it's child rows would show. A unique id for each row with a bit of column filtering would probably do for this method.

Hope that helps!
Allan