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