From 759db047d2ac72c55b1b4855c25067e0304ec091 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>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Signed-off-by: Tomas Cohen Arazi <tomascohen@theke.io>
---
 Koha/AdditionalContents.pm | 53 ++++++++++++--------------------------
 1 file changed, 17 insertions(+), 36 deletions(-)

diff --git a/Koha/AdditionalContents.pm b/Koha/AdditionalContents.pm
index 4b97fc1d83..bbad4869a2 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.39.5