Bug 33514 - SearchWithIS[B|S]NVariations breaks ISBN search in item search
Summary: SearchWithIS[B|S]NVariations breaks ISBN search in item search
Status: NEW
Alias: None
Product: Koha
Classification: Unclassified
Component: Searching (show other bugs)
Version: Main
Hardware: All All
: P5 - low normal
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on: 23919
Blocks:
  Show dependency treegraph
 
Reported: 2023-04-13 07:57 UTC by Katrin Fischer
Modified: 2023-06-17 01:16 UTC (History)
6 users (show)

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


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Katrin Fischer 2023-04-13 07:57:11 UTC
Item search is SQL based, not search engine based. When bug 23919 added SearchWithISSNVariations to item search, it broke all searches, but for one use case (that was tested): single ISBN, no ISBD punctuation. 

We are exploding the search now with up to 4 forms of ISBN into something like:

isbn like 'isbn1' or isbn like 'isbn2' ...

But: We are not truncating. 

As soon as there is more than one ISBN (this is a repeatable field) or the library actually follows MARC21 standard adding ISBD punctuation, this fails. The data in biblioitems.isbn looks like this:

isbn1 ; | isbn2 ; | isbn3.
isbn1.

This also seems to create quite a performance issue on big databases and it can't be turned off separately from the normal search.

I am not sure what is best, we could do something like this, which will make the search work again, but I think we also should make it a separate switch:

@@ -1047,7 +1047,12 @@ sub _SearchItems_build_where_fragment {
                  if ( 
C4::Context->preference("SearchWithISBNVariations") and $query ) {
                      my @isbns = C4::Koha::GetVariationsOfISBN( $query );
                      $query = [];
+                    foreach my $isbn(@isbns) {
+                        $isbn = "%" . $isbn . "%";
+                    }
Comment 1 Katrin Fischer 2023-04-13 07:58:54 UTC
I should also mention that any truncation entered in the item search is lost when it's run through the ISBN conversion. So there is no workaround.
Comment 2 Katrin Fischer 2023-05-14 15:38:29 UTC
I am not sure what the best solution here is:

1) Remove the ISBN handling from item search.
2) Fix it to search for ISBN left and right truncated (%isbn%) 
   a) create a separate system preference to turn it on/off 
   b) leave it at one system preference as it is now that controls catalog and     
      item search

Note: the performance issues I mentioned earlier have been fixed by giving the databaes more resources.

2a?
Comment 3 Tomás Cohen Arazi 2023-05-24 11:38:18 UTC
(In reply to Katrin Fischer from comment #2)
> I am not sure what the best solution here is:

What about making sure that normalized strings are always stored on the DB? (i.e. no spaces, no symbols). It feels like GetVariationsOfISBN might be too much in my opinion.
Comment 4 Katrin Fischer 2023-05-26 07:48:36 UTC
(In reply to Tomás Cohen Arazi from comment #3)
> (In reply to Katrin Fischer from comment #2)
> > I am not sure what the best solution here is:
> 
> What about making sure that normalized strings are always stored on the DB?
> (i.e. no spaces, no symbols). It feels like GetVariationsOfISBN might be too
> much in my opinion.

I think that's not the main issue here, sorting differently would not resolve hte issue. We need to fix the search query. The problem is that we store a string of space separated ISBN and you can't search that with SQL without truncating/using LIKE. And that's what happens now and only works when there is only one ISBN on the record (which often is not the case at least for us)
Comment 5 Katrin Fischer 2023-05-27 21:54:48 UTC
sorting => storing

> I think that's not the main issue here, storing differently would not
> resolve hte issue. We need to fix the search query. The problem is that we
> store a string of space separated ISBN and you can't search that with SQL
> without truncating/using LIKE. And that's what happens now and only works
> when there is only one ISBN on the record (which often is not the case at
> least for us)