[Bug 42587] New: overdues+count is not sortable on the API
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 Bug ID: 42587 Summary: overdues+count is not sortable on the API Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: REST API Assignee: koha-bugs@lists.koha-community.org Reporter: tomascohen@gmail.com QA Contact: testopia@bugs.koha-community.org CC: tomascohen@gmail.com Depends on: 41950 The overdues+count embed on the patrons endpoint cannot be sorted because the overdues DBIC relationship does not exist on the Borrower result class. The Koha::Patron->overdues method currently filters checkouts in Perl (date_due < NOW()). To make it sortable, we need: 1. A DBIC coderef relationship on Borrower that filters issues.date_due < NOW() at the SQL level 2. Teach _build_count_subquery to handle coderef conditions (invoke the coderef, parse the returned hashref into WHERE clauses) 3. Update Koha::Patron->overdues to delegate to the DBIC relationship 4. Unit tests proving the sort works with a mix of overdue and non-overdue checkouts The generated subquery would be: (SELECT COUNT(*) FROM issues WHERE issues.borrowernumber = me.borrowernumber AND issues.date_due < NOW()) Test plan: prove t/Koha/REST/Plugin/Query.t t/db_dependent/api/v1/patrons.t t/db_dependent/Koha/Patron.t Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41950 [Bug 41950] Make +count embeds sortable by using SQL-level COUNT subqueries -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |42588 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42588 [Bug 42588] biblio.uncancelled_orders+count is not sortable on the API -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |42589 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42589 [Bug 42589] bundle_items_lost+count is not sortable on the API -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |42590 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42590 [Bug 42590] bundle_items_not_lost+count is not sortable on the API -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |42591 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42591 [Bug 42591] current_item_level_holds+count is not sortable on the API -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |42592 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42592 [Bug 42592] Flag +count embeds that cannot be sorted -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Small patch Status|NEW |Needs Signoff -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 --- Comment #1 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- Created attachment 199492 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199492&action=edit Bug 42587: Unit tests for Koha::Patron->overdues Adds tests covering the existing overdues() method behavior: - Returns a Koha::Checkouts resultset - Returns empty when no checkouts exist - Does not include checkouts with future due dates - Includes checkouts with past due dates - Correctly counts multiple overdues - Only returns overdues, not all checkouts Test plan: 1. Apply patch 2. Run: $ prove t/db_dependent/Koha/Patron.t => SUCCESS: Tests pass 3. Sign off :-D Assisted-by: Sonnet 4.6 (Anthropic) -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 --- Comment #2 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- Created attachment 199493 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199493&action=edit Bug 42587: Use DBIC coderef relationship for Koha::Patron->overdues Adds a DBIC coderef relationship 'overdues' on Borrower that filters issues.date_due < NOW() at the SQL level. Updates Koha::Patron->overdues to delegate to this relationship instead of filtering in Perl. This is a refactor with no behavior change. The filter logic now lives in a single place (the DBIC relationship), which both the Koha method and the +count subquery infrastructure can use. Test plan: 1. Run tests before applying: $ prove t/db_dependent/Koha/Patron.t => SUCCESS: Tests pass 2. Apply patch 3. Run tests again: $ prove t/db_dependent/Koha/Patron.t => SUCCESS: Tests still pass (no behavior change) 4. Sign off :-D Assisted-by: Sonnet 4.6 (Anthropic) -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 --- Comment #3 from Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> --- Created attachment 199494 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199494&action=edit Bug 42587: Make overdues+count sortable via coderef subquery Extends _build_count_subquery to handle DBIC coderef conditions. When a relationship uses a coderef (like overdues with date_due < NOW()), the function invokes it and parses the returned hashref into SQL WHERE clauses for the COUNT subquery. Supported condition types: - -ident (FK join columns) - Comparison operators (<, >, !=, etc.) with literal SQL or values - undef (IS NULL) - -in with scalar ref (literal SQL subquery) Adds a full-stack API test proving overdues+count sorting works with patrons having different mixes of overdue and non-overdue checkouts. Test plan: 1. Apply patches (requires bug 41950) 2. Run: $ ktd --shell k$ prove t/Koha/REST/Plugin/Query.t \ t/db_dependent/api/v1/patrons.t \ t/db_dependent/Koha/Patron.t => SUCCESS: All tests pass 3. Verify overdues+count sorting: GET /api/v1/patrons x-koha-embed: overdues+count _order_by: -me.overdues_count => SUCCESS: Patrons sorted by overdue count (filtered, not all checkouts) 4. Sign off :-D Assisted-by: Sonnet 4.6 (Anthropic) -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 Tomás Cohen Arazi (tcohen) <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |tomascohen@gmail.com |ity.org | -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 Bug 42587 depends on bug 41950, which changed state. Bug 41950 Summary: Make +count embeds sortable by using SQL-level COUNT subqueries https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41950 What |Removed |Added ---------------------------------------------------------------------------- Status|Needs documenting |RESOLVED Resolution|--- |FIXED -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #199492|0 |1 is obsolete| | Attachment #199493|0 |1 is obsolete| | Attachment #199494|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 --- Comment #4 from David Nind <david@davidnind.com> --- Created attachment 205265 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=205265&action=edit Bug 42587: Unit tests for Koha::Patron->overdues Adds tests covering the existing overdues() method behavior: - Returns a Koha::Checkouts resultset - Returns empty when no checkouts exist - Does not include checkouts with future due dates - Includes checkouts with past due dates - Correctly counts multiple overdues - Only returns overdues, not all checkouts Test plan: 1. Apply patch 2. Run: $ prove t/db_dependent/Koha/Patron.t => SUCCESS: Tests pass 3. Sign off :-D Assisted-by: Sonnet 4.6 (Anthropic) Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 --- Comment #5 from David Nind <david@davidnind.com> --- Created attachment 205266 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=205266&action=edit Bug 42587: Use DBIC coderef relationship for Koha::Patron->overdues Adds a DBIC coderef relationship 'overdues' on Borrower that filters issues.date_due < NOW() at the SQL level. Updates Koha::Patron->overdues to delegate to this relationship instead of filtering in Perl. This is a refactor with no behavior change. The filter logic now lives in a single place (the DBIC relationship), which both the Koha method and the +count subquery infrastructure can use. Test plan: 1. Run tests before applying: $ prove t/db_dependent/Koha/Patron.t => SUCCESS: Tests pass 2. Apply patch 3. Run tests again: $ prove t/db_dependent/Koha/Patron.t => SUCCESS: Tests still pass (no behavior change) 4. Sign off :-D Assisted-by: Sonnet 4.6 (Anthropic) Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 --- Comment #6 from David Nind <david@davidnind.com> --- Created attachment 205267 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=205267&action=edit Bug 42587: Make overdues+count sortable via coderef subquery Extends _build_count_subquery to handle DBIC coderef conditions. When a relationship uses a coderef (like overdues with date_due < NOW()), the function invokes it and parses the returned hashref into SQL WHERE clauses for the COUNT subquery. Supported condition types: - -ident (FK join columns) - Comparison operators (<, >, !=, etc.) with literal SQL or values - undef (IS NULL) - -in with scalar ref (literal SQL subquery) Adds a full-stack API test proving overdues+count sorting works with patrons having different mixes of overdue and non-overdue checkouts. Test plan: 1. Apply patches (requires bug 41950) 2. Run: $ ktd --shell k$ prove t/Koha/REST/Plugin/Query.t \ t/db_dependent/api/v1/patrons.t \ t/db_dependent/Koha/Patron.t => SUCCESS: All tests pass 3. Verify overdues+count sorting: GET /api/v1/patrons x-koha-embed: overdues+count _order_by: -me.overdues_count => SUCCESS: Patrons sorted by overdue count (filtered, not all checkouts) 4. Sign off :-D Assisted-by: Sonnet 4.6 (Anthropic) Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42587 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david@davidnind.com --- Comment #7 from David Nind <david@davidnind.com> --- Hope I tested this correctly! Testing notes (using KTD): 1. Check some items out to different patrons so that items are overdue, and that they have different numbers of overdue items: - Click the "Checkout settings" icon where you enter in the barcode - Change the "Specify due date" to a date in the past - Mary Burton (23529000651225): - 39999000011418 - Lisa Charles (23529000197047): - 39999000000559 - 39999000006155 - Henry Acevedo (23529000035676): - 39999000006094 - 39999000006117 - 39999000000498 - Also add some normal non-overdue checkouts 2. After applying patches, run yarn build and a restart_all (not sure if this is required). 3. Used Bruno to test patch 3, step 3: - Set RESTdefaultPageSize system preference to "100" - GET 27.0.0.1:8081/api/v1/patrons Params: . Name: _order_by . Value: +me.overdues_count Headers: . Name: x-koha-embed . Value: overdues+count Auth: Basic Auth (inherited from Collection) ==> Result = the last 3 entries in response are ordered correctly: Mary Burton: "overdues_count": 1 Lisa Charles: "overdues_count": 2 Henry Acevedo: "overdues_count": 3 - Change _order_by value to -me.overdues_count ==> Results = the first 3 entries in response are ordered correctly: Henry Acevedo: "overdues_count": 3 Lisa Charles: "overdues_count": 2 Mary Burton: "overdues_count": 1 -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org