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.
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>