From c78485824293aa0286434a78a0f625da1272ab12 Mon Sep 17 00:00:00 2001 From: Kevin Carnes Date: Tue, 24 Feb 2026 13:22:01 +0100 Subject: [PATCH] Bug 21941: Fix GROUP BY in circ/reserveratios.pl When using strict_sql_modes, the Hold ratios page causes an Incorrect GROUP BY error and returns an Error 500. This patch removes uncessary columns from the database query and adds the biblio columns to the GROUP BY statement, so they can be selected. Since it's already grouped by biblionumber, it should not cause any additional rows to be created. Test plan: 1. Make sure that strict_sql_modes is enabled in the koha-conf.xml file. 2. Place at least one hold on a book. 3. Go to Circulation and select Hold ratios. 4. Observe that you get an Error 500. 5. Apply patch. 6. Repeat step 3. 7. Observe that you can view the page. 8. If the table is empty, change the ratio to 1. 9. Sign off. Sponsored-by: Lund University Library --- circ/reserveratios.pl | 22 +++++----------------- 1 file changed, 5 insertions(+), 17 deletions(-) diff --git a/circ/reserveratios.pl b/circ/reserveratios.pl index 9810e3c42cd..418b57c591a 100755 --- a/circ/reserveratios.pl +++ b/circ/reserveratios.pl @@ -98,13 +98,7 @@ my $include_aqorders_qty_join = my $nfl_comparison = $include_ordered ? '<=' : '='; my $sus_comparison = $include_suspended ? '<=' : '<'; -my $strsth = "SELECT reservedate, - reserves.borrowernumber as borrowernumber, - reserves.biblionumber, - reserves.branchcode as branch, - items.holdingbranch, - items.itemcallnumber, - items.itemnumber, +my $strsth = "SELECT reserves.biblionumber, GROUP_CONCAT(DISTINCT items.itemcallnumber ORDER BY items.itemnumber SEPARATOR '|') as listcall, GROUP_CONCAT(DISTINCT homebranch @@ -117,8 +111,6 @@ my $strsth = "SELECT reservedate, ORDER BY items.itemnumber SEPARATOR '|') as l_itype, GROUP_CONCAT(DISTINCT items.ccode ORDER BY items.ccode SEPARATOR '|') as l_ccode, - - reserves.found, biblio.title, biblio.subtitle, biblio.medium, @@ -142,7 +134,10 @@ if ( C4::Context->preference('IndependentBranches') ) { push @query_params, C4::Context->userenv->{'branch'}; } -$strsth .= " GROUP BY reserves.biblionumber ORDER BY reservecount DESC"; +$strsth .= " GROUP BY reserves.biblionumber, biblio.title, biblio.subtitle, + biblio.medium, biblio.part_number, biblio.part_name, biblio.author + ORDER BY reservecount DESC +"; $template->param( sql => $strsth ); my $sth = $dbh->prepare($strsth); @@ -156,22 +151,15 @@ while ( my $data = $sth->fetchrow_hashref ) { push( @reservedata, { - reservedate => $data->{reservedate}, - priority => $data->{priority}, - name => $data->{borrower}, title => $data->{title}, subtitle => $data->{subtitle}, medium => $data->{medium}, part_number => $data->{part_number}, part_name => $data->{part_name}, author => $data->{author}, - itemnum => $data->{itemnumber}, biblionumber => $data->{biblionumber}, - holdingbranch => $data->{holdingbranch}, homebranch_list => [ split( '\|', $data->{homebranch_list} ) ], holdingbranch_list => [ split( '\|', $data->{holdingbranch_list} ) ], - branch => $data->{branch}, - itemcallnumber => $data->{itemcallnumber}, location => [ split( '\|', $data->{l_location} ) ], itype => [ split( '\|', $data->{l_itype} ) ], ccode => [ split( '\|', $data->{l_ccode} ) ], -- 2.43.0