From d6380cdc40f0039c16e66514af9dbf291d457730 Mon Sep 17 00:00:00 2001 From: Jonathan Druart Date: Mon, 3 Feb 2025 12:20:49 +0100 Subject: [PATCH] Bug 39011: Pre-process coded values when building the query When a column contains authorised/coded values, it is not possible to search on the description. It is confusing for the user as the description is the value displayed in the table. We have discussed this already on bug 38130 and decided to fix the problem by doing a new SQL join. But this can lead to performance issues, especially for tables like the items table where a lot of coded values are displayed. This patch suggests to do a pre-processing step, before the query is sent, to let the client build the query it needs. An alternative approach would be to do the same job server-side, and allow this kind of processing for all the attributes with a object using columns_to_str. But we will certainly reach performance problems. This is still not perfect, but it's fixing a regression quite easily, and will open the door to more fixes for tables using coded values. This patch revert one we have done on bug 38130, and use this new mecanism for home library, holding library, item type and collection code. Signed-off-by: David Nind Signed-off-by: Nick Clemens Signed-off-by: Lucas Gass Signed-off-by: David Nind Signed-off-by: Martin Renvoize --- .../tables/items/catalogue_detail.inc | 20 +++++-- koha-tmpl/intranet-tmpl/prog/js/datatables.js | 54 +++++++++++++++++-- 2 files changed, 67 insertions(+), 7 deletions(-) diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/html_helpers/tables/items/catalogue_detail.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/html_helpers/tables/items/catalogue_detail.inc index 3746af5f3cb..a8a2cb889b5 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/includes/html_helpers/tables/items/catalogue_detail.inc +++ b/koha-tmpl/intranet-tmpl/prog/en/includes/html_helpers/tables/items/catalogue_detail.inc @@ -231,6 +231,13 @@ [% IF Koha.Preference('UseCourseReserves') %] const av_courses_term = new Map([% To.json(AuthorisedValues.Get('TERM')) | $raw %].map( av => [av.authorised_value, av.lib])); [% END %] + + const coded_values = { + library: new Map(all_libraries.map( l => [l.branchname, l.branchcode] )), + item_type: new Map(all_item_types.map( i => [i.translated_description, i.itemtype] )), + collection_code: new Map([% To.json(AuthorisedValues.GetDescriptionsByKohaField({ kohafield => 'items.ccode' })) | $raw %].map( av => [av.lib, av.authorised_value])), + }; + [% IF Koha.Preference('URLLinkText') %] const url_link_text = "[% Koha.Preference('URLLinkText') | html %]"; [% ELSE %] @@ -240,7 +247,7 @@ [%# In case or SeparateHoldings we may need to display the number of biblios in each tab %] [%# Do we need separate/new endpoints or do we hack the somewhere client-side? %] let item_table_url = "/api/v1/biblios/[% biblio.biblionumber | uri %]/items?"; - let embed = ["+strings,home_library,holding_library,checkout,checkout.patron,transfer,transfer+strings,first_hold,first_hold+strings,first_hold.patron,first_hold.desk"]; + let embed = ["+strings,checkout,checkout.patron,transfer,transfer+strings,first_hold,first_hold+strings,first_hold.patron,first_hold.desk"]; [% IF Koha.Preference('LocalCoverImages') %] embed.push('cover_image_ids'); [% END %] @@ -282,6 +289,7 @@ }; function build_items_table (tab_id, add_filters, dt_options, drawcallback) { + let table_dt; if ( dt_options && dt_options.hasOwnProperty('destroy') ) { // Keep a copy of the user settings, the destroy is going to trigger the column-visibility.dt event for all columns let user_colvis_bak= Object.assign({}, user_colvis[tab_id]); @@ -370,6 +378,7 @@ [% IF ( item_level_itypes ) %] { data: "me.item_type_id", // FIXME Cannot filter by biblioitem.itemtype + datatype: "coded_value:item_type", className: "itype", searchable: true, orderable: true, @@ -388,7 +397,8 @@ }, [% END %] { - data: "holding_library.name:me.holding_library_id", + data: "me.holding_library_id", + datatype: "coded_value:library", className: "location", searchable: true, orderable: true, @@ -397,7 +407,8 @@ } }, { - data: "home_library.name:me.home_library_id", + data: "me.home_library_id", + datatype: "coded_value:library", className: "homebranch", searchable: true, orderable: true, @@ -427,6 +438,7 @@ }, { data: "me.collection_code", + datatype: "coded_value:collection_code", searchable: true, orderable: true, render: function (data, type, row, meta) { @@ -882,7 +894,7 @@ filters_options, ); - let table_dt = items_table.DataTable(); + table_dt = items_table.DataTable(); table_dt.on("column-visibility.dt", function(e, settings, column, state, recalc ){ if (recalc === false) return; diff --git a/koha-tmpl/intranet-tmpl/prog/js/datatables.js b/koha-tmpl/intranet-tmpl/prog/js/datatables.js index ec0bb6f0ba1..4e3b4f70f90 100644 --- a/koha-tmpl/intranet-tmpl/prog/js/datatables.js +++ b/koha-tmpl/intranet-tmpl/prog/js/datatables.js @@ -510,6 +510,9 @@ function _dt_default_ajax(params) { var length = data.length; var start = data.start; + let api = new $.fn.dataTable.Api(settings); + const global_search = api.search(); + var dataSet = { _page: Math.floor(start / length) + 1, _per_page: length, @@ -521,6 +524,7 @@ function _dt_default_ajax(params) { for (var i = 0; i < attributes.length; i++) { var part = {}; var attr = attributes[i]; + let default_build = true; let criteria = options.criteria; if (value.match(/^\^(.*)\$$/)) { value = value.replace(/^\^/, "").replace(/\$$/, ""); @@ -539,6 +543,9 @@ function _dt_default_ajax(params) { } if (col.datatype !== undefined) { + default_build = false; + let coded_datatype = + col.datatype.match(/^coded_value:(.*)/); if (col.datatype == "related-object") { let query_term = value; @@ -565,6 +572,48 @@ function _dt_default_ajax(params) { [col.related + "." + col.relatedSearchOn]: query_term, }; + } else if ( + coded_datatype && + coded_datatype.length > 1 + ) { + if (global_search.length) { + coded_datatype = coded_datatype[1]; + const regex = new RegExp( + `^${global_search}`, + "i" + ); + if ( + coded_values && + coded_values.hasOwnProperty(coded_datatype) + ) { + let codes = [ + ...coded_values[ + coded_datatype + ].entries(), + ] + .filter(([label]) => regex.test(label)) + .map(([, code]) => code); + + if (codes.length) { + part[ + !attr.includes(".") + ? "me." + attr + : attr + ] = codes; + } else { + // Coded value not found using the description, fallback to code + default_build = true; + } + } else { + console.log( + "coded datatype %s not supported yet".format( + coded_datatype + ) + ); + } + } else { + default_build = true; + } } else { console.log( "datatype %s not supported yet".format( @@ -573,8 +622,7 @@ function _dt_default_ajax(params) { ); } } - - if (col.datatype != "related-object") { + if (default_build) { let value_part; if (criteria === "exact") { value_part = built_value @@ -604,7 +652,7 @@ function _dt_default_ajax(params) { value_part; } - parts.push(part); + if (Object.keys(part).length) parts.push(part); } return parts; } -- 2.49.0