From 756c3c8abfe26ca975225399d2628e0ce2e70e64 Mon Sep 17 00:00:00 2001
From: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Date: Wed, 21 Jun 2023 12:38:54 +0000
Subject: [PATCH] Bug 31383: Use one query in search_for_display instead of
four
Benchmarking does however not show real performance gain.
But it might be considered as a code improvement?
Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
---
Koha/AdditionalContents.pm | 53 ++++++++++++--------------------------
1 file changed, 17 insertions(+), 36 deletions(-)
diff --git a/Koha/AdditionalContents.pm b/Koha/AdditionalContents.pm
index 4b97fc1d837..bbad4869a2a 100644
--- a/Koha/AdditionalContents.pm
+++ b/Koha/AdditionalContents.pm
@@ -73,44 +73,25 @@ location is one of this:
sub search_for_display {
my ( $self, $params ) = @_;
+ my $lang = $params->{lang};
- my $search_params;
- $search_params->{location} = $params->{location};
- $search_params->{branchcode} = $params->{library_id} ? [ $params->{library_id}, undef ] : undef;
- $search_params->{published_on} = { '<=' => \'CAST(NOW() AS DATE)' };
- $search_params->{-or} = [
- expirationdate => { '>=' => \'CAST(NOW() AS DATE)' },
- expirationdate => undef
- ];
- $search_params->{category} = $params->{category} if $params->{category};
-
- my $contents = $self->SUPER::search( $search_params, { order_by => 'number' } );
- my @all_content_id = $contents->get_column('id');
-
- my ( $translated_contents, @translated_content_id );
- if ( $params->{lang} && $params->{lang} ne 'default' ) {
- $translated_contents = Koha::AdditionalContentsLocalizations->search(
- {
- additional_content_id => [$contents->get_column('id')],
- lang => $params->{lang},
- }
- );
- @translated_content_id = $translated_contents->get_column('additional_content_id');
- }
-
- my $default_contents = Koha::AdditionalContentsLocalizations->search(
- {
- additional_content_id => [array_minus(@all_content_id, @translated_content_id)],
- lang => 'default',
- }
- );
-
- return Koha::AdditionalContentsLocalizations->search(
- {
- id => [ $translated_contents ? $translated_contents->get_column('id') : (), $default_contents->get_column('id') ]
- },
- );
+ # If lang is not default, we will search for entries matching $lang but fallback to default if $lang is not found
+ # Then we need a subquery count in where clause; DBIx::Class/SQL::Abstract does not support it, fallback to literal SQL
+ my $subquery =
+ qq|(SELECT COUNT(*) FROM additional_contents_localizations WHERE lang='$lang' AND additional_content_id=me.additional_content_id)=0|;
+ my $search_params;
+ $search_params->{location} = $params->{location};
+ $search_params->{branchcode} = $params->{library_id} ? [ $params->{library_id}, undef ] : undef;
+ $search_params->{published_on} = { '<=' => \'CAST(NOW() AS DATE)' };
+ $search_params->{expirationdate} = [ '-or', { '>=' => \'CAST(NOW() AS DATE)' }, undef ];
+ $search_params->{category} = $params->{category} if $params->{category};
+ $search_params->{lang} = 'default' if !$lang || $lang eq 'default';
+ $search_params->{-or} = [ { 'lang' => $lang }, '-and' => [ 'lang', 'default', \$subquery ] ]
+ if !$search_params->{lang};
+
+ my $attribs = { prefetch => 'additional_content', order_by => 'additional_content.number' };
+ return Koha::AdditionalContentsLocalizations->search( $search_params, $attribs );
}
=head3 find_best_match
--
2.25.1