How to use date range filter and filter on the same table?

FRS4002

I need to add date range filter + filter on the same table. Date range filter is working but filter is not working as intended. Here is the test case: http://live.datatables.net/radogoxa/1/edit

tangerine

filter is not working as intended.

What do you mean? What happens?

If you are talking about the search input box, it's working for me in your test case.

FRS4002

@tangerine Yes, I am talking about search input box. Try to search for "Garrett Winters" and check ...

kthorngren

Datatables performs an AND search across all the columns. You would need an OR search to search for items that are not included in the selected date range. To do this you can either create your own search input or take over the default search input, something like this:
http://live.datatables.net/jorexujo/1/edit

The search event handler will just call draw() to run the search plugin and not use search(). in your range search plugin you will need to add code to see if there is a match in the row for the search input or if the range matches. If either condition is true then return true. Otherwise return false.

Kevin

FRS4002

@kthorngren Could you please explain and help me more... Because I didn't understand... and I am having this issue for long time...

allan

Which specific part of Kevin's answer and example do you need clarification on? Perhaps you can show us what you have tried so far?

Allan

FRS4002

@allan What I have tried is in the test case above. I just need a explanation of Kevin's test case on how to implement it with date range filter on my test case... I guess the filter didn't work because only today's row or selected days row appear so the filter only works when on what is appear on the screen... I don't know if it is the case... and I don't know how to fix it... It seems difficult to implement...

kthorngren

I guess the filter didn't work because only today's row or selected days row appear so the filter only works when on what is appear on the screen

Essentially that is correct.

I created an example of replacing the default Datatables search with a search plugin. It takes over the event handlers for the search input. The plugin uses regex try matching the data in each column. You may find using Javascript indexOf() or includes() to be more efficient. I never compared them. If its of concern you can see if there are Stack Overflow or other threads discussing the difference in performance. Start by getting this example to work the way you want.
http://live.datatables.net/jorexujo/702/edit

Once its working then update your date range example to have a boolean variable (rangeFound for example), like the found variable in my example, that is returned at the end of the function. So only one return. Once you have that working then combine the two into one function and at the end use something like return found || rangeFound;. This will return true if either the range matches or the search input matches.

Kevin

FRS4002

@kthorngren I wonder why the filter doesn't work unless the rows appear on the screen, but date range filter works very well. I recommend fixing this on the official datatables source code. Now, regarding your test case, I just need to add the date range filter with some modifications?

tangerine

Try to search for "Garrett Winters"

"Garrett Winters" is not found because that record is outside the date range you have set.
That demonstrates the difference between AND and OR which Kevin has explained to you. In that example, your filters are saying "Find records between these dates AND name equals Gareth Winters". Obviously that won't find anything.
What don't you understand here?

FRS4002

Yeah thanks @tangerine I already understand this part. Now I am trying to find a way to merge date range filter with the regular filter. Again, I hope this will be added in the documentation in the future... Since, I am not that good on coding, and I always use Datatables documentation to make things easier on me...

allan

The documentation will always break components down - e.g. the date filter on its own. We assume a certain level of Javascript knowledge since we can't possibly document Javascript from first principles.

That said, I'm actually not clear on what the problem you are having is still? When you say merge the two filters, do you mean you want a single input element? Or are you just trying to have the date filter inputs and the global filter for the table shown?

Allan

kthorngren

Here is the updated OR search example:
http://live.datatables.net/radogoxa/3/edit

I found that you can't use the default Datatables search input. A process within the Datatables code clears the input at some point - presumable because there is no search via the input taking place. Since you are attaching the date input to the builtin search I hid it the search input in initComplete. I created a customer search input and used the toolbar technique in this example to show it. Your date range search didn't seem quite right so I made some changes.

Kevin

FRS4002

@kthorngren Thanks! Your test case works pretty well! But if you noticed in my test case, I wrote a code that shows today's code when opening the page/link. How can I add this in your test case? I've added this
} else if ( max <= "" && min <= "" ) { if ( moment().format('YYYYMMDD') === date ) { return true; } return false; }
before the last dateRangeFound = false but it didn't work...

kthorngren

but it didn't work

What happens?

If you use return statements then the other code that checks the search input value won't be executed eliminating the OR capabilities for this case. Use some breakpoints or console.log statements to do some debugging of the new if statement and flow of the code to learn what its doing.

Kevin

FRS4002

@kthorngren Yeah so I need to show today's row when opening the page/link. When I add the code above, nothing happens. It still shows all rows when loading the page...

kthorngren

You might need to move your code up a little bit to this section:

    // Show the row if the search input and date range input are blank
    if ( search.length === 0  && max === "" && min === ""  ) {
      return true;
    }

On page load this if statement will be true so all the rows will be shown and the code below it won't be exevuted.

Kevin

FRS4002

@kthorngren I added today's date in the table and I moved the code above if statement but I am getting in the output "No matching records". I tried also moving the code below if statement nothing happened... Still all rows are shown. I reviewed my code in my test case and I found that I MUST put this code

if ( max <= "" && min <= "" ) {
          if ( moment().format('YYYYMMDD') === date ) {
            return true;
          }
          return false;
        }

under this code

var date = moment( data[4], 'DD/MM/YYYY' ).format('YYYYMMDD');

so I tried that but I am still getting all rows...

allan

Please link to a page showing what you have used so we can take a look.

Allan

FRS4002

@allan This is 1st try http://live.datatables.net/zivovavo/1/edit
This is 2nd try http://live.datatables.net/getojose/1/edit

kthorngren

I moved your code inside the if statement like I suggested above:
http://live.datatables.net/bakeyaco/1/edit

Kevin

FRS4002

@kthorngren Yep, showing today's row on load is working in your test case but the date range filter stopped working... Is it impossible to use date range filter + regular filter + showing today's row on load?

kthorngren

Sorry placed this statement in the wrong place:

var date = moment( data[4], 'DD/MM/YYYY' ).format('YYYYMMDD');

http://live.datatables.net/bakeyaco/2/edit

Kevin

FRS4002

@kthorngren Thanks!!! It worked perfectly fine!!! But there is a very small issue I need to ask about in search input box. If I typed the letter "t", all rows that has the letter "t" will appear which is not what I want. I need to filter letter by letter. For example, if I type "t" it must bring all rows that start with the letter "t" and so on. So, I need to search in an order of the letters.

kthorngren

Change the regex expression to make a more specific search, maybe something like this:

      // Build regex expression
      let re = new RegExp( '^' + search + '$', 'i' );

Kevin

FRS4002

@kthorngren Here is the test case http://live.datatables.net/necananu/1/edit
I've replaced
// Build regex expression let re = new RegExp( search, 'i' );
with
// Build regex expression let re = new RegExp( '^' + search + '$', 'i' );
and I tried to type the letter "t" it says no matching records.

kthorngren

Misread your problem description. Remove the trailing $, like this:

http://live.datatables.net/necananu/2/edit

Kevin

FRS4002

@kthorngren Thanks! 100% done! Thank you very much! Also thanks to @allan

FRS4002

@kthorngren I've seen you've used an older version of datatables 1.11 I have the latest version which is 1.12 and it didn't work... Is there a way to make it work with the latest verison?

FRS4002

@kthorngren You made the search input box in a toolbar, but I can make it in the same div/row of the date range filter inside "initComplete" function, not independent in a toolbar?

kthorngren

It works with DT 1.12.1:
http://live.datatables.net/necananu/2/edit

Your test case is loading datatables.js multiple times which you shouldn't do.

You made the search input box in a toolbar, but I can make it in the same div/row of the date range filter inside "initComplete" function, not independent in a toolbar?

Yes, its a custom search input. You can create it however and where ever you want.

Kevin