Possible bug in SearchBuilder

Moebius

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

colin

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

Moebius

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?

tangerine

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?

Moebius

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.

tangerine

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?

Moebius

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'
                });

colin

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

Moebius

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.

Moebius

New fork:

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

colin

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

Moebius

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.

colin

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

Colin