Bug 40368 - Predictive patron search does not handle very similar first and last names
Summary: Predictive patron search does not handle very similar first and last names
Status: REOPENED
Alias: None
Product: Koha
Classification: Unclassified
Component: Patrons (show other bugs)
Version: Main
Hardware: All All
: P5 - low trivial
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2025-07-14 19:48 UTC by Kristi Krueger
Modified: 2025-12-03 21:58 UTC (History)
6 users (show)

See Also:
GIT URL:
Initiative type: ---
Sponsorship status: ---
Comma delimited list of Sponsors:
Crowdfunding goal: 0
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:


Attachments
example of DBIx search query (2.65 KB, application/x-perl)
2025-11-29 21:59 UTC, Mark Hofstetter
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Kristi Krueger 2025-07-14 19:48:20 UTC
When using predictive patron search, patrons that have similar first and last names do not get predicted or appear at the top of the search results. 

To recreate: Create a handful of patrons with the last name Johnson. Create a patron named John Johnson. 

Search for Johnson, John. You will notice the patron does not appear in predictive search, it will not redirect to that patron's account page, and their name/entry will not appear in the top of the search results. 

This makes it difficult to find these patrons.
Comment 1 Andrew Fuerste-Henry 2025-08-06 14:02:00 UTC

*** This bug has been marked as a duplicate of bug 36226 ***
Comment 2 Andrew Fuerste-Henry 2025-08-06 14:31:55 UTC
I've immediately flip-flopped on this. On the one hand, this issue is very related to bug 36226 and only becomes noticeable when one has a lot of patrons. But on the other hand the specific issue Kristi describes is also one of how searches are parsed.

A search for "johnson, john" is treated just like a search for "johnson john." In both cases, Koha will return every patron with "johnson" in either the firstname the surname and "john" in either the firstname or the surname. The way this search is performed, every name that contains "johnson" also contains "john."

We could address this issue by allowing a search that somehow specifies which terms and firstname and which terms are surname. That could be separate search fields or a documented special search syntax -- like "johnson, john" could be parsed as "surname: johnson, firstname: john"
Comment 3 Mark Hofstetter 2025-11-29 21:59:03 UTC
Created attachment 190042 [details]
example of DBIx search query
Comment 4 Mark Hofstetter 2025-11-29 22:07:45 UTC
After discussing this topic with kidclam (Nick) at kohaconn25 I investigated the issue and it seems to be a little bit messy:

the DBIx search query is generated in javascript(!), and not correct/performing at all (and maybe even a minor security issue) - it really took me some time to find it:

koha-tmpl/intranet-tmpl/prog/js/staff-global.js
function buildPatronSearchQuery(term, options) 

which creates a GET(!) uri encoded json 

http://kohadev.ktd.devel:8081/api/v1/patrons?_page=1&_per_page=20&q=%5B%7B%22-and%22%3A%5B%5B%7B%22me.firstname%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%2C%7B%22me.preferred_name%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%2C%7B%22me.middle_name%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%2C%7B%22me.surname%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%2C%7B%22me.othernames%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%2C%7B%22me.cardnumber%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%2C%7B%22me.userid%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%5D%5D%7D%2C%7B%22-or%22%3A%5B%7B%22me.firstname%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%2C%7B%22me.preferred_name%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%2C%7B%22me.middle_name%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%2C%7B%22me.surname%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%2C%7B%22me.othernames%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%2C%7B%22me.cardnumber%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%2C%7B%22me.userid%22%3A%7B%22like%22%3A%22john%25%22%7D%7D%5D%7D%2C%7B%22-and%22%3A%5B%5B%7B%22extended_attributes.value%22%3A%7B%22like%22%3A%22john%25%22%7D%2C%22extended_attributes.code%22%3A%5B%5B%22CODE%22%2C%22GRADE%22%2C%22INTERNET%22%2C%22SCHOOLID%22%2C%22SHOW_BCODE%22%5D%5D%7D%5D%5D%7D%5D&_match=contains&_order_by=%2Bme.surname%2C%2Bme.preferred_name%2C%2Bme.firstname%2C%2Bme.middle_name%2C%2Bme.othernames%2C%2Bme.street_number%2C%2Bme.address%2C%2Bme.address2%2C%2Bme.city%2C%2Bme.state%2C%2Bme.postal_code%2C%2Bme.country

where leads to SQL (abbr.) along the lines of

    FROM borrowers me
    LEFT JOIN borrower_attributes extended_attributes
        ON extended_attributes.borrowernumber = me.borrowernumber
    JOIN branches library
        ON library.branchcode = me.branchcode
    WHERE (
        (
              me.firstname      LIKE 'john%'
           OR me.preferred_name LIKE 'john%'
           OR me.middle_name    LIKE  'john%'
           OR me.surname        LIKE  'john%'
           OR me.othernames     LIKE  'john%'
           OR me.cardnumber     LIKE  'john%'
           OR me.userid         LIKE  'john%'
        )
        OR (
              me.firstname      LIKE  'john%'
           OR me.preferred_name LIKE  'john%'
           OR me.middle_name    LIKE  'john%'
           OR me.surname        LIKE  'john%'
           OR me.othernames     LIKE  'john%'
           OR me.cardnumber     LIKE  'john%'
           OR me.userid         LIKE  'john%'
        )
        OR (
               extended_attributes.attribute LIKE 'john%'
           AND (
                  extended_attributes.code = 'CODE'
               OR extended_attributes.code = 'GRADE'
               OR extended_attributes.code = 'INTERNET'
               OR extended_attributes.code = 'SCHOOLID'
               OR extended_attributes.code = 'SHOW_BCODE'
           )
        )
    )
    GROUP BY
        me.borrowernumber,
        me.cardnumber,
        me.surname,
        me.firstname,
        me.branchcode
    ORDER BY
        me.surname     ASC,
        me.firstname   ASC,
        me.cardnumber  ASC
) me
LEFT JOIN borrower_attributes extended_attributes
    ON extended_attributes.borrowernumber = me.borrowernumber
JOIN branches library
    ON library.branchcode = me.branchcode
WHERE (
    (
          me.firstname      LIKE  'john%'
       OR me.preferred_name LIKE  'john%'
       OR me.middle_name    LIKE  'john%'
       OR me.surname        LIKE  'john%'
       OR me.othernames     LIKE  'john%'
       OR me.cardnumber     LIKE  'john%'
       OR me.userid         LIKE  'john%'
    )
    OR (
          me.firstname      LIKE  'john%'
       OR me.preferred_name LIKE  'john%'
       OR me.middle_name    LIKE  'john%'
       OR me.surname        LIKE  'john%'
       OR me.othernames     LIKE  'john%'
       OR me.cardnumber     LIKE  'john%'
       OR me.userid         LIKE  'john%'
    )
    OR (
           extended_attributes.attribute LIKE 'john%'
       AND (
                 extended_attributes.code = 'CODE'
               OR extended_attributes.code = 'GRADE'
               OR extended_attributes.code = 'INTERNET'
               OR extended_attributes.code = 'SCHOOLID'
               OR extended_attributes.code = 'SHOW_BCODE'
	   )
    )
)
ORDER BY
    me.surname     ASC,
    me.firstname   ASC,
    me.cardnumber  ASC;


===

which doesn't perform well with several 10k of patrons

possible solutions:

1. "band aid fix", change the created json in the js to POST and provide only the search fields and search method (starting with, containing, ...), additional attributes etc. Build the DBIx query in Koha/Patrons.pm in a hand crafted method,   (see attached file)

2. do it "properly" with datatables API search 
https://datatables.net/manual/search
https://datatables.net/manual/server-side

which imho would be the far better solution because it would use a properly defined return format for all searches using datatables,

At the moment I/we can't allocate the resource to this (and our customers don't have problems in this area) but I would be glad to help or do if sponsored