https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=40368
--- Comment #4 from Mark Hofstetter <[email protected]> --- 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 -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes. _______________________________________________ Koha-bugs mailing list [email protected] https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
