SearchPanes with numbers fail to filter rows

TrilceAC

Hi,

I'm trying to use searchPanes with data coming from an AJAX source, being that data a list of objects.

The rendering and counting of the panes works flawlessly: The panes shows the proper numbers. The problem that I'm experiencing occurs when I try to filter the table by clicking on any pane which source of data should be numeric, i.e., if the pane renders text, it filters the data correctly, but if the pane filters data that are numbers in the data source, then all the rows are filtered out.

The problem seems to be that searchPanes does not handle properly numeric data when the data is in fact numeric, maybe because it might perform a === comparison. I'm making this conjecture because if I implement a render to handle numeric fields on filter, then the pane works as expected. This is a code snipped of what I have done to make a pane with numbers to work:

    const tableDatasets = $('#tableDatasets').DataTable({
        ajax: {
            url: Flask.url_for('dataset.index'),
            dataSrc: ''
        },
        rowId: 'id',
        columns: [
            {
                data: 'id',
            },
            {
                data: 'number',
                render: function(data, type, row, meta) {
                    if (type === 'filter') {
                        if (data !== null) {
                            return data.toString();
                        }
                        return 'No Data'
                    }
                    return data;
                }
            },
            {
                data: 'version'
            }
        ],
        dom: 'Pfrtip'
    });

As you see, in order for the pane to filter by number column, the second one, there should be a render function just for handling the filter and convert the data into a string or into 'No Data' in case that the data is null. Without this render function, the pane for number just does not work.

Is it the proper way to do it? I would have expected an easier, almost automatic, way to do it.

I have run the datatables debugger. The code provided by the debugger is: akococ

Thanks for your help,
Carlos

sandy

Hi @TrilceAC ,

SearchPanes should handle the numbers automatically. Could you link to a test case showing your problem please? Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Thanks,
Sandy

TrilceAC

Hi @sandy ,

Thank you for your interest.

I have been able to prepare a simple test case with almost minimal data and code. I also prepared another one that is almost my original case which is far more polluted.

The behaviour that I am seeing in the simple example is:
* The pane for Text works just fine.
* The pane for Number is rendered, but when any of its rows is selected surprisingly no matching record is found.
* The pane for Number to string, which has a render function that converts the data into a String only for filter works fine.

It might well be an issue with my environment, which right now is MacOSX 10.15.7 and firefox 81.0, but I can confirm that the behaviour in Chrome 85.0.4183.121 is just the same. Note that I used Bootstrap 4 and jQuery 3.5.1 in both examples, but this they are not the root of the problem.

Thanks again for your interest and for helping me with this wonderful product that is DataTables.

Cheers,
Carlos

sandy

Hi @TrilceAC ,

Thanks for the test case. A small issue, the searching that SearchPanes uses was doing a strict type check (===) and the two bits of data that is was comparing were of string and number above. I've changed this to just be a == and now it is working nicely. Commit is here if you are interested.

This will be available in the next SearchPanes release which we hope will be in the next few weeks. Until then you can access the fix from the nightly builds.

Thanks,
Sandy

colin

Just to confirm this has been address - see your test case updated with the nightly builds. We're hoping to release this in the next week,

Colin

TrilceAC

Hi @sandy, @colin

I had prepared an elaborate answer about this just here which I have posted, but I'm afraid that editing a reply to fix a typo I ended up somehow deleting it. I'm really sorry for that. The idea of it was the following:

The change improves the situation but does not properly handle null values as you can see in this test case.

With Numeric columns a null is shown in the search pane as No Data, but it filters all the rows probably because the rendered value for null in the table is '' and 'No Data' == '' is false. Furthermore, 0 also messes the selection because 0 == '' is true. This causes that when the user selects No Data, no row pass the filter, but when you select 0, the rows without data are also selected.

With columns that have empty strings '' and null this is also an issue:
* Two different No Data options are shown in the pane.
* One of them selects nothing, whereas the other one selects both the rows which cell data is null as well as the rows which cell data is ''.

I also tested what happens with undefined, but it breaks everything and I did not dig into further.

I proposed in my deleted answer to mark the cells that have this kind of data with a data- attribute in the html code. Maybe the real data might be set into this data attribute, or just the data type, or maybe is enough to mark null values, so that the comparison can be properly done. This was just an idea to overcome this odd behaviour and properly handle null values.

I really regret to have deleted my own answer. Sorry for that.

Cheers,
Carlos

sandy

Luckily, I've managed to find it in a tab that I had open and hadn't yet closed. You said as follows.

Hi @sandy ,

Thanks for your reply. There are still issues with this change: null is not properly handled because the value in the search pane is 'No Data', and 'No Data' == null is false, therefore rows with null values don't pass the filter.

I have prepared a modified test case that includes some tricky cases: data with null values and 0 values for the number column, as well as empty strings '' and null values for the text column. As you can check, there are some undesirable behaviours:

In the case of the Number search pane:
* No row pass the filter when the filter chosen is No Data.
* Four rows pass the filter when the 0 is selected in the search pane. I guess that 0 == '', and therefore, rows with null values, that is rendered as '' satisfy the filter.

In the case above, I think that an strict comparison is appropriate, but No Data should be checked against null. Maybe, it would worth to add special data- attribute to indicate the actual value of the data, or the fact that cell has the special value null, or at least the type of the provided value, and proceed accordingly, because generally speaking, when dealing with numbers, no value is not the same as a value of 0.

As I said, I also polluted the text related data, including empty strings '' and null values. Of course, the behaviour is inconsistent too:
* The Text search pane has rendered two different entries labelled as No Data, each with one item according to their respective counters. One should correspond to '' whereas the other should correspond to the null value.
* but when selecting them, one of them filters all the rows (I bet that it performs 'No Data' == '' which is false), whereas the other one allows rows 5 and 6 to pass the filter and be rendered in the table (maybe performing '' == '' which is true).

I have also tried other more unusual value: undefined, but it breaks everything and I did not dig further.

As I said, maybe providing the actual value of non string values by means of a data- attribute, or labelling the cells which have special values, like null, or at least labelling the type of the cell when it is not string, could made the trick, but this seems to be a bit more complex than the current approach.

Sorry for the headache,
Carlos

I've since made a further commit, but had not yet been able to find your original, now though I have.

I'm going to dig further into this with the information above. The last commit will also be in the nightly build.

sandy

Reading through it all again I hope that most of what you have pointed out has been covered. I'm still to look at undefined values and will hopefully have that fixed soon.

Sadly, there isn't a nice way to apply and read an attribute to the cells without the performance taking a massive hit. The search data is read as it is easily available and quick to work with. What we really need to add, as I have done to some extent in the previous commits, is better handling for these edge cases.

Bear with me and I will take a look at the undefined datas behaviour.

Thanks,
Sandy

sandy

Hi @TrilceAC ,

Had some further discussion about this. When you try to initialise a DataTable in that way and pass in undefined data, it should show you a warning about requesting an unknown parameter. In essence, the dev should be solving this anyway to avoid any other undefined behaviour within DataTables. So, SearchPanes isn't expected to cope with that.

Let me know if the previous fix still throws up any issues for you.

Thanks,
Sandy

TrilceAC

@sandy you found it! Thanks!!!

I'm aware that polutting the html makes it harder to generate and harder to parse too. It is a really complicated compromise, therefore the suggestion of only marking those cells that are not what they have rendered, or maybe those where the == operator can lead to wrong results. Anyway, a cell without content and datatables using ajax or JSON is a candidate for this kind of checks.

To me the difference between 0 and null is quite clear, but the difference between '' and null is far more subtle since many people have stored historically the absence of values in VARCHAR columns of databases as empty strings '' whereas other people have chosen NULL. Even worse, if the data is not properly curated, one can find databases were both values are used to represent the same, even in the same column, because two different input forms for the same field are just handled differently. This is really unfortunate, but things happen...

I'm really glad that you have addressed this. I'm not sure what else I can provide. I'm aware that the changes are a bit harder than just changing the comparator, but I can mark any of your answer as the one that solved the issue if you want me to.

Thanks!
Carlos

sandy

Hi @TrilceAC ,

The fix I have implemented should do an exact, strict match first, before looking at any edge casing. It will then do a looser check if that does not work, this should be enough to check numbers that have a type of number, against numbers in string form. From there it then checks to see if the value selected is null if so, it then checks that the data that DataTables picks up from the table is an empty string '' (DataTables does this if null data is found).

I think that should cover the edge cases that you have pointed out above. As I've said if you find anymore, please do let us know so that we can tidy them up

Thanks,
Sandy

TrilceAC

@sandy , we were writing simultaneously.

I see reasonable to show a warning for undefined.

To my understanding, null should be properly handled by datatables because I think that many APIs can return that value, i.e. it can be relatively common and it is a value available in many other languages like SQL, PHP or Python to mention some of them.

This is just an opinion, but whatever you decide, proper documentation on how to handle null values should be enough. As I did, I can always write my own colums.data and/or colums.render to handle null values the way I want. In the case of data panes, it is just odd to be aware of the fact that I have to rewrite the filter case and that I have to return 'No Data' as I did, or maybe ''.

Cheers,
Carlos

TrilceAC

Hi @sandy ,

The changes you made actually improve the behaviour of the last test case that I provided.

A minor issue to consider is whether during the creation of the search pane for Text, null and '' should or should not be grouped into the same No Data: Either you group together null and '' or when filtering, they behave differently. Right now, there are two No Data groups: One created because of '' and the other one created because of null. The counters of each group reflect this fact. This can be odd for an uninformed user, but so far so good. My concern arises when clicking any of them: Both rows pass the filter and are rendered independently of which No Data you clicked, which is not crazy, but somehow breaks the consistency with what the counter said and with the fact that you have two different groups. Therefore, I suggest you to either always distinguish between '' and null (including the searches), or to never distinguish between them and when creating the pane and the counters to consider null as '' or vice versa.

Anyway, I appreciate the work you have done. Apologies for being a pain.

Cheers,
Carlos

sandy

Hi @TrilceAC ,

Thanks for pointing that out. You're quite right I would say that makes sense. I've raised an issue internally (DD-1692 for my reference) and will report back here when there is an update.

Thanks,
Sandy

colin

This has now been addressed in nightly builds - see your example updated here. We're aiming to make releases in the next couple of weeks and this will be included in that.

Colin

agrogers

Sorry to clutter up your ticket. Thank you for this post. It helped me solve my problem too. In fact, there have been a lot of posts that have solved my problems but there is no easy way to say thank you. If you had a button to say 'Big Thank You' i would push it often.

colin

Ha, glad it helped. and glad the forum is proving useful!

Colin

TrilceAC

Hi @colin and @sandy,

Wonderful work! Thanks for addressing these issues beautifully.

Cheers,
Carlos