| Summary: | Make +count embeds sortable by using SQL-level COUNT subqueries | ||
|---|---|---|---|
| Product: | Koha | Reporter: | Tomás Cohen Arazi (tcohen) <tomascohen> |
| Component: | REST API | Assignee: | Tomás Cohen Arazi (tcohen) <tomascohen> |
| Status: | Signed Off --- | QA Contact: | Testopia <testopia> |
| Severity: | enhancement | ||
| Priority: | P5 - low | CC: | andrew, jonathan.druart, nick, tomascohen |
| Version: | Main | ||
| Hardware: | All | ||
| OS: | All | ||
| GIT URL: | Initiative type: | --- | |
| Sponsorship status: | --- | Comma delimited list of Sponsors: | |
| Crowdfunding goal: | 0 | Patch complexity: | Small patch |
| Documentation contact: | Documentation submission: | ||
| Text to go in the release notes: | Version(s) released in: | ||
| Circulation function: | |||
| Attachments: |
Bug 41950: Unit tests for sortable +count embeds
Bug 41950: Make +count embeds sortable via SQL COUNT subqueries Bug 41950: Unit tests for sortable +count embeds Bug 41950: Make +count embeds sortable via SQL COUNT subqueries |
||
|
Description
Tomás Cohen Arazi (tcohen)
2026-02-27 14:47:15 UTC
Created attachment 194107 [details] [review] Bug 41950: Unit tests for sortable +count embeds Add tests to verify that +count embeds produce +select/+as attributes instead of a prefetch, enabling SQL-level sorting by count columns. Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> Created attachment 194108 [details] [review] Bug 41950: Make +count embeds sortable via SQL COUNT subqueries Currently, +count embeds (e.g. claims+count) are computed in Perl via $object->$relation->count during to_api serialization. This means they cannot be used in _order_by and each count triggers a separate SQL query per row (N+1). This patch makes dbic_merge_prefetch detect +count embeds and, when a matching DBIC relationship exists, inject a correlated COUNT subquery via +select/+as instead of adding a prefetch. The order_by entries referencing count aliases are fixed up to strip the me. prefix, since +as aliases are virtual columns not bound to a table. In to_api, the is_count handler now checks for the pre-computed column via get_column before falling back to the Perl-level ->count call. This preserves backward compatibility for Koha-level methods that don't have a corresponding DBIC relationship. To test: 1. Run: $ prove t/Koha/REST/Plugin/Query.t => SUCCESS 2. Run: $ prove t/db_dependent/Koha/Object.t => SUCCESS 3. Run: $ prove t/db_dependent/Koha/REST/Plugin/Objects.t => SUCCESS 4. Run: $ prove -r t/db_dependent/api/v1 => SUCCESS Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> Created attachment 194191 [details] [review] Bug 41950: Unit tests for sortable +count embeds Add tests to verify that +count embeds produce +select/+as attributes instead of a prefetch, enabling SQL-level sorting by count columns. Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> Signed-off-by: David Nind <david@davidnind.com> Created attachment 194192 [details] [review] Bug 41950: Make +count embeds sortable via SQL COUNT subqueries Currently, +count embeds (e.g. claims+count) are computed in Perl via $object->$relation->count during to_api serialization. This means they cannot be used in _order_by and each count triggers a separate SQL query per row (N+1). This patch makes dbic_merge_prefetch detect +count embeds and, when a matching DBIC relationship exists, inject a correlated COUNT subquery via +select/+as instead of adding a prefetch. The order_by entries referencing count aliases are fixed up to strip the me. prefix, since +as aliases are virtual columns not bound to a table. In to_api, the is_count handler now checks for the pre-computed column via get_column before falling back to the Perl-level ->count call. This preserves backward compatibility for Koha-level methods that don't have a corresponding DBIC relationship. To test: 1. Run: $ prove t/Koha/REST/Plugin/Query.t => SUCCESS 2. Run: $ prove t/db_dependent/Koha/Object.t => SUCCESS 3. Run: $ prove t/db_dependent/Koha/REST/Plugin/Objects.t => SUCCESS 4. Run: $ prove -r t/db_dependent/api/v1 => SUCCESS Signed-off-by: Tomás Cohen Arazi <tomascohen@theke.io> Signed-off-by: David Nind <david@davidnind.com> |