View | Details | Raw Unified | Return to bug 41950
Collapse All | Expand All

(-)a/Koha/Object.pm (-3 / +10 lines)
Lines 203-209 sub store { Link Here
203
                    value    => $value,
203
                    value    => $value,
204
                    property => $property =~ /(\w+\.\w+)$/
204
                    property => $property =~ /(\w+\.\w+)$/
205
                    ? $1
205
                    ? $1
206
                    : $property,    # results in table.column without quotes or backtics
206
                    : $property,    # results in table.column without quotes or backticks
207
                );
207
                );
208
            } elsif ( $_->{msg} =~ /Data truncated for column \W?(?<property>\w+)/ )
208
            } elsif ( $_->{msg} =~ /Data truncated for column \W?(?<property>\w+)/ )
209
            {                       # The optional \W in the regex might be a quote or backtick
209
            {                       # The optional \W in the regex might be a quote or backtick
Lines 213-219 sub store { Link Here
213
                    type     => 'enum',
213
                    type     => 'enum',
214
                    property => $property =~ /(\w+\.\w+)$/
214
                    property => $property =~ /(\w+\.\w+)$/
215
                    ? $1
215
                    ? $1
216
                    : $property,    # results in table.column without quotes or backtics
216
                    : $property,    # results in table.column without quotes or backticks
217
                    value => $self->$property,
217
                    value => $self->$property,
218
                ) if $type eq 'enum';
218
                ) if $type eq 'enum';
219
            }
219
            }
Lines 680-686 sub to_api { Link Here
680
            {
680
            {
681
681
682
                my $relation = $+{relation};
682
                my $relation = $+{relation};
683
                $json_object->{$embed} = $self->$relation->count;
683
684
                # If the query was built with a SQL-level COUNT subquery (via +select/+as
685
                # in _merge_count_select), the count is already available as a virtual column
686
                # on the DBIC row. Use it to avoid an N+1 query per row.
687
                # Falls back to the Koha relation ->count call for cases where no DBIC
688
                # relationship exists (e.g. a Koha-only method like the old claims_count).
689
                my $count = eval { $self->_result->get_column($embed) };
690
                $json_object->{$embed} = defined $count ? $count : $self->$relation->count;
684
            } else {
691
            } else {
685
                my $curr = $embed;
692
                my $curr = $embed;
686
                my $next = $embeds->{$curr}->{children};
693
                my $next = $embeds->{$curr}->{children};
(-)a/Koha/REST/Plugin/Query.pm (-1 / +81 lines)
Lines 148-154 Generates the DBIC prefetch attribute based on embedded relations, and merges in Link Here
148
            return unless defined $embed;
148
            return unless defined $embed;
149
149
150
            my @prefetches;
150
            my @prefetches;
151
            my $count_aliases = {};
152
151
            foreach my $key ( sort keys( %{$embed} ) ) {
153
            foreach my $key ( sort keys( %{$embed} ) ) {
154
155
                # Handle +count embeds with SQL-level subquery
156
                if ( $embed->{$key}->{is_count} ) {
157
                    my $added = _merge_count_select( $key, $attributes, $result_set );
158
                    if ($added) {
159
                        $count_aliases->{$key} = 1;
160
                        next;
161
                    }
162
163
                    # No DBIC relationship found; fall through so to_api
164
                    # can use the Koha method ->count fallback
165
                }
166
152
                my $parsed = _parse_prefetch( $key, $embed, $result_set );
167
                my $parsed = _parse_prefetch( $key, $embed, $result_set );
153
                push @prefetches, $parsed if defined $parsed;
168
                push @prefetches, $parsed if defined $parsed;
154
            }
169
            }
Lines 156-161 Generates the DBIC prefetch attribute based on embedded relations, and merges in Link Here
156
            if ( scalar(@prefetches) ) {
171
            if ( scalar(@prefetches) ) {
157
                $attributes->{prefetch} = \@prefetches;
172
                $attributes->{prefetch} = \@prefetches;
158
            }
173
            }
174
175
            # Fix order_by entries referencing count aliases (me.X_count => X_count).
176
            # dbic_merge_sorting (which runs before this) translates _order_by=-me.claims_count
177
            # into { -desc => 'me.claims_count' }. But +as aliases are not real table columns,
178
            # so DBIC needs them without the 'me.' prefix to match the +select subquery.
179
            if ( %{$count_aliases} && $attributes->{order_by} ) {
180
                for my $i ( 0 .. $#{ $attributes->{order_by} } ) {
181
                    my $atom = $attributes->{order_by}[$i];
182
                    if ( ref($atom) eq 'HASH' ) {
183
                        for my $dir ( keys %{$atom} ) {
184
                            ( my $col = $atom->{$dir} ) =~ s/^me\.//;
185
                            $atom->{$dir} = $col if $count_aliases->{$col};
186
                        }
187
                    } elsif ( !ref($atom) ) {
188
                        ( my $col = $atom ) =~ s/^me\.//;
189
                        $attributes->{order_by}[$i] = $col if $count_aliases->{$col};
190
                    }
191
                }
192
            }
159
        }
193
        }
160
    );
194
    );
161
195
Lines 449-454 sub _merge_embed { Link Here
449
    }
483
    }
450
}
484
}
451
485
486
=head3 _merge_count_select
487
488
    my $added = _merge_count_select( $key, $attributes, $result_set );
489
490
Given a I<$key> like C<claims_count> (from a C<claims+count> embed), strips the C<_count>
491
suffix to derive the DBIC relationship name, then uses the relationship metadata to build
492
a correlated COUNT subquery added via C<+select>/C<+as>.
493
494
This makes the count available as a virtual column in the SQL result, enabling both
495
server-side sorting (ORDER BY) and an efficient single-query count instead of N+1.
496
497
Returns 1 if the subquery was added, or undef if the relationship was not found in the
498
C<prefetch_whitelist> or has no DBIC relationship info. In the latter case, the caller
499
should fall through to the Koha-level C<< $object->$relation->count >> path in C<to_api>.
500
501
Handles both C<Koha::Objects> (plural, via C<_resultset>) and C<Koha::Object> (singular,
502
via C<_result>) for accessing the DBIC result source.
503
504
=cut
505
506
sub _merge_count_select {
507
    my ( $key, $attributes, $result_set ) = @_;
508
509
    ( my $rel = $key ) =~ s/_count$//;
510
    return unless exists $result_set->prefetch_whitelist->{$rel};
511
512
    my $source =
513
          $result_set->can('_resultset')
514
        ? $result_set->_resultset->result_source
515
        : $result_set->_result->result_source;
516
    my $rel_info = $source->relationship_info($rel);
517
    return unless $rel_info;
518
519
    my $rel_table    = $source->related_source($rel)->name;
520
    my $cond         = $rel_info->{cond};
521
    my ($fk_foreign) = keys %{$cond};
522
    my $fk_self      = $cond->{$fk_foreign};
523
    $fk_foreign =~ s/^foreign\.//;
524
    $fk_self    =~ s/^self\.//;
525
526
    push @{ $attributes->{'+select'} },
527
        \"(SELECT COUNT(*) FROM $rel_table WHERE $rel_table.$fk_foreign = me.$fk_self)";
528
    push @{ $attributes->{'+as'} }, $key;
529
530
    return 1;
531
}
532
452
sub _parse_prefetch {
533
sub _parse_prefetch {
453
    my ( $key, $embed, $result_set ) = @_;
534
    my ( $key, $embed, $result_set ) = @_;
454
535
455
- 

Return to bug 41950