Possible bug in SearchBuilder

Possible bug in SearchBuilder

MoebiusMoebius Posts: 12Questions: 3Answers: 1
edited August 2021 in SearchBuilder

I've used the SearchBuilder library on my web app and it's amazing, but I noticed a possible bug.

On a table with only one date column, the drop down 'Condition' menu will give the 'Between' option, i.e. filtering on the date value being between date A and date B.

But if the table has more than one date column, the 'Between' option does not appear. Has anyone else experienced that and is there a workaround? If not, can I report it as a bug?

This is the link to the code I used in my app:

https://datatables.net/extensions/searchbuilder/examples/customisation/customMultiple

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 15,177Questions: 1Answers: 2,590

    It appears to be working fine here. Could you look at that, please, and see if it helps. If it's still not working for you, please can you update my example, or link to your page, so that we can see the problem.

    Cheers,

    Colin

  • MoebiusMoebius Posts: 12Questions: 3Answers: 1

    Hi Colin,

    Thank you for the response and the example. It confirms my further research.

    In your example, as far as I can tell, the 'Between' operator does not appear when these columns are chosen:

    Name
    Position
    Office

    But it does appear when these are chosen:

    Age
    Start Date1
    Start Date2
    Salary

    I see the same behavior in my application, so it has nothing to do with having one date column or not. I guess I need to revise my question.

    Is there some condition that causes the Between operator to appear or not to appear?

  • tangerinetangerine Posts: 3,350Questions: 37Answers: 394

    That would appear to be dates and numbers, unless you are expecting something like "between Edinburgh and San Francisco" or "between Technical Author and System Architect" - neither of which make any sense to me.
    What behaviour are you looking for?

  • MoebiusMoebius Posts: 12Questions: 3Answers: 1

    In my app, the issue is with date columns. I get that 'Between' on a non numerical column doesn't make sense. I was just trying to figure out the criteria that SearchBuilder uses to populate the drop down list.

    Here's what I've checked and double checked:

    • There are two different tables in my MySQL database
    • Both of them have DATE columns
    • Both are the same data type in MySQL
    • Both are formatted the same in the PHP page

    SearchBuilder returns the 'Between' option on the date column from the first table, but does not return the 'Between' option on the date column in the second table. I just need to find out why, because it's a requirement for my end users.

    The data is proprietary, but if necessary I can fake something up. Thank you for the support.

  • tangerinetangerine Posts: 3,350Questions: 37Answers: 394

    Ah, okay. Certainly I would expect "Between" to be offered for both date fields.
    Are both tables on the same page? And if so, do they have different ids?

  • MoebiusMoebius Posts: 12Questions: 3Answers: 1
    edited August 2021

    Yes, both are on the same page, but in separate tabs (nav tabs).

    They both have different id's, as in id="tableOne" or id="tableTwo". Then, I pass that to the SearchBuilder / javascript as #tableOne and #tableTwo.

    So right now, I have a separate script for each tab, as below. Maybe that's the problem?

                $('#tableTwo').DataTable( {
                        dom: 'Qlfrtip'
                    });
    
  • colincolin Posts: 15,177Questions: 1Answers: 2,590

    It sounds to me like your date is being treated as a string. It would be worth verifying that all the values are indeed dates.

    If no joy, as I said before, please can update my example to demonstrate the problem, or link to your page so we can take a look,

    Colin

  • MoebiusMoebius Posts: 12Questions: 3Answers: 1

    Okay, I copied and anonymized some date from my application and make a fork of Colin's live stream:

    http://live.datatables.net/kevegane/1/edit

    If it works, hopefully it shows that I have two columns, Created Date and Closed Date.

    Created Date returns the Between Option and Closed Date does not.

  • MoebiusMoebius Posts: 12Questions: 3Answers: 1
  • colincolin Posts: 15,177Questions: 1Answers: 2,590
    Answer ✓

    Yep, the problem is your date "0000-00-00" - that's not a valid date. Yep, the format is correct, but there isn't a month or day with value "00" , so the column is being treated as a string.

    The best way to address would be either to change the data at the source, or blank it out in columns.render - see example here: http://live.datatables.net/fibifure/3/edit

    Colin

  • MoebiusMoebius Posts: 12Questions: 3Answers: 1
    edited August 2021

    Yes, that worked. Thank you, thank you, thank you.

    Apparently the root cause is that the fields in the legacy Access database were blank. When they were exported to CSV and imported into MySQL in a date column, it rendered that as '0000-00-00'. I don't know if there's a way to handle that when loading the data, but lesson learned.

    One other thing I discovered is that to assign that to multiple columns it would be:

                        columnDefs: [{
                        targets: [4, 5],
                        render: function(data) {
                            return data === "0000-00-00"? '' : data;
                        }
                        }]
    

    Does that seem correct?

    SearchBuilder is an amazing plug in. Thank you for your support and help.

  • colincolin Posts: 15,177Questions: 1Answers: 2,590

    Yep, that's correct! Glad you're enjoying SearchBuilder - it's one of our newest extensions,

    Colin

Sign In or Register to comment.