Blanks in the date column - causes string sort

taffyboiy

Hi,

We are displaying dates as dd Mon yyyy (eg 4 Jun 2011) - but are having problems getting DataTables (v 1.7.5) to use date sorting when some of the cells in the date column are blank. Our format parses as a date in our target browsers, so I would have expected DataTables to use the date sort functions, but it appears to still use string sorting (obviously this sorts dates alphabetically, which is not what we need!)

Any chance you could provide a bit more background on how DataTables tries to determine the data type to use for sorting, and what we can do to force it to use date sorting if any dates are found in amongst the empty cells.

Best wishes

Dave

ps. Almost goes without saying, thanks for a fantastic component! :)

allan

The date type detection is done by Date.parse() - so if the built in Javascript method can't pick up the data format, then it won't sort by date. There is a little bit of code in which allows empty string there, so this should work okay - which suggests that some data isn't being picked up as a date. You can force the sorting type by using sType for the column set to 'date' - or you could write a type detection plug-in which correctly picks up the date format.

Allan

taffyboiy

Hi Allan

I have created a simple test page which should demonstrate the behaviour I described above.

http://www.idsdevsites.co.uk/demo/test.html

As you can see from the first table - the date format used is parsed correctly by the browser, as it sorts on date, not string. The second table fails though, and only sorts on string order, and dates are not consecutive. If you check the source, the only difference is that there is a blank cell in the second column.

Obviously this behaviour may not be considered a bug for some other reasons, (although I think my end-users consider it a bug!) - so if there is no patch/workaround directly available I am quite happy to use a type detection plug-in, although I would need a bit of help with how to write it and get it on the type detection list. This is why I asked for a bit more info on the method DataTables uses for determining types, because I don't understand how DataTables resolves the situation where cells are not uniform in the format of their content. (Which is the case here)

Obviously if there's anything I am doing wrong with my production of sortable tables on this page, please let me know - I don't want to give you headaches when there's something I have done incorrectly, although I have tried to make the demo page as simple as possible!

Many thanks for any help you can provide.

Best wishes

Dave

taffyboiy

Forgot to add - much as I'd like to implement your other suggestion "You can force the sorting type by using sType for the column set to 'date'" - our tables are auto-generated (there's loads of them in our application!) - so we don't have the luxury of explicitly telling DataTables what to do with the columns. It's actually a fantastic selling-point that DataTables is clever enough to work it out, which is why I think it would be worth overcoming this little glitch! :-)

allan

Could you possibly try this with the 1.8 development software, which you can get from the 'nightly' on the downloads page ( http://datatables.net/download ) - I think that this is something I've addressed while doing the 1.8 work.

Allan

taffyboiy

Hi

Thanks for the response. I have downloaded the nightly buildand placed it in the test site, but as you'll see, nothing much has changed I'm afraid... :-(

Without picking through your code too much, I can see you've updated the section which used to read

n.aTypes=[function(g){if(g.length===0)return"numeric";

so that it uses typeof in order to try and ascertain a datatype (which seems more correct to me!) - but I just wonder whether, having encountered more than one datatype in a column, DataTables cannot decide which sort takes precedence and defaults to string sorts. This would be fine if all cells were filled in (it wouldn't be possible to sort numbers against dates in any meaningful way anyhow), but empty cells don't really have a datatype, so shouldn't be capable of returning anything which confuses the sorting engine.

Please let me know if there's anything I can do to assist with the testing etc!

Cheers

Dave

taffyboiy

Hi

I have downloaded the latest nightly build and placed it on my test page (above) - I'm afraid the dates are still not being sorted chronologically.

Please can you let me know if the stable 1.8 edition of DataTables will correct this issue.

Many thanks

Dave

allan

HI Dave,

Sorry for the delay in replying. With the help of your link I've identified the core issue. basically it appears that my type detection algorithm is falling over with your example. What is happening (at least in the 1.8 betas) is that the empty cell is being picked up as a 'numeric' type. All the other cells in the column are picked up as dates, and since DataTables can't sort both date and numeric together, then it sets it as a string.

So basically it is a precedence issue. Since the numeric option gets priority over the date, this happens. If date came first then it wouldn't. So there is one solution which will work immediately, and that is to set the sType for your column to be 'date'.

However, it would be nice if DataTables picked this up correctly when doing the automatic type detection... It might be a little tricky to alter the algorithm for this - I'll get back to you...

Regards,
Allan

taffyboiy

Hi Allan

Thanks for looking into this. As I said above, I can't set the type because the tables are auto generated, and there's lots of them!

However, I am wondering why blank cells should be picked up as 'numeric', as opposed to anything else - is there a reason for this? I have poked around the code enough to know that DataTables looks at the first cell, if it finds something, but that something that isn't a number or date it defaults to a string sort, if not, it looks in the next cell down, and that, having found a date or number, it looks through all the cells in the column to make sure there are no conflicting values.

As such I would have thought it would be possible for a blank cell to be picked up as 'no-data', (or equivalent) and for 'no-data' to be overruled as soon as a date or number was found. As such, the conflict that causes a string sort would only happen when there is actually a conflict. If, having examined all the cells in a column, the data type was still 'no-data' - that wouldn't matter, because it is meaningless to attempt to sort an entire column of blank cells.

I hope this line of reasoning helps. I doubt it would add too much to the size of the minified script.

I have seen the page which describes how to get DataTables to sort on other datatypes (eg IP addresses and such like) - are these datatypes equally affected? (ie they don't work if there's a blank in the column), if not, could you give me a quick primer in how to get my own datatype to the fron tof the list of 'checked for' datatypes in IE6? As I understand it, IE6 doesn't support unshift...

Many thanks for any help you can provide.

allan

Hi Dave,

> However, I am wondering why blank cells should be picked up as 'numeric', as opposed to anything else

Basically because the numeric type detection comes first. The way DataTables type detection works is to simply loop over the type detection functions and when the first one that finds the data as valid reports that, the loop breaks. As we see here, this isn't particularly adequate, particularly when empty data is found.

What I've done is made a change to this algorithm so it simply skips type detection when the cell data is empty. This requires a small change to the sorting in DataTables as well - but nothing too big (basically if the whole column is empty then the sort type might be undefined - which it never could be before). This has been committed and is available as the nightly on the downloads page - I'd be most interested to know how you get on. I've been testing with your demo page and it works well with that.

Ultimately this modified algorithm is still not perfect, when more complex cases are considered. Ideally what would happen would be that each type detection function should run on every cell, and a list of what is valid be kept. However, I think that this would introduce a significant performance hit on the table, since the type detection would be run so often. Therefore, I think that I'll stick with this updated method for now - sType can be used if it fails (btw - you can use aoColumnDefs and aTargets to attach sType to an arbitrary column based on the column class - but that shouldn't be needed now).

Regarding your question about unshift and IE6 - you could use splice() to create the same effect - but again with the nightly this shouldn't be needed now.

Regards,
Allan

taffyboiy

Many thanks for the update - I will try it out now.

I agree entirely with your worry that looking through every cell will create a performance hit on page load, but ignoring cells lower down the column is what the drivers for Excel used to do, and that caused it's own problems. Having said that of course, anyone who creates a table with mixtures of datatypes in the same column and still expects meaningful sorting is probably asking for a bit much!

Instead of scanning each column for a sort type on page load, would it be possible to scan the column just before attempting the sort? It would slow down the sort (a bit), but speed up the loading process.... - just a thought.

allan

That's a good idea - thanks for the suggestion. Part of the reason it is done the way it is at the moment is that sType is also used for building the internal filter, which is done at page load time for a little bit of speed when actually doing the filter (much like the current method makes sorting faster the first time). However, the current method does mean that the type that is detected is never used, if the sorting or filter isn't used... I'll take this into consideration in future versions of DataTables, but for the moment, if maximum speed is needed sType can just be defined, which skips type detection completely.

Regards,
Allan