Bug 24561 - Add a datatables API wrapper
Summary: Add a datatables API wrapper
Status: CLOSED FIXED
Alias: None
Product: Koha
Classification: Unclassified
Component: Architecture, internals, and plumbing (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement (vote)
Assignee: Agustín Moyano
QA Contact: Jonathan Druart
URL:
Keywords:
Depends on: 24615
Blocks: 20212 25279 25287 27402 30626
  Show dependency treegraph
 
Reported: 2020-02-01 04:12 UTC by Agustín Moyano
Modified: 2022-04-27 10:32 UTC (History)
6 users (show)

See Also:
Change sponsored?: ---
Patch complexity: Small patch
Documentation contact:
Documentation submission:
Text to go in the release notes:
This patch adds a datatables wrapper that allows using datatables against Koha's API. It implements: - Server side pagination - Filtering/searching - Embedding related objects in the request - Sorting and filtering by nested objects
Version(s) released in:
20.05.00


Attachments
Bug 24561: Datatables api wrapper also filter and order embedded columns (9.84 KB, patch)
2020-02-08 18:07 UTC, Agustín Moyano
Details | Diff | Splinter Review
Bug 24561: Datatables api wrapper also filter and order embedded columns (9.72 KB, patch)
2020-02-09 20:49 UTC, Agustín Moyano
Details | Diff | Splinter Review
Bug 24561: Datatables api wrapper also filter and order embedded columns (11.28 KB, patch)
2020-02-19 14:24 UTC, Agustín Moyano
Details | Diff | Splinter Review
Bug 24561: Datatables api wrapper also filter and order embedded columns (4.89 KB, patch)
2020-02-19 14:35 UTC, Agustín Moyano
Details | Diff | Splinter Review
Bug 24561: Datatables api wrapper also filter and order embedded columns (6.44 KB, patch)
2020-02-27 23:43 UTC, Agustín Moyano
Details | Diff | Splinter Review
Bug 24561: Datatables api wrapper also filter and order embedded columns (11.38 KB, patch)
2020-03-03 15:21 UTC, Agustín Moyano
Details | Diff | Splinter Review
Bug 24561: Datatables api wrapper also filter and order embedded columns (11.45 KB, patch)
2020-03-04 13:57 UTC, Kyle M Hall
Details | Diff | Splinter Review
Bug 24561: Datatables api wrapper also filter and order embedded columns (11.55 KB, patch)
2020-03-09 17:11 UTC, Agustín Moyano
Details | Diff | Splinter Review
Bug 24561: Add a Datatables api wrapper that implements filter and order embedded columns (11.62 KB, patch)
2020-04-27 03:01 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 24561: (follow-up) Set default matching criteria to 'contains' and add configuration option (4.87 KB, patch)
2020-04-30 14:24 UTC, Agustín Moyano
Details | Diff | Splinter Review
Bug 24561: (follow-up) Use full_numbers for pagingType (1.16 KB, patch)
2020-05-05 21:29 UTC, Tomás Cohen Arazi
Details | Diff | Splinter Review
Bug 24561: Add a Datatables api wrapper that implements filter and order embedded columns (11.69 KB, patch)
2020-05-06 14:11 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 24561: (follow-up) Set default matching criteria to 'contains' and add configuration option (4.95 KB, patch)
2020-05-06 14:12 UTC, Jonathan Druart
Details | Diff | Splinter Review
Bug 24561: (follow-up) Use full_numbers for pagingType (1.23 KB, patch)
2020-05-06 14:12 UTC, Jonathan Druart
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
Description Agustín Moyano 2020-02-01 04:12:21 UTC
Bug 24502 introduced filtering by embedded columns. It would be great if datatables wrapper for the API in bug 20212 could use this feature.
Comment 1 Agustín Moyano 2020-02-08 18:07:19 UTC
Created attachment 98612 [details] [review]
Bug 24561: Datatables api wrapper also filter and order embedded columns

This patch adds the ability to filter and order by embedded columns.

To use it you must in JS:

$('datatable_selector').api({datatables_options})

where datatables_options are all datatables options plus:

1. embed: [list of embeddable tables]
   This option adds x-koha-embed header to request.

2. header_filter: true|false
   This option if true sets x-koha-query header with stringyfied json of filters

Oderable and searchable columns must define data option as string, otherwise filter and order won't be possible.
If you must custom the output, use the render function.

For example:

* Don't

> $('.table_selector').api({
>   columns: [
>     {
>       data: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

* Do

> $('.table_selector').api({
>   columns: [
>     {
>       data: 'holds.patron.firstname',
>       render: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

To test you must implement and test bug 20936, where it will be used.
Comment 2 Agustín Moyano 2020-02-09 20:49:10 UTC
Created attachment 98624 [details] [review]
Bug 24561: Datatables api wrapper also filter and order embedded columns

This patch adds the ability to filter and order by embedded columns.

To use it you must in JS:

$('datatable_selector').api({datatables_options})

where datatables_options are all datatables options plus:

1. embed: [list of embeddable tables]
   This option adds x-koha-embed header to request.

2. header_filter: true|false
   This option if true sets x-koha-query header with stringyfied json of filters

Oderable and searchable columns must define data option as string, otherwise filter and order won't be possible.
If you must custom the output, use the render function.

For example:

* Don't

> $('.table_selector').api({
>   columns: [
>     {
>       data: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

* Do

> $('.table_selector').api({
>   columns: [
>     {
>       data: 'holds.patron.firstname',
>       render: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

To test you must implement and test bug 20936, where it will be used.
Comment 3 Agustín Moyano 2020-02-19 14:24:08 UTC
Created attachment 99254 [details] [review]
Bug 24561: Datatables api wrapper also filter and order embedded columns

This patch adds the ability to filter and order by embedded columns.

To use it you must in JS:

$('datatable_selector').api({datatables_options})

where datatables_options are all datatables options plus:

1. embed: [list of embeddable tables]
   This option adds x-koha-embed header to request.

2. header_filter: true|false
   This option if true sets x-koha-query header with stringyfied json of filters

Oderable and searchable columns must define data option as string, otherwise filter and order won't be possible.
If you must custom the output, use the render function.

For example:

* Don't

> $('.table_selector').api({
>   columns: [
>     {
>       data: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

* Do

> $('.table_selector').api({
>   columns: [
>     {
>       data: 'holds.patron.firstname',
>       render: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

To test you must implement and test bug 20936, where it will be used.
Comment 4 Agustín Moyano 2020-02-19 14:35:25 UTC
Created attachment 99255 [details] [review]
Bug 24561: Datatables api wrapper also filter and order embedded columns

This patch adds the ability to filter and order by embedded columns.

To use it you must in JS:

$('datatable_selector').api({datatables_options})

where datatables_options are all datatables options plus:

1. embed: [list of embeddable tables]
   This option adds x-koha-embed header to request.

2. header_filter: true|false
   This option if true sets x-koha-query header with stringyfied json of filters

Oderable and searchable columns must define data option as string, otherwise filter and order won't be possible.
If you must custom the output, use the render function.

For example:

* Don't

> $('.table_selector').api({
>   columns: [
>     {
>       data: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

* Do

> $('.table_selector').api({
>   columns: [
>     {
>       data: 'holds.patron.firstname',
>       render: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

To test you must implement and test bug 20936, where it will be used.
Comment 5 Agustín Moyano 2020-02-19 14:39:43 UTC
I don't know why, but when applying this bug with git bz, bug 20212 doesn't appear as an option. Maybe it's because is in state "ASSIGNED"

To succesfully apply this bug, first apply 20212 manually.
Comment 6 Agustín Moyano 2020-02-27 23:43:03 UTC
Created attachment 99723 [details] [review]
Bug 24561: Datatables api wrapper also filter and order embedded columns

This patch adds the ability to filter and order by embedded columns.

To use it you must in JS:

$('datatable_selector').api({datatables_options})

where datatables_options are all datatables options plus:

1. embed: [list of embeddable tables]
   This option adds x-koha-embed header to request.

2. header_filter: true|false
   This option if true sets x-koha-query header with stringyfied json of filters

Oderable and searchable columns must define data option as string, otherwise filter and order won't be possible.
If you must custom the output, use the render function.

For example:

* Don't

> $('.table_selector').api({
>   columns: [
>     {
>       data: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

* Do

> $('.table_selector').api({
>   columns: [
>     {
>       data: 'holds.patron.firstname',
>       render: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

To test you must implement and test bug 20936, where it will be used.
Comment 7 Agustín Moyano 2020-03-03 15:21:21 UTC
Created attachment 100039 [details] [review]
Bug 24561: Datatables api wrapper also filter and order embedded columns

This patch adds the ability to filter and order by embedded columns.

To use it you must in JS:

$('datatable_selector').api({datatables_options})

where datatables_options are all datatables options plus:

1. embed: [list of embeddable tables]
   This option adds x-koha-embed header to request.

2. header_filter: true|false
   This option if true sets x-koha-query header with stringyfied json of filters

Oderable and searchable columns must define data option as string, otherwise filter and order won't be possible.
If you must custom the output, use the render function.

For example:

* Don't

> $('.table_selector').api({
>   columns: [
>     {
>       data: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

* Do

> $('.table_selector').api({
>   columns: [
>     {
>       data: 'holds.patron.firstname',
>       render: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

To test you must implement and test bug 20936, where it will be used.
Comment 8 Kyle M Hall 2020-03-04 13:57:49 UTC
Created attachment 100126 [details] [review]
Bug 24561: Datatables api wrapper also filter and order embedded columns

This patch adds the ability to filter and order by embedded columns.

To use it you must in JS:

$('datatable_selector').api({datatables_options})

where datatables_options are all datatables options plus:

1. embed: [list of embeddable tables]
   This option adds x-koha-embed header to request.

2. header_filter: true|false
   This option if true sets x-koha-query header with stringyfied json of filters

Oderable and searchable columns must define data option as string, otherwise filter and order won't be possible.
If you must custom the output, use the render function.

For example:

* Don't

> $('.table_selector').api({
>   columns: [
>     {
>       data: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

* Do

> $('.table_selector').api({
>   columns: [
>     {
>       data: 'holds.patron.firstname',
>       render: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

To test you must implement and test bug 20936, where it will be used.

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 9 Agustín Moyano 2020-03-09 17:11:48 UTC
Created attachment 100401 [details] [review]
Bug 24561: Datatables api wrapper also filter and order embedded columns

This patch adds the ability to filter and order by embedded columns.

To use it you must in JS:

$('datatable_selector').api({datatables_options})

where datatables_options are all datatables options plus:

1. embed: [list of embeddable tables]
   This option adds x-koha-embed header to request.

2. header_filter: true|false
   This option if true sets x-koha-query header with stringyfied json of filters

Oderable and searchable columns must define data option as string, otherwise filter and order won't be possible.
If you must custom the output, use the render function.

For example:

* Don't

> $('.table_selector').api({
>   columns: [
>     {
>       data: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

* Do

> $('.table_selector').api({
>   columns: [
>     {
>       data: 'holds.patron.firstname',
>       render: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

To test you must implement and test bug 20936, where it will be used.

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Comment 10 Tomás Cohen Arazi 2020-04-27 03:01:27 UTC
Created attachment 103727 [details] [review]
Bug 24561: Add a Datatables api wrapper that implements filter and order embedded columns

This patch adds the ability to filter and order by embedded columns.

To use it you must in JS:

$('datatable_selector').api({datatables_options})

where datatables_options are all datatables options plus:

1. embed: [list of embeddable tables]
   This option adds x-koha-embed header to request.

2. header_filter: true|false
   This option if true sets x-koha-query header with stringyfied json of filters

Oderable and searchable columns must define data option as string, otherwise filter and order won't be possible.
If you must custom the output, use the render function.

For example:

* Don't

> $('.table_selector').api({
>   columns: [
>     {
>       data: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

* Do

> $('.table_selector').api({
>   columns: [
>     {
>       data: 'holds.patron.firstname',
>       render: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

To test you must implement and test bug 20936, where it will be used.

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 11 Josef Moravec 2020-04-30 08:34:45 UTC
Comment on attachment 103727 [details] [review]
Bug 24561: Add a Datatables api wrapper that implements filter and order embedded columns

Review of attachment 103727 [details] [review]:
-----------------------------------------------------------------

::: koha-tmpl/intranet-tmpl/prog/js/datatables.js
@@ +571,5 @@
> +                                } else {
> +                                    delete options.query_parameters;
> +                                }
> +
> +                                dataSet._match = 'starts_with';

It is inconsistent with current behavior - we do filter on any port of string
Comment 12 Josef Moravec 2020-04-30 08:47:55 UTC
Great job Augustín, I found only one small issue with this, see Comment 11
Comment 13 Agustín Moyano 2020-04-30 14:24:25 UTC
Created attachment 104037 [details] [review]
Bug 24561: (follow-up) Set default matching criteria to 'contains' and add configuration option

This patch adds option.criteria as an option. Possible values are
'contains', 'starts_with', 'ends_with' and 'exact'. 'contains' is the
default value.
Comment 14 Agustín Moyano 2020-04-30 14:38:21 UTC
(In reply to Josef Moravec from comment #12)
> Great job Augustín, I found only one small issue with this, see Comment 11

Thanks for you comments Josef, I just applied your suggestions (I hope)

Please test this patch again.

Cheers
Comment 15 Tomás Cohen Arazi 2020-05-05 21:29:16 UTC
Created attachment 104384 [details] [review]
Bug 24561: (follow-up) Use full_numbers for pagingType

We are trying to keep the current datatables behaviour so this is a
trivial yet sensible change.

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
Comment 16 Jonathan Druart 2020-05-06 14:11:59 UTC
Created attachment 104424 [details] [review]
Bug 24561: Add a Datatables api wrapper that implements filter and order embedded columns

This patch adds the ability to filter and order by embedded columns.

To use it you must in JS:

$('datatable_selector').api({datatables_options})

where datatables_options are all datatables options plus:

1. embed: [list of embeddable tables]
   This option adds x-koha-embed header to request.

2. header_filter: true|false
   This option if true sets x-koha-query header with stringyfied json of filters

Oderable and searchable columns must define data option as string, otherwise filter and order won't be possible.
If you must custom the output, use the render function.

For example:

* Don't

> $('.table_selector').api({
>   columns: [
>     {
>       data: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

* Do

> $('.table_selector').api({
>   columns: [
>     {
>       data: 'holds.patron.firstname',
>       render: function(row, type, val, meta) {
>         return '<a href="'+row.link+'">'+row.holds.patron.firstname+'</a>';
>       },
>       orderable: true,
>       searchable: true
>     }
>   ]
> });

To test you must implement and test bug 20936, where it will be used.

Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 17 Jonathan Druart 2020-05-06 14:12:03 UTC
Created attachment 104425 [details] [review]
Bug 24561: (follow-up) Set default matching criteria to 'contains' and add configuration option

This patch adds option.criteria as an option. Possible values are
'contains', 'starts_with', 'ends_with' and 'exact'. 'contains' is the
default value.

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 18 Jonathan Druart 2020-05-06 14:12:07 UTC
Created attachment 104426 [details] [review]
Bug 24561: (follow-up) Use full_numbers for pagingType

We are trying to keep the current datatables behaviour so this is a
trivial yet sensible change.

Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>

Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org>
Comment 19 Martin Renvoize 2020-05-12 10:47:40 UTC
Nice work everyone!

Pushed to master for 20.05
Comment 20 Joy Nelson 2020-05-13 19:08:04 UTC
missing dependencies - not backported to 19.11.x
Comment 21 Jonathan Druart 2020-08-18 09:13:06 UTC
+jQuery.fn.dataTable.ext.errMode = function(settings, note, message) {
+    console.warn(message);
+};


Has this been added on purpose or for debug?
This is a non advertised change, switched from alert to console.log. I don't think we want that in production (better to display an alert to the end user to help them to report the relevant issue).