Bug 32559 - Support formatted date for column filtering (DT)
Summary: Support formatted date for column filtering (DT)
Status: RESOLVED DUPLICATE of bug 34226
Alias: None
Product: Koha
Classification: Unclassified
Component: Staff interface (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low normal (vote)
Assignee: Jonathan Druart
QA Contact: Kyle M Hall
URL:
Keywords:
Depends on: 30310 32505
Blocks:
  Show dependency treegraph
 
Reported: 2023-01-03 16:58 UTC by Jonathan Druart
Modified: 2023-07-24 15:47 UTC (History)
5 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 32559: Add support for formatted date when filtering on a column (5.11 KB, patch)
2023-01-03 17:21 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 32559: Add support for formatted date when filtering on a column (5.16 KB, patch)
2023-01-03 18:00 UTC, Lucas Gass
Details | Diff | Splinter Review
Bug 32559: Add tests (6.51 KB, patch)
2023-01-04 10:14 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 32559: Add support for formatted date when filtering on a column (5.21 KB, patch)
2023-01-06 19:17 UTC, Kyle M Hall
Details | Diff | Splinter Review
Bug 32559: Add tests (6.56 KB, patch)
2023-01-06 19:18 UTC, Kyle M Hall
Details | Diff | Splinter Review
Bug 32559: [ALT] Use column 'type' to pre-process search patterns (5.56 KB, patch)
2023-01-09 10:14 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 32559: Add support for formatted date when filtering on a column (3.64 KB, patch)
2023-07-24 15:47 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 32559: Add tests (26.72 KB, patch)
2023-07-24 15:47 UTC, Martin Renvoize
Details | Diff | Splinter Review
Bug 32559: [ALT] Use column 'type' to pre-process search patterns (5.38 KB, patch)
2023-07-24 15:47 UTC, Martin Renvoize
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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.