Bug 41950 - Make +count embeds sortable by using SQL-level COUNT subqueries
Summary: Make +count embeds sortable by using SQL-level COUNT subqueries
Status: Signed Off
Alias: None
Product: Koha
Classification: Unclassified
Component: REST API (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement
Assignee: Tomás Cohen Arazi (tcohen)
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2026-02-27 14:47 UTC by Tomás Cohen Arazi (tcohen)
Modified: 2026-02-28 03:44 UTC (History)
4 users (show)

See Also:
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 (1.37 KB, patch)
2026-02-27 16:51 UTC, Tomás Cohen Arazi (tcohen)
Details | Diff | Splinter Review
Bug 41950: Make +count embeds sortable via SQL COUNT subqueries (7.88 KB, patch)
2026-02-27 16:51 UTC, Tomás Cohen Arazi (tcohen)
Details | Diff | Splinter Review
Bug 41950: Unit tests for sortable +count embeds (1.40 KB, patch)
2026-02-28 03:44 UTC, David Nind
Details | Diff | Splinter Review
Bug 41950: Make +count embeds sortable via SQL COUNT subqueries (7.91 KB, patch)
2026-02-28 03:44 UTC, David Nind
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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>