Bug 32559

Summary: Support formatted date for column filtering (DT)
Product: Koha Reporter: Jonathan Druart <jonathan.druart>
Component: Staff interfaceAssignee: Jonathan Druart <jonathan.druart>
Status: RESOLVED DUPLICATE QA Contact: Kyle M Hall <kyle>
Severity: normal    
Priority: P5 - low CC: gmcharlt, kyle, lucas, martin.renvoize, tomascohen
Version: unspecified   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=34226
Change sponsored?: --- Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on: 30310, 32505    
Bug Blocks:    
Attachments: Bug 32559: Add support for formatted date when filtering on a column
Bug 32559: Add support for formatted date when filtering on a column
Bug 32559: Add tests
Bug 32559: Add support for formatted date when filtering on a column
Bug 32559: Add tests
Bug 32559: [ALT] Use column 'type' to pre-process search patterns
Bug 32559: Add support for formatted date when filtering on a column
Bug 32559: Add tests
Bug 32559: [ALT] Use column 'type' to pre-process search patterns

Description Jonathan Druart 2023-01-03 16:58:44 UTC
When dates are displayed formatted (following the dateformat syspref) in a DataTable table and that there is a filter for the column, we should filter correctly the data.
Comment 1 Katrin Fischer 2023-01-03 16:59:52 UTC
Hm, maybe data-search could work?
Comment 2 Jonathan Druart 2023-01-03 17:21:23 UTC
Created attachment 144951 [details] [review]
Bug 32559: Add support for formatted date when filtering on a column

When dates are displayed formatted (following the dateformat syspref)
in a DataTable table and that there is a filter for the column, we should
filter correctly the data.

Test plan:
Assuming dateformat is set to metric
Go to the main patron search, search for all patrons (don't provide a
search term)
In the "date of birth" column filter, enter "1958", notice that you see
Henry
Search using an iso formatted date 1958-05-30
Search using a metric formatted date 30/05/1958

Note that:
- it's also working for "Expires on" on the same table. We may want to
adjust other tables that are using the DT REST API wrapper and the
column filters
- You need to search for the full formatted date to make it work.
"30/05" won't work.
Comment 3 Jonathan Druart 2023-01-03 17:29:19 UTC
(In reply to Katrin Fischer from comment #1)
> Hm, maybe data-search could work?

Nope, we need to deal with it "manually", we cannot rely on DT internals. Here we are using the REST API and don't have the data loaded in the DOM already.

However I would be happy if someone else come with a better solution.
Comment 4 Lucas Gass 2023-01-03 18:00:19 UTC
Created attachment 144954 [details] [review]
Bug 32559: Add support for formatted date when filtering on a column

When dates are displayed formatted (following the dateformat syspref)
in a DataTable table and that there is a filter for the column, we should
filter correctly the data.

Test plan:
Assuming dateformat is set to metric
Go to the main patron search, search for all patrons (don't provide a
search term)
In the "date of birth" column filter, enter "1958", notice that you see
Henry
Search using an iso formatted date 1958-05-30
Search using a metric formatted date 30/05/1958

Note that:
- it's also working for "Expires on" on the same table. We may want to
adjust other tables that are using the DT REST API wrapper and the
column filters
- You need to search for the full formatted date to make it work.
"30/05" won't work.

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>
Comment 5 Martin Renvoize 2023-01-04 08:42:19 UTC
This looks like a reasonable approach.. I did similar in another bug somewhere a while ago, but this implimentation is a little cleaner.

Can we get some unit tests for it?  There are tests for the dt wrapper right.. I can't entirely remember.
Comment 6 Jonathan Druart 2023-01-04 09:27:42 UTC
(In reply to Martin Renvoize from comment #5)
> This looks like a reasonable approach.. I did similar in another bug
> somewhere a while ago, but this implimentation is a little cleaner.
> 
> Can we get some unit tests for it?  There are tests for the dt wrapper
> right.. I can't entirely remember.

Yes, t/db_dependent/selenium/patrons_search.t
Good idea, I will provide them.
Comment 7 Jonathan Druart 2023-01-04 10:14:27 UTC
Created attachment 144977 [details] [review]
Bug 32559: Add tests
Comment 8 Martin Renvoize 2023-01-04 11:11:52 UTC
Excellent, thanks Jonathan.

Now we just need the blockers to get unblocked.. I'm chasing Marcel on that as I think he's the most recent touch on them.
Comment 9 Martin Renvoize 2023-01-04 11:21:02 UTC
The other bug I was talking about was 'BugĀ 29302 - The dataTables Koha RESTful API wrapper should allow picking match type at the column level'.. where I was trying to do this closer to the datatables JS instantiation rather than in the table header markup.
Comment 10 Jonathan Druart 2023-01-04 12:54:24 UTC
Ideally we could have use 'type' (https://datatables.net/reference/option/columns.type) with type=date. What I've tried first.
But it got removed by DT because it's not useful when server-side processing is used:
"""Please note that if you are using server-side processing (serverSide) this option has no effect since the ordering and search actions are performed by a server-side script.)"""
Comment 11 Kyle M Hall 2023-01-06 19:17:52 UTC
Created attachment 145094 [details] [review]
Bug 32559: Add support for formatted date when filtering on a column

When dates are displayed formatted (following the dateformat syspref)
in a DataTable table and that there is a filter for the column, we should
filter correctly the data.

Test plan:
Assuming dateformat is set to metric
Go to the main patron search, search for all patrons (don't provide a
search term)
In the "date of birth" column filter, enter "1958", notice that you see
Henry
Search using an iso formatted date 1958-05-30
Search using a metric formatted date 30/05/1958

Note that:
- it's also working for "Expires on" on the same table. We may want to
adjust other tables that are using the DT REST API wrapper and the
column filters
- You need to search for the full formatted date to make it work.
"30/05" won't work.

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 12 Kyle M Hall 2023-01-06 19:18:07 UTC
Created attachment 145095 [details] [review]
Bug 32559: Add tests

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 13 Martin Renvoize 2023-01-09 10:13:22 UTC
(In reply to Jonathan Druart from comment #10)
> Ideally we could have use 'type'
> (https://datatables.net/reference/option/columns.type) with type=date. What
> I've tried first.
> But it got removed by DT because it's not useful when server-side processing
> is used:
> """Please note that if you are using server-side processing (serverSide)
> this option has no effect since the ordering and search actions are
> performed by a server-side script.)"""

Interesting.. I tried with 'type' instead of 'datatype' and it didn't get dropped for me..
Comment 14 Martin Renvoize 2023-01-09 10:14:34 UTC
Created attachment 145136 [details] [review]
Bug 32559: [ALT] Use column 'type' to pre-process search patterns

This patch adds the option to pass 'type' at the 'column' definition
level and then binds on that type to add date parsing to queries.
Comment 15 Martin Renvoize 2023-01-09 10:15:57 UTC
Temporarily in discussion whilst I catch Jonathan... I'm still of the opinion his patchset is a bit more functional than mine.. but I am interested to see why the 'type' term didn't work for him when it worked in my patch? Assuming I'm mis-understanding something somewhere.
Comment 16 Martin Renvoize 2023-07-24 15:31:29 UTC

*** This bug has been marked as a duplicate of bug 34226 ***
Comment 17 Martin Renvoize 2023-07-24 15:31:59 UTC
Marking as a duplicate and I'm going to lift the tests out of here to re-use with the other patchset.
Comment 18 Martin Renvoize 2023-07-24 15:47:16 UTC
Created attachment 153845 [details] [review]
Bug 32559: Add support for formatted date when filtering on a column

When dates are displayed formatted (following the dateformat syspref)
in a DataTable table and that there is a filter for the column, we should
filter correctly the data.

Test plan:
Assuming dateformat is set to metric
Go to the main patron search, search for all patrons (don't provide a
search term)
In the "date of birth" column filter, enter "1958", notice that you see
Henry
Search using an iso formatted date 1958-05-30
Search using a metric formatted date 30/05/1958

Note that:
- it's also working for "Expires on" on the same table. We may want to
adjust other tables that are using the DT REST API wrapper and the
column filters
- You need to search for the full formatted date to make it work.
"30/05" won't work.

Signed-off-by: Lucas Gass <lucas@bywatersolutions.com>

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 19 Martin Renvoize 2023-07-24 15:47:18 UTC
Created attachment 153846 [details] [review]
Bug 32559: Add tests

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 20 Martin Renvoize 2023-07-24 15:47:21 UTC
Created attachment 153847 [details] [review]
Bug 32559: [ALT] Use column 'type' to pre-process search patterns

This patch adds the option to pass 'type' at the 'column' definition
level and then binds on that type to add date parsing to queries.