From ef36c94c171536a0cc582c7b27cbe7f4802d4960 Mon Sep 17 00:00:00 2001 From: Nick Clemens Date: Wed, 30 Aug 2023 18:56:59 +0000 Subject: [PATCH] Bug 34666: Combine queries in _Findgroupreserve The queries here are the same except for 2 differences: 1 - They check if the hold was on a particular item 2 - The latter confirms that the reserve item group matches the item's item group For 1, it doesn't matter - only 1 item can be mapped ot a reserve, itemnumber is the primary key for hold_fill_targets - so we are either matching it in the first query or the second, either way we get the same reserve - the returns are the same so we don't care which query it came from For 2, this has already been checked when the queue was built. We don't need to verify the match because it wouldn't be in the targets if they didn't match To test: 1 - Apply second unit test patch 2 - prove t/db_dependent/Reserves.t 3 - It should pass 4 - Apply this patch 5 - prove t/db_dependent/Reserves.t 6 - It continues to pass --- C4/Reserves.pm | 47 +++-------------------------------------------- 1 file changed, 3 insertions(+), 44 deletions(-) diff --git a/C4/Reserves.pm b/C4/Reserves.pm index 160fc369c1..b771a37083 100644 --- a/C4/Reserves.pm +++ b/C4/Reserves.pm @@ -1717,9 +1717,8 @@ sub _Findgroupreserve { my ( $biblionumber, $itemnumber, $lookahead, $ignore_borrowers) = @_; my $dbh = C4::Context->dbh; - # TODO: consolidate at least the SELECT portion of the first 2 queries to a common $select var. - # check for exact targeted match - my $item_level_target_query = qq{ + # check for targeted match form the holds queue + my $hold_target_query = qq{ SELECT reserves.biblionumber AS biblionumber, reserves.borrowernumber AS borrowernumber, reserves.reservedate AS reservedate, @@ -1740,13 +1739,12 @@ sub _Findgroupreserve { JOIN hold_fill_targets USING (reserve_id) WHERE found IS NULL AND priority > 0 - AND item_level_request = 1 AND hold_fill_targets.itemnumber = ? AND reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY) AND suspend = 0 ORDER BY priority }; - my $sth = $dbh->prepare($item_level_target_query); + my $sth = $dbh->prepare($hold_target_query); $sth->execute($itemnumber, $lookahead||0); my @results; if ( my $data = $sth->fetchrow_hashref ) { @@ -1755,45 +1753,6 @@ sub _Findgroupreserve { } return @results if @results; - # check for title-level targeted match - my $title_level_target_query = qq{ - SELECT reserves.biblionumber AS biblionumber, - reserves.borrowernumber AS borrowernumber, - reserves.reservedate AS reservedate, - reserves.branchcode AS branchcode, - reserves.cancellationdate AS cancellationdate, - reserves.found AS found, - reserves.reservenotes AS reservenotes, - reserves.priority AS priority, - reserves.timestamp AS timestamp, - biblioitems.biblioitemnumber AS biblioitemnumber, - reserves.itemnumber AS itemnumber, - reserves.reserve_id AS reserve_id, - reserves.itemtype AS itemtype, - reserves.non_priority AS non_priority, - reserves.item_group_id AS item_group_id - FROM reserves - JOIN biblioitems USING (biblionumber) - JOIN hold_fill_targets USING (reserve_id) - LEFT JOIN item_group_items ON ( item_group_items.item_id = hold_fill_targets.itemnumber ) - WHERE found IS NULL - AND priority > 0 - AND item_level_request = 0 - AND hold_fill_targets.itemnumber = ? - AND reservedate <= DATE_ADD(NOW(),INTERVAL ? DAY) - AND suspend = 0 - AND (reserves.item_group_id = item_group_items.item_group_id OR reserves.item_group_id IS NULL) - ORDER BY priority - }; - $sth = $dbh->prepare($title_level_target_query); - $sth->execute($itemnumber, $lookahead||0); - @results = (); - if ( my $data = $sth->fetchrow_hashref ) { - push( @results, $data ) - unless any{ $data->{borrowernumber} eq $_ } @$ignore_borrowers ; - } - return @results if @results; - my $query = qq{ SELECT reserves.biblionumber AS biblionumber, reserves.borrowernumber AS borrowernumber, -- 2.30.2