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/Exceptions.pm (+8 lines)
Lines 73-78 sub register { Link Here
73
                        error_code => 'invalid_query',
73
                        error_code => 'invalid_query',
74
                    }
74
                    }
75
                );
75
                );
76
            } elsif ( blessed $exception && ref($exception) eq 'Koha::Exceptions::BadParameter' ) {
77
                return $c->render(
78
                    status => 400,
79
                    json   => {
80
                        error      => "$exception",
81
                        error_code => 'bad_parameter',
82
                    }
83
                );
76
            } elsif ( blessed $exception
84
            } elsif ( blessed $exception
77
                && ref($exception) eq 'Koha::Exceptions::REST::Public::Authentication::Required' )
85
                && ref($exception) eq 'Koha::Exceptions::REST::Public::Authentication::Required' )
78
            {
86
            {
(-)a/Koha/REST/Plugin/Query.pm (-4 / +145 lines)
Lines 148-161 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} ) ) {
152
                my $parsed = _parse_prefetch( $key, $embed, $result_set );
154
155
                # Handle +count embeds with SQL-level subquery
156
                if ( $embed->{$key}->{is_count} ) {
157
                    my $subquery = _merge_count_select( $key, $attributes, $result_set );
158
                    if ($subquery) {
159
                        $count_aliases->{$key} = $subquery;
160
                        next;
161
                    }
162
163
                    # No DBIC relationship found; fall through so to_api
164
                    # can use the Koha method ->count fallback
165
                }
166
167
                my $parsed = _parse_prefetch( $key, $embed, $result_set, $attributes, undef, $count_aliases );
153
                push @prefetches, $parsed if defined $parsed;
168
                push @prefetches, $parsed if defined $parsed;
154
            }
169
            }
155
170
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 ( $attributes->{order_by} ) {
180
181
                # Collect is_count embeds that lack a DBIC relationship (no SQL-level count)
182
                my $unsortable_counts = {};
183
                for my $key ( keys %{$embed} ) {
184
                    $unsortable_counts->{$key} = 1
185
                        if $embed->{$key}->{is_count} && !$count_aliases->{$key};
186
                }
187
188
                for my $i ( 0 .. $#{ $attributes->{order_by} } ) {
189
                    my $atom = $attributes->{order_by}[$i];
190
                    if ( ref($atom) eq 'HASH' ) {
191
                        for my $dir ( keys %{$atom} ) {
192
                            ( my $col = $atom->{$dir} ) =~ s/^me\.//;
193
194
                            # Reject sorting on +count embeds without a DBIC relationship
195
                            Koha::Exceptions::BadParameter->throw(
196
                                "Cannot sort on $col: no matching database relationship")
197
                                if $unsortable_counts->{$col};
198
199
                            # Replace with the literal subquery so DBIC puts it directly
200
                            # in the ORDER BY clause (DBIC +as is Perl-side only, not a SQL alias)
201
                            $atom->{$dir} = $count_aliases->{$col} if $count_aliases->{$col};
202
                        }
203
                    } elsif ( !ref($atom) ) {
204
                        ( my $col = $atom ) =~ s/^me\.//;
205
206
                        Koha::Exceptions::BadParameter->throw("Cannot sort on $col: no matching database relationship")
207
                            if $unsortable_counts->{$col};
208
209
                        $attributes->{order_by}[$i] = $count_aliases->{$col} if $count_aliases->{$col};
210
                    }
211
                }
212
            }
159
        }
213
        }
160
    );
214
    );
161
215
Lines 449-467 sub _merge_embed { Link Here
449
    }
503
    }
450
}
504
}
451
505
506
=head3 _merge_count_select
507
508
    my $added = _merge_count_select( $key, $attributes, $result_set );
509
510
Given a I<$key> like C<claims_count> (from a C<claims+count> embed), strips the C<_count>
511
suffix to derive the DBIC relationship name, then uses the relationship metadata to build
512
a correlated COUNT subquery added via C<+select>/C<+as>.
513
514
This makes the count available as a virtual column in the SQL result, enabling both
515
server-side sorting (ORDER BY) and an efficient single-query count instead of N+1.
516
517
Returns the subquery scalar reference if added (used by the caller to substitute
518
into ORDER BY clauses), or undef if the relationship was not found in the
519
C<prefetch_whitelist> or has no DBIC relationship info. In the latter case, the caller
520
should fall through to the Koha-level C<< $object->$relation->count >> path in C<to_api>.
521
522
Handles both C<Koha::Objects> (plural, via C<_resultset>) and C<Koha::Object> (singular,
523
via C<_result>) for accessing the DBIC result source.
524
525
=cut
526
527
sub _merge_count_select {
528
    my ( $key, $attributes, $result_set ) = @_;
529
530
    ( my $rel = $key ) =~ s/_count$//;
531
    return unless exists $result_set->prefetch_whitelist->{$rel};
532
533
    my $subquery = _build_count_subquery( $rel, $result_set, 'me' );
534
    return unless $subquery;
535
536
    push @{ $attributes->{'+select'} }, $subquery;
537
    push @{ $attributes->{'+as'} },     $key;
538
539
    return $subquery;
540
}
541
542
=head3 _build_count_subquery
543
544
    my $subquery = _build_count_subquery( $rel, $result_set, $parent_alias );
545
546
Builds a correlated COUNT subquery scalar ref for the given DBIC relationship.
547
I<$parent_alias> is the SQL alias of the parent table (e.g. C<me> for top-level,
548
or the relation name for nested embeds like C<biblio>).
549
550
Returns the scalar ref, or undef if the relationship has no DBIC metadata.
551
552
=cut
553
554
sub _build_count_subquery {
555
    my ( $rel, $result_set, $parent_alias ) = @_;
556
557
    my $source =
558
          $result_set->can('_resultset')
559
        ? $result_set->_resultset->result_source
560
        : $result_set->_result->result_source;
561
    my $rel_info = $source->relationship_info($rel);
562
    return unless $rel_info;
563
564
    my $rel_table    = $source->related_source($rel)->name;
565
    my $cond         = $rel_info->{cond};
566
    my ($fk_foreign) = keys %{$cond};
567
    my $fk_self      = $cond->{$fk_foreign};
568
    $fk_foreign =~ s/^foreign\.//;
569
    $fk_self    =~ s/^self\.//;
570
571
    return \"(SELECT COUNT(*) FROM $rel_table WHERE $rel_table.$fk_foreign = $parent_alias.$fk_self)";
572
}
573
452
sub _parse_prefetch {
574
sub _parse_prefetch {
453
    my ( $key, $embed, $result_set ) = @_;
575
    my ( $key, $embed, $result_set, $attributes, $parent_alias, $count_aliases ) = @_;
576
577
    $parent_alias //= 'me';
454
578
455
    my $pref_key = $key;
579
    my $pref_key = $key;
456
    $pref_key =~ s/_count$// if $embed->{$key}->{is_count};
580
    $pref_key =~ s/_count$// if $embed->{$key}->{is_count};
457
    return unless exists $result_set->prefetch_whitelist->{$pref_key};
581
    return unless exists $result_set->prefetch_whitelist->{$pref_key};
458
582
583
    # Handle nested +count embeds with SQL-level subquery
584
    if ( $embed->{$key}->{is_count} && $attributes ) {
585
        my $subquery = _build_count_subquery( $pref_key, $result_set, $parent_alias );
586
        if ($subquery) {
587
588
            # Use the full dotted path as the alias key so order_by fixup
589
            # can match e.g. "biblio.items_count" from _from_api_param
590
            my $alias_key = $parent_alias eq 'me' ? $key : "$parent_alias.$key";
591
            push @{ $attributes->{'+select'} }, $subquery;
592
            push @{ $attributes->{'+as'} },     $alias_key;
593
            $count_aliases->{$alias_key} = $subquery if $count_aliases;
594
            return;    # no prefetch needed, count is in the subquery
595
        }
596
    }
597
459
    my $ko_class = $result_set->prefetch_whitelist->{$pref_key};
598
    my $ko_class = $result_set->prefetch_whitelist->{$pref_key};
460
    return $pref_key unless defined $embed->{$key}->{children} && defined $ko_class;
599
    return $pref_key unless defined $embed->{$key}->{children} && defined $ko_class;
461
600
462
    my @prefetches;
601
    my @prefetches;
463
    foreach my $child ( sort keys( %{ $embed->{$key}->{children} } ) ) {
602
    foreach my $child ( sort keys( %{ $embed->{$key}->{children} } ) ) {
464
        my $parsed = _parse_prefetch( $child, $embed->{$key}->{children}, $ko_class->new );
603
        my $parsed = _parse_prefetch(
604
            $child, $embed->{$key}->{children}, $ko_class->new, $attributes, $pref_key,
605
            $count_aliases
606
        );
465
        push @prefetches, $parsed if defined $parsed;
607
        push @prefetches, $parsed if defined $parsed;
466
    }
608
    }
467
609
468
- 

Return to bug 41950