[Bug 43623] New: Pagination of JSON Report Exports
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43623 Bug ID: 43623 Summary: Pagination of JSON Report Exports Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Reports Assignee: koha-bugs@lists.koha-community.org Reporter: mathsabypro@gmail.com QA Contact: testopia@bugs.koha-community.org CC: lisette@bywatersolutions.com Target Milestone: --- To avoid the risk of downloading too many rows when exporting a report as JSON, I wanted to try paginating the results by adding a limit and a variable offset (a parameter passed in the URL) to the report. But OFFSET doesn’t seem to be supported in SQL, which surprises me because it isn’t listed among the filtered keywords specified in the community wiki (create, update, etc.). Example: select itemnumber from items LIMIT 50 OFFSET 100 gives me : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 0, 50' at line 2 Is it a bug ? a missing feature? -- 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=43623 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- 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=43623 --- Comment #1 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 206812 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=206812&action=edit Bug 43623: Fix negative LIMIT when report offset is greater than its limit execute_query added the offset of the user supplied LIMIT to the pagination offset before comparing it with the user supplied count. With "LIMIT 100, 50" the computed limit was 50 - 100 = -50, resulting in a SQL syntax error. The pagination offset is relative to the user supplied offset, so compare it with the user supplied count first and add the user offset afterwards. Test plan: 1. Create a SQL report: SELECT itemnumber FROM items LIMIT 100, 50 2. Run it => SQL syntax error near '-50' 3. Apply this patch 4. Run the report again => 50 items are displayed, and the following pages stop at the 50th item 5. prove t/db_dependent/Reports/Guided.t Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com> -- 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=43623 --- Comment #2 from Jonathan Druart <jonathan.druart@gmail.com> --- Created attachment 206813 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=206813&action=edit Bug 43623: Support LIMIT ... OFFSET ... syntax in SQL reports strip_limit only recognized "LIMIT count" and "LIMIT offset, count". A report using "LIMIT count OFFSET offset" kept its OFFSET clause, and execute_query then appended "LIMIT ?, ?" to it, resulting in a SQL syntax error. Test plan: 1. Create a SQL report: SELECT itemnumber FROM items LIMIT 50 OFFSET 100 2. Run it => SQL syntax error near 'LIMIT 0, 50' 3. Apply this patch 4. Run the report again => 50 items are displayed, the same as in the database console 5. prove t/db_dependent/Reports/Guided.t Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com> -- 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=43623 Jonathan Druart <jonathan.druart@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Pagination of JSON Report |LIMIT OFFSET syntax not |Exports |correctly handled in | |reports CC| |jonathan.druart@gmail.com Assignee|koha-bugs@lists.koha-commun |jonathan.druart@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=43623 OpenFifth Sandboxes <sandboxes@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #206813|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=43623 --- Comment #3 from OpenFifth Sandboxes <sandboxes@openfifth.co.uk> --- Created attachment 206884 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=206884&action=edit Bug 43623: Support LIMIT ... OFFSET ... syntax in SQL reports strip_limit only recognized "LIMIT count" and "LIMIT offset, count". A report using "LIMIT count OFFSET offset" kept its OFFSET clause, and execute_query then appended "LIMIT ?, ?" to it, resulting in a SQL syntax error. Test plan: 1. Create a SQL report: SELECT itemnumber FROM items LIMIT 50 OFFSET 100 2. Run it => SQL syntax error near 'LIMIT 0, 50' 3. Apply this patch 4. Run the report again => 50 items are displayed, the same as in the database console 5. prove t/db_dependent/Reports/Guided.t Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com> Signed-off-by: Mathieu Saby <mathsabypro@gmail.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43623 OpenFifth Sandboxes <sandboxes@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #206812|0 |1 is obsolete| | Attachment #206884|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=43623 --- Comment #4 from OpenFifth Sandboxes <sandboxes@openfifth.co.uk> --- Created attachment 206885 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=206885&action=edit Bug 43623: Fix negative LIMIT when report offset is greater than its limit execute_query added the offset of the user supplied LIMIT to the pagination offset before comparing it with the user supplied count. With "LIMIT 100, 50" the computed limit was 50 - 100 = -50, resulting in a SQL syntax error. The pagination offset is relative to the user supplied offset, so compare it with the user supplied count first and add the user offset afterwards. Test plan: 1. Create a SQL report: SELECT itemnumber FROM items LIMIT 100, 50 2. Run it => SQL syntax error near '-50' 3. Apply this patch 4. Run the report again => 50 items are displayed, and the following pages stop at the 50th item 5. prove t/db_dependent/Reports/Guided.t Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com> Signed-off-by: Mathieu Saby <mathsabypro@gmail.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43623 --- Comment #5 from OpenFifth Sandboxes <sandboxes@openfifth.co.uk> --- Created attachment 206886 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=206886&action=edit Bug 43623: Support LIMIT ... OFFSET ... syntax in SQL reports strip_limit only recognized "LIMIT count" and "LIMIT offset, count". A report using "LIMIT count OFFSET offset" kept its OFFSET clause, and execute_query then appended "LIMIT ?, ?" to it, resulting in a SQL syntax error. Test plan: 1. Create a SQL report: SELECT itemnumber FROM items LIMIT 50 OFFSET 100 2. Run it => SQL syntax error near 'LIMIT 0, 50' 3. Apply this patch 4. Run the report again => 50 items are displayed, the same as in the database console 5. prove t/db_dependent/Reports/Guided.t Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com> Signed-off-by: Mathieu Saby <mathsabypro@gmail.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43623 Mathieu Saby <mathsabypro@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off --- Comment #6 from Mathieu Saby <mathsabypro@gmail.com> --- Thank you for the patch! I tested on an sandbox, it works as expected. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org