Bugzilla – Attachment 154331 Details for
Bug 33554
Searching borrowers is a lot slower if there's searchable extended attributes
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 33554: [WIP] Add a new lookup endpoint for patron searching
Bug-33554-WIP-Add-a-new-lookup-endpoint-for-patron.patch (text/plain), 13.14 KB, created by
Nick Clemens (kidclamp)
on 2023-08-09 13:59:16 UTC
(
hide
)
Description:
Bug 33554: [WIP] Add a new lookup endpoint for patron searching
Filename:
MIME Type:
Creator:
Nick Clemens (kidclamp)
Created:
2023-08-09 13:59:16 UTC
Size:
13.14 KB
patch
obsolete
>From 3370d2278351024446272e4a84a46f81dbe711fe Mon Sep 17 00:00:00 2001 >From: Nick Clemens <nick@bywatersolutions.com> >Date: Wed, 9 Aug 2023 13:52:44 +0000 >Subject: [PATCH] Bug 33554: [WIP] Add a new lookup endpoint for patron > searching > >This patch adds a new API lookup optoin for patrons. This endpoint takes >three params: >search_type - contains or starts with >search_term - the string passed to search >search_field - a comma separated string of fields to search > >The patron-search code is altered to pass these additional params, and the datatables >code is updated to pass these params through > >The lookup code parses the attributes search directly, then adds it as a subquery to the >results > >This should only affect patron/checkout searches - other patron searches are not changed at this time > >NOTE: In testing I noted that a search for a patron field plus an attribute returns nothing, but >this is the same as existing code. >i.e. patron Kenneth ABRAMS with SCHOOL attribute 'Oxford' is not returned for 'ken oxf' search > >WIP: >Needs test coverage > >TO test: >1 - Add the sample borrowers and attributes to your DB >2 - Perform some patron searches 'ken' 'ken abr' 'oxford' 'ken oxford' >3 - Note the response times, most are slow >4 - Apply patch, restart all >5 - Repeat searches >6 - Note faster response times, but same results >--- > Koha/Patrons.pm | 60 ++++++++++++++ > Koha/REST/V1/Patrons.pm | 47 +++++++++++ > api/v1/swagger/paths/patrons.yaml | 81 +++++++++++++++++++ > api/v1/swagger/swagger.yaml | 2 + > .../prog/en/includes/patron-search.inc | 21 +++-- > koha-tmpl/intranet-tmpl/prog/js/datatables.js | 7 +- > 6 files changed, 210 insertions(+), 8 deletions(-) > >diff --git a/Koha/Patrons.pm b/Koha/Patrons.pm >index c210f53f4b..1aecaddfb4 100644 >--- a/Koha/Patrons.pm >+++ b/Koha/Patrons.pm >@@ -41,6 +41,66 @@ Koha::Patron - Koha Patron Object class > > =cut > >+ >+=head3 lookup >+ >+my $patrons = Koha::Patrons->lookup({ >+ search_term => $search_term, >+ search_field => $search_field, >+ search_type => $search_type >+}); >+ >+Parses a lookup request into a result set >+ >+=cut >+ >+sub lookup { >+ my ( $self, $params ) = @_; >+ >+ my $search_term = $params->{search_term}; >+ my $search_field = $params->{search_field}; >+ my $search_type = $params->{search_type}; >+ >+ my @terms = split( /\s/, $search_term ); >+ my @search_fields = split( /,/, $search_field ); >+ my $leading_wildcard = $search_type eq 'contains' ? '%' : ''; >+ >+ my $query = {}; >+ my @term_query; >+ foreach my $term (@terms) { >+ my @field_query = (); >+ foreach my $field (@search_fields) { >+ push @field_query, { "me." . $field => { like => $leading_wildcard . $term . '%' } }; >+ } >+ push @term_query, { -or => \@field_query }; >+ } >+ push @{ $query->{-or} }, { -and => \@term_query }; >+ >+ my $attributes_rs; >+ if ( C4::Context->preference("ExtendedPatronAttributes") ) { >+ my @attributes_query = (); >+ my $search_attribute_types = Koha::Patron::Attribute::Types->search( { staff_searchable => 1 } ); >+ foreach my $term (@terms) { >+ my @attributes_subquery = (); >+ while ( my $attribute = $search_attribute_types->next ) { >+ push @attributes_subquery, { >+ "me.code" => $attribute->code, >+ "me.attribute" => { like => $leading_wildcard . $term . '%' } >+ }; >+ } >+ push @attributes_query, \@attributes_subquery; >+ >+ } >+ $attributes_rs = Koha::Patron::Attributes->search( { -and => \@attributes_query } ); >+ } >+ >+ push @{ $query->{-or} }, { 'me.borrowernumber' => { -in => [ $attributes_rs->get_column('borrowernumber') ] } }; >+ >+ my $found = $self->search($query); >+ return $found; >+ >+} >+ > =head3 search_limited > > my $patrons = Koha::Patrons->search_limit( $params, $attributes ); >diff --git a/Koha/REST/V1/Patrons.pm b/Koha/REST/V1/Patrons.pm >index 0e04199b55..d20d7f676e 100644 >--- a/Koha/REST/V1/Patrons.pm >+++ b/Koha/REST/V1/Patrons.pm >@@ -65,6 +65,53 @@ sub list { > }; > } > >+ >+=head3 lookup >+ >+Controller function that handles searching for KOha patrons >+ >+=cut >+ >+sub lookup { >+ my $c = shift->openapi->valid_input or return; >+ >+ return try { >+ >+ my $query = {}; >+ >+ my $search_term = $c->param('search_term'); >+ my $search_field = $c->param('search_field'); >+ my $search_type = $c->param('search_type'); >+ $c->req->params->remove('search_term'); >+ $c->req->params->remove('search_field'); >+ $c->req->params->remove('search_type'); >+ $c->req->params->remove('q'); >+ >+ my $patrons = Koha::Patrons->lookup( >+ { >+ search_term => $search_term, >+ search_field => $search_field, >+ search_type => $search_type >+ } >+ ); >+ >+ my $restricted = $c->param('restricted'); >+ $c->req->params->remove('restricted'); >+ $query->{debarred} = { '!=' => undef } >+ if $restricted; >+ >+ my $patrons_rs = $patrons->search($query); >+ $patrons = $c->objects->search($patrons_rs); >+ >+ return $c->render( >+ status => 200, >+ openapi => $patrons >+ ); >+ } catch { >+ $c->unhandled_exception($_); >+ }; >+} >+ > =head3 get > > Controller function that handles retrieving a single Koha::Patron object >diff --git a/api/v1/swagger/paths/patrons.yaml b/api/v1/swagger/paths/patrons.yaml >index accd6651ca..7bfb7a0bb2 100644 >--- a/api/v1/swagger/paths/patrons.yaml >+++ b/api/v1/swagger/paths/patrons.yaml >@@ -449,6 +449,87 @@ > x-koha-authorization: > permissions: > borrowers: edit_borrowers >+"/patrons/lookup": >+ get: >+ x-mojo-to: Patrons#lookup >+ operationId: lookupPatrons >+ tags: >+ - patrons >+ summary: Lookup patrons >+ produces: >+ - application/json >+ parameters: >+ - name: search_term >+ in: query >+ description: Term to use in searech >+ required: false >+ type: string >+ - name: search_field >+ in: query >+ description: Field in the patron record to search >+ required: false >+ type: string >+ - name: search_type >+ in: query >+ description: How to search terms, contains or starts with >+ required: false >+ type: string >+ enum: >+ - contains >+ - starts_with >+ - $ref: "../swagger.yaml#/parameters/match" >+ - $ref: "../swagger.yaml#/parameters/order_by" >+ - $ref: "../swagger.yaml#/parameters/page" >+ - $ref: "../swagger.yaml#/parameters/per_page" >+ - $ref: "../swagger.yaml#/parameters/q_param" >+ - $ref: "../swagger.yaml#/parameters/q_body" >+ - $ref: "../swagger.yaml#/parameters/request_id_header" >+ - name: x-koha-embed >+ in: header >+ required: false >+ description: Embed list sent as a request header >+ type: array >+ items: >+ type: string >+ enum: >+ - extended_attributes >+ - checkouts+count >+ - overdues+count >+ - account_balance >+ - library >+ collectionFormat: csv >+ responses: >+ "200": >+ description: A list of patrons >+ schema: >+ type: array >+ items: >+ $ref: "../swagger.yaml#/definitions/patron" >+ "401": >+ description: Authentication required >+ schema: >+ $ref: "../swagger.yaml#/definitions/error" >+ "403": >+ description: Access forbidden >+ schema: >+ $ref: "../swagger.yaml#/definitions/error" >+ "500": >+ description: | >+ Internal server error. Possible `error_code` attribute values: >+ >+ * `internal_server_error` >+ schema: >+ $ref: "../swagger.yaml#/definitions/error" >+ "503": >+ description: Under maintenance >+ schema: >+ $ref: "../swagger.yaml#/definitions/error" >+ x-koha-authorization: >+ permissions: >+ - borrowers: "edit_borrowers" >+ - tools: "label_creator" >+ - serials: "routing" >+ - acquisition: "order_manage" > "/patrons/{patron_id}": > get: > x-mojo-to: Patrons#get >diff --git a/api/v1/swagger/swagger.yaml b/api/v1/swagger/swagger.yaml >index 48d8b5af62..fc50442694 100644 >--- a/api/v1/swagger/swagger.yaml >+++ b/api/v1/swagger/swagger.yaml >@@ -299,6 +299,8 @@ paths: > $ref: ./paths/oauth.yaml#/~1oauth~1token > /patrons: > $ref: ./paths/patrons.yaml#/~1patrons >+ "/patrons/lookup": >+ $ref: ./paths/patrons.yaml#/~1patrons~1lookup > "/patrons/{patron_id}": > $ref: "./paths/patrons.yaml#/~1patrons~1{patron_id}" > "/patrons/{patron_id}/account": >diff --git a/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc b/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc >index 8f13fa0374..7471d79262 100644 >--- a/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc >+++ b/koha-tmpl/intranet-tmpl/prog/en/includes/patron-search.inc >@@ -278,7 +278,8 @@ > [% CASE 'erm_users' %] > let patron_search_url = '/api/v1/erm/users'; > [% CASE %] >- let patron_search_url = '/api/v1/patrons'; >+ let patron_search_url = '/api/v1/patrons/lookup'; >+ let lookup = true; > [% END %] > $(document).ready(function(){ > >@@ -309,6 +310,18 @@ > } > }); > >+ let search_type = $("#searchtype_filter").val() || "contains"; >+ let search_fields = $("#searchfieldstype_filter").val(); >+ let pattern = $("#search_patron_filter").val(); >+ let additional_data = {}; >+ if( lookup ){ >+ additional_data = { >+ "search_type": search_type, >+ "search_field": search_fields, >+ "search_term": pattern >+ }; >+ } >+ > let additional_filters = { > surname: function(){ > let start_with = $("#firstletter_filter").val() >@@ -318,10 +331,6 @@ > "-and": function(){ > let filters = []; > >- let search_type = $("#searchtype_filter").val() || "contains"; >- let search_fields = $("#searchfieldstype_filter").val(); >- let pattern = $("#search_patron_filter").val(); >- > filters = buildPatronSearchQuery( > pattern, > { >@@ -659,7 +668,7 @@ > }); > }, > [% END %] >- }, typeof table_settings !== 'undefined' ? table_settings : null, 1, additional_filters); >+ }, typeof table_settings !== 'undefined' ? table_settings : null, 1, additional_filters, additional_data); > > $("#patron_search_form").on('submit', filter); > $("#patron_search_form").on('submit', update_search_type); >diff --git a/koha-tmpl/intranet-tmpl/prog/js/datatables.js b/koha-tmpl/intranet-tmpl/prog/js/datatables.js >index fe4fe8de97..328ca6b90b 100644 >--- a/koha-tmpl/intranet-tmpl/prog/js/datatables.js >+++ b/koha-tmpl/intranet-tmpl/prog/js/datatables.js >@@ -507,6 +507,7 @@ jQuery.fn.dataTable.ext.errMode = function(settings, note, message) { > function _dt_default_ajax (params){ > let default_filters = params.default_filters; > let options = params.options; >+ let additional_data = params.additional_data; > > if(!options.criteria || ['contains', 'starts_with', 'ends_with', 'exact'].indexOf(options.criteria.toLowerCase()) === -1) options.criteria = 'contains'; > options.criteria = options.criteria.toLowerCase(); >@@ -692,6 +693,7 @@ function _dt_default_ajax (params){ > }); > dataSet._order_by = orderArray.filter((v, i, a) => a.indexOf(v) === i).join(','); > } >+ dataSet = { ...dataSet, ...additional_data }; > > return dataSet; > } >@@ -938,9 +940,10 @@ function _dt_add_filters(table_node, table_dt, filters_options = {}) { > * available from the columns_settings template toolkit include > * @param {Boolean} add_filters Add a filters row as the top row of the table > * @param {Object} default_filters Add a set of default search filters to apply at table initialisation >+ * @param {Object} additional_data Add additional parameters to the ajax data > * @return {Object} The dataTables instance > */ >- $.fn.kohaTable = function(options, table_settings, add_filters, default_filters) { >+ $.fn.kohaTable = function(options, table_settings, add_filters, default_filters, additional_data) { > var settings = null; > > if(options) { >@@ -963,7 +966,7 @@ function _dt_add_filters(table_node, table_dt, filters_options = {}) { > 'language': { > 'emptyTable': (options.emptyTable) ? options.emptyTable : __("No data available in table") > }, >- 'ajax': _dt_default_ajax({default_filters, options}), >+ 'ajax': _dt_default_ajax({default_filters, options,additional_data}), > }, options); > } > >-- >2.30.2
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 33554
: 154331