SearchBuilder does not pass date to backend, when datetime is supplied in a serverside setup

jaygrayson

Description of problem:
I have looked everywhere and I do not see a way to make this work. When using DataTables and SearchBuilder as ServerSide, submitting a date field with a format of DateTime, the value is not passed to the backend.

I am not sure where to go from here? Is there something that needs to be overridden to tell SearchBuilder to send the value, without checking it or something?

I do not know of a way to create a CodePin for this, since it is using ServerSide. You can only notice the error in the ServerSide code anyway. The value that gets sent for the field is empty.

I have tried submitting multiple values through the SearchBuilder and always get the same results. Values tested:

• Date = 2022-04-13 12:00:00
• Date = 2022-04-13T12:00:00
• Date = 2022-04-1312:00:00
• Date = 12:00:00 2022-04-13
• Date = 12:00:002022-04-13

The value supplied to the backend:

{
   "condition":"=",
   "data":"Date",
   "origData":"received",
   "type":"date",
   "value":[""],
   "value1":""
}

I have read about moment and luxon. I have attempted to add both of those, but they make no difference. I believe the documentation says that those mean nothing if you are using serverside. I just need the value that is in the Date field to be passed to the backend code. I can handle it from there. What am I missing?

colin

It's interesting that the type is "date", and not "datetime". How have you defined that field within Editor and DataTables?

Colin

jaygrayson

Interesting. Based on column.type documentation, there is no datetime column type. https://datatables.net/reference/option/columns.type. I went back and added type="datetime" to the field in datatables init and it just took away the datepicker.

The documentation suggest using moment.js, but using moment.js does not seem to do anything. I have included the proper js files, in the correct order (took a minute to get that right):

<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>
<script src="/media/assets/admin/include/datatables/DataTables-1.11.5/js/jquery.dataTables.js"></script>
<script src="/media/assets/admin/include/datatables/SearchBuilder-1.3.2/js/dataTables.searchBuilder.min.js"></script>
<script src="/media/assets/admin/include/datatables/DateTime-1.1.2/js/dataTables.dateTime.min.js"></script>
<script src="/media/assets/admin/include/datatables/Buttons-2.2.2/js/dataTables.buttons.js"></script>
<script src="https://cdn.datatables.net/plug-ins/1.11.5/sorting/datetime-moment.js"></script>

and added the override to our code:
jQuery.fn.dataTable.moment( 'Y-m-d H:i' );

Adding the moment.js and adding the override to the functionality does not affect SearchBuilder nor DataTables. The dates in the date columns are still showing up as 'Y-m-d H:i:s' format.

jaygrayson

Just to be clear, I was trying different date formats. The moment.js format does not work for overriding the moment function either.
jQuery.fn.dataTable.moment( 'YYYY-MM-DD HH:mm' );

jaygrayson

FYI, here is a SearchBuilder CodePen that shows the same issue, not using serverside processing:
https://codepen.io/p2g-jaygrayson/pen/bGaxyaO

jaygrayson

WOW! Well, good news is that we figured it out. The bad news is that SearchBuilder behaves really weird. When you specify the overrides for moment(), the format has to match every field in the datatable. This seems like a weird design because there might be cases where you would want a field with the format YYYY-MM-DD HH:mm:ss, but not want to show the builder for the seconds field. Anyway, we got it working, by specifying the correct format for the datatable design.
jQuery.fn.dataTable.moment( 'YYYY-MM-DD HH:mm:ss' );

allan

Hi,

When you specify the overrides for moment(), the format has to match every field in the datatable.

By "field" do you mean each cell in a column? With the jQuery.fn.dataTable.moment plug-in you register a date/time format to let DataTables understand it. It does mean that to be able to detect a column with that type, all cells in that column must be the same format. However, you can have different formats in different columns.

I'm not quite clear on what the issue the CodePen is showing. Could you clarify the steps to reproduce the issue for me please?

I'm actually working on integrating better date/time support into DataTables core at the moment, including support for transforming date formats, and also registering formats like what jQuery.fn.dataTable.moment does.

Allan

jaygrayson

Yes. We were looking at it wrong to begin with. We were expecting the override for moment to format the dates the way that we specified, but it was the reverse of that.

I apologize about the CodePen. I modified the code to make it work without thinking that I had put that CodePen in this ticket. That CodePen is working code.

Something that we did notice after the fact is that when using ServerSide with a button modal window, when we specify the format of date using the moment override, the value that is put into the input field when the search returns is ISO8601 format. It's like the opening of the modal window, when putting the value into the input, does not honor the moment override. If this is not clear, I can get some screenshots to show you.

jaygrayson

We also noticed a weird behavior with the submission of dates to the backend. We store all of our dates in the database in UTC. This is what displays on the front-end, in datatables. When we perform a date search with SearchBuilder, the value that is submitted to the backend is in the timezone of the clients browser. We worked around this by also passing the offset value from javascript along with the query and modified the value in the controller before performing the query. It was just a little odd to us that it would do that.

allan

I wonder if it is UTC but with a timezone added? If you could link me to an example showing these issues, that would be really useful. I think the timezone one at least is an error.

Allan

jaygrayson

What site can I use that will let me perform front-end and back-end code? I don't know of one.

allan

Might you be able to PM me a link to your page showing the issue? You can send a PM by clicking my forum user name and then "Send message".

If that isn't possible, perhaps something like https://stackblitz.com/ might help to produce a working example?

Allan

jaygrayson

If you are familiar with Laravel, I have created a small Laravel project here:
https://github.com/Ir8Coder/searchbuilder
If you are not, the code that you need to duplicate this issue is here:
https://github.com/Ir8Coder/searchbuilder/blob/6514f19a9ea178827e53aaf6ee41878fcc6e3918/resources/views/welcome.blade.php
It is literally just setting up a serverside DataTable with SearchBuilder.

The way that you can see this work oddly is:
1. Notice the exact date/time in the Date field that you are going to search on
2. Perform a search with SearchBuilder on a Date field
3. Notice the values in the payload:
     searchBuilder[criteria][0][type]: moment-YYYY-MM-DD HH:mm:ss
     searchBuilder[criteria][0][value1]: 2022-04-18T05:00:00.000Z
    a. These values are already a little odd to me because the type should control the format of the value that is passed in the value1, value2 criteria IMO.
4. Change the TimeZone on your local computer
5. Reload the page
6. Notice the exact date/time in the Date field that you searched on is still the same date/time
7. Perform the same search with SearchBuilder as before
8. Notice the values in the payload are now different, based on your TimeZone you have selected on the computer.

For what it is worth, I am using:
macOS Monterey version 12.3.1
Chrome Version 100.0.4896.75 (Official Build) (x86_64)

jaygrayson

Let's cut a long story to a short one. The conversion that is being performed by SearchBuilder , on the date values that are sent to the backend, is not working consistently at all. Without even changing the TimeZone on the computer, SearchBuilder is adding different values to the value that we need passed in to the server. Some records it will add 4 hours to create a UTC, some it will add 5. It is very inconsistent.

How do we make SearchBuilder pass the value that is displayed in DataTables so that we can make this work? We just want it to pass the value shown and not do any type of conversions.

allan

Yup, sorry, I'm not familar with Laravel enough - I tried a basic command and didn't get anywhere.

→ php artisan cache:clear    
PHP Fatal error:  Uncaught Error: Class 'Illuminate\Foundation\Application' not found in /home/vagrant/DataTablesSrc/build/test/searchbuilder-main/bootstrap/app.php:14
Stack trace:
#0 /home/vagrant/DataTablesSrc/build/test/searchbuilder-main/artisan(20): require_once()
#1 {main}
  thrown in /home/vagrant/DataTablesSrc/build/test/searchbuilder-main/bootstrap/app.php on line 14

However, many thanks for the code - I see the issue now. I've just made this commit to have SearchBuilder send the information to the server-side be local time (i.e. the time that was selected) in YYYY-MM-DD hh:mm:ss format if it was detected as a date.

That will be in the nightly build very shortly.

Regards,
Allan

jaygrayson

Nice! Good work! It works as expected. Thank you! Where can I send you a coffee?

colin

Thank you! We always love donations - this page lists a few options,

Colin

jaygrayson

Thank you!

lightningsea

This thread was very helpful for me as well. I updated to latest code and it worked great. Thanks for all you do, allan, and the community!