Bug 41950

Summary: Make +count embeds sortable by using SQL-level COUNT subqueries
Product: Koha Reporter: Tomás Cohen Arazi (tcohen) <tomascohen>
Component: REST APIAssignee: 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
Currently, +count embeds (e.g., claims+count, checkouts+count) are computed in Perl via 
$object->$relation->count during to_api serialization. This means:

1. They cannot be used in _order_by — attempting to sort by a _count field (e.g., 
_order_by=-me.claims_count) causes a 500 error because DBIC cannot find the column in the database.
2. Each count triggers a separate SQL query per row (N+1 problem).

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. For example, 
claims+count on orders generates:

sql
(SELECT COUNT(*) FROM aqorders_claims
 WHERE aqorders_claims.ordernumber = me.ordernumber) AS claims_count


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.
Comment 1 Tomás Cohen Arazi (tcohen) 2026-02-27 16:51:37 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>
Comment 2 Tomás Cohen Arazi (tcohen) 2026-02-27 16:51:39 UTC
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>
Comment 3 David Nind 2026-02-28 03:44:47 UTC
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>
Comment 4 David Nind 2026-02-28 03:44:50 UTC
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>