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