From 801992af836e6278eaf29359f8e67b6d9fb96ce7 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 --- .../tables/items/catalogue_detail.inc | 20 +++++++++--- koha-tmpl/intranet-tmpl/prog/js/datatables.js | 32 +++++++++++++++++-- 2 files changed, 45 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 c4cff843c00..c06a0d6cec8 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 @@ -227,6 +227,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 %] @@ -236,7 +243,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 %] @@ -278,6 +285,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]); @@ -366,6 +374,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, @@ -384,7 +393,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, @@ -393,7 +403,8 @@ } }, { - data: "home_library.name:me.home_library_id", + data: "me.home_library_id", + datatype: "coded_value:library", className: "homebranch", searchable: true, orderable: true, @@ -416,6 +427,7 @@ }, { data: "me.collection_code", + datatype: "coded_value:collection_code", searchable: true, orderable: true, render: function (data, type, row, meta) { @@ -871,7 +883,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 084cb8c82c8..8448800be42 100644 --- a/koha-tmpl/intranet-tmpl/prog/js/datatables.js +++ b/koha-tmpl/intranet-tmpl/prog/js/datatables.js @@ -444,6 +444,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 @@ -456,6 +459,7 @@ function _dt_default_ajax (params){ for (var i=0;i 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(col.datatype)); } } - - if (col.datatype != 'related-object') { + if ( default_build ) { let value_part; if ( criteria === 'exact' ) { value_part = built_value ? [value, built_value] : value @@ -502,7 +528,7 @@ function _dt_default_ajax (params){ part[!attr.includes('.')?'me.'+attr:attr] = value_part; } - parts.push(part); + if (Object.keys(part).length) parts.push(part); } return parts; } -- 2.39.5