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

(-)a/Koha/Object.pm (-3 / +11 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
                # When no subquery was added (no DBIC relationship, or not called via the
688
                # API search path), get_column returns undef and we fall back to the
689
                # original $relation->count call, which always works.
690
                my $count = eval { $self->_result->get_column($embed) };
691
                $json_object->{$embed} = defined $count ? $count : $self->$relation->count;
684
            } else {
692
            } else {
685
                my $curr = $embed;
693
                my $curr = $embed;
686
                my $next = $embeds->{$curr}->{children};
694
                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 (-5 / +153 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; mark as unsortable (0) so the order_by
164
                    # fixup rejects it with a 400. Fall through so to_api can use the
165
                    # Koha method ->count fallback for display.
166
                    $count_aliases->{$key} = 0;
167
                }
168
169
                my $parsed = _parse_prefetch( $key, $embed, $result_set, $attributes, undef, $count_aliases );
153
                push @prefetches, $parsed if defined $parsed;
170
                push @prefetches, $parsed if defined $parsed;
154
            }
171
            }
155
172
156
            if ( scalar(@prefetches) ) {
173
            if ( scalar(@prefetches) ) {
157
                $attributes->{prefetch} = \@prefetches;
174
                $attributes->{prefetch} = \@prefetches;
158
            }
175
            }
176
177
            # Fix order_by entries referencing count aliases (me.X_count => X_count).
178
            # dbic_merge_sorting (which runs before this) translates _order_by=-me.claims_count
179
            # into { -desc => 'me.claims_count' }. But +as aliases are not real table columns,
180
            # so DBIC needs them without the 'me.' prefix to match the +select subquery.
181
            if ( $attributes->{order_by} ) {
182
183
                for my $i ( 0 .. $#{ $attributes->{order_by} } ) {
184
                    my $atom = $attributes->{order_by}[$i];
185
                    if ( ref($atom) eq 'HASH' ) {
186
                        for my $dir ( keys %{$atom} ) {
187
                            ( my $col = $atom->{$dir} ) =~ s/^me\.//;
188
189
                            # Reject sorting on +count embeds without a DBIC relationship.
190
                            # $count_aliases->{$col} is 0 (unsortable) or missing (top-level unsortable)
191
                            Koha::Exceptions::BadParameter->throw(
192
                                "Cannot sort on $col: no matching database relationship")
193
                                if exists $count_aliases->{$col} && !$count_aliases->{$col};
194
195
                            # Replace with the literal subquery so DBIC puts it directly
196
                            # in the ORDER BY clause (DBIC +as is Perl-side only, not a SQL alias)
197
                            $atom->{$dir} = $count_aliases->{$col} if $count_aliases->{$col};
198
                        }
199
                    } elsif ( !ref($atom) ) {
200
                        ( my $col = $atom ) =~ s/^me\.//;
201
202
                        Koha::Exceptions::BadParameter->throw("Cannot sort on $col: no matching database relationship")
203
                            if exists $count_aliases->{$col} && !$count_aliases->{$col};
204
205
                        $attributes->{order_by}[$i] = $count_aliases->{$col} if $count_aliases->{$col};
206
                    }
207
                }
208
            }
159
        }
209
        }
160
    );
210
    );
161
211
Lines 449-467 sub _merge_embed { Link Here
449
    }
499
    }
450
}
500
}
451
501
502
=head3 _merge_count_select
503
504
    my $added = _merge_count_select( $key, $attributes, $result_set );
505
506
Given a I<$key> like C<claims_count> (from a C<claims+count> embed), strips the C<_count>
507
suffix to derive the DBIC relationship name, then uses the relationship metadata to build
508
a correlated COUNT subquery added via C<+select>/C<+as>.
509
510
This makes the count available as a virtual column in the SQL result, enabling both
511
server-side sorting (ORDER BY) and an efficient single-query count instead of N+1.
512
513
Returns the subquery scalar reference if added (used by the caller to substitute
514
into ORDER BY clauses), or undef if the relationship was not found in the
515
C<prefetch_whitelist> or has no DBIC relationship info. In the latter case, the count
516
embed still works for display: C<to_api> falls back transparently to the original
517
C<< $object->$relation->count >> Perl-level call. Only sorting is unavailable.
518
519
Handles both C<Koha::Objects> (plural, via C<_resultset>) and C<Koha::Object> (singular,
520
via C<_result>) for accessing the DBIC result source.
521
522
=cut
523
524
sub _merge_count_select {
525
    my ( $key, $attributes, $result_set ) = @_;
526
527
    ( my $rel = $key ) =~ s/_count$//;
528
    return unless exists $result_set->prefetch_whitelist->{$rel};
529
530
    my $subquery = _build_count_subquery( $rel, $result_set, 'me' );
531
    return unless $subquery;
532
533
    push @{ $attributes->{'+select'} }, $subquery;
534
    push @{ $attributes->{'+as'} },     $key;
535
536
    return $subquery;
537
}
538
539
=head3 _build_count_subquery
540
541
    my $subquery = _build_count_subquery( $rel, $result_set, $parent_alias );
542
543
Builds a correlated COUNT subquery scalar ref for the given DBIC relationship.
544
I<$parent_alias> is the SQL alias of the parent table (e.g. C<me> for top-level,
545
or the relation name for nested embeds like C<biblio>).
546
547
Returns the scalar ref, or undef if the relationship has no DBIC metadata.
548
549
=cut
550
551
sub _build_count_subquery {
552
    my ( $rel, $result_set, $parent_alias ) = @_;
553
554
    my $source =
555
          $result_set->can('_resultset')
556
        ? $result_set->_resultset->result_source
557
        : $result_set->_result->result_source;
558
    my $rel_info = $source->relationship_info($rel);
559
    return unless $rel_info;
560
561
    my $rel_table    = $source->related_source($rel)->name;
562
    my $cond         = $rel_info->{cond};
563
    my ($fk_foreign) = keys %{$cond};
564
    my $fk_self      = $cond->{$fk_foreign};
565
    $fk_foreign =~ s/^foreign\.//;
566
    $fk_self    =~ s/^self\.//;
567
568
    return \"(SELECT COUNT(*) FROM $rel_table WHERE $rel_table.$fk_foreign = $parent_alias.$fk_self)";
569
}
570
452
sub _parse_prefetch {
571
sub _parse_prefetch {
453
    my ( $key, $embed, $result_set ) = @_;
572
    my ( $key, $embed, $result_set, $attributes, $parent_alias, $count_aliases ) = @_;
573
574
    $parent_alias //= 'me';
454
575
455
    my $pref_key = $key;
576
    my $pref_key = $key;
456
    $pref_key =~ s/_count$// if $embed->{$key}->{is_count};
577
    $pref_key =~ s/_count$// if $embed->{$key}->{is_count};
457
    return unless exists $result_set->prefetch_whitelist->{$pref_key};
578
579
    if ( !exists $result_set->prefetch_whitelist->{$pref_key} ) {
580
581
        # Track unsortable nested +count embeds (no DBIC relationship) so the
582
        # order_by fixup can reject them with a 400 instead of a DBIC 500
583
        if ( $embed->{$key}->{is_count} && $count_aliases ) {
584
            my $alias_key = $parent_alias eq 'me' ? $key : "$parent_alias.$key";
585
            $count_aliases->{$alias_key} = 0;    # 0 = unsortable
586
        }
587
        return;
588
    }
589
590
    # Handle nested +count embeds with SQL-level subquery
591
    if ( $embed->{$key}->{is_count} && $attributes ) {
592
        my $subquery = _build_count_subquery( $pref_key, $result_set, $parent_alias );
593
        if ($subquery) {
594
595
            # Use the full dotted path as the alias key so order_by fixup
596
            # can match e.g. "biblio.items_count" from _from_api_param
597
            my $alias_key = $parent_alias eq 'me' ? $key : "$parent_alias.$key";
598
            push @{ $attributes->{'+select'} }, $subquery;
599
            push @{ $attributes->{'+as'} },     $alias_key;
600
            $count_aliases->{$alias_key} = $subquery if $count_aliases;
601
            return;    # no prefetch needed, count is in the subquery
602
        }
603
    }
458
604
459
    my $ko_class = $result_set->prefetch_whitelist->{$pref_key};
605
    my $ko_class = $result_set->prefetch_whitelist->{$pref_key};
460
    return $pref_key unless defined $embed->{$key}->{children} && defined $ko_class;
606
    return $pref_key unless defined $embed->{$key}->{children} && defined $ko_class;
461
607
462
    my @prefetches;
608
    my @prefetches;
463
    foreach my $child ( sort keys( %{ $embed->{$key}->{children} } ) ) {
609
    foreach my $child ( sort keys( %{ $embed->{$key}->{children} } ) ) {
464
        my $parsed = _parse_prefetch( $child, $embed->{$key}->{children}, $ko_class->new );
610
        my $parsed = _parse_prefetch(
611
            $child, $embed->{$key}->{children}, $ko_class->new, $attributes, $pref_key,
612
            $count_aliases
613
        );
465
        push @prefetches, $parsed if defined $parsed;
614
        push @prefetches, $parsed if defined $parsed;
466
    }
615
    }
467
616
468
- 

Return to bug 41950