From 5b583dc1857a41ebe63f2a8a3cbec8526380a3d7 Mon Sep 17 00:00:00 2001 From: Marcel de Rooy Date: Fri, 9 Feb 2018 11:47:52 +0100 Subject: [PATCH] Bug 19451: (QA follow-up) Replace weird subquery Content-Type: text/plain; charset=utf-8 As requested, we add a JOIN and make the SELECT distinct. Signed-off-by: Marcel de Rooy Tested that no-overwrite still works as expected. --- misc/maintenance/borrowers-force-messaging-defaults.pl | 9 ++++++--- 1 file changed, 6 insertions(+), 3 deletions(-) diff --git a/misc/maintenance/borrowers-force-messaging-defaults.pl b/misc/maintenance/borrowers-force-messaging-defaults.pl index 2daa8b7..72e8c95 100755 --- a/misc/maintenance/borrowers-force-messaging-defaults.pl +++ b/misc/maintenance/borrowers-force-messaging-defaults.pl @@ -54,12 +54,15 @@ sub force_borrower_messaging_defaults { $dbh->do(q|SET FOREIGN_KEY_CHECKS = 1|); } - my $sql = "SELECT borrowernumber, categorycode FROM borrowers bo WHERE dateenrolled >= ?"; + my $sql = +q|SELECT DISTINCT bo.borrowernumber, bo.categorycode FROM borrowers bo +LEFT JOIN borrower_message_preferences mp USING (borrowernumber) +WHERE bo.dateenrolled >= ?|; if ($not_expired) { - $sql .= " AND dateexpiry >= NOW()" + $sql .= " AND bo.dateexpiry >= NOW()" } if( $no_overwrite ) { - $sql .= " AND (SELECT COUNT(*) FROM borrower_message_preferences mp WHERE mp.borrowernumber=bo.borrowernumber) = 0" + $sql .= " AND mp.borrowernumber IS NULL"; } my $sth = $dbh->prepare($sql); $sth->execute($since); -- 2.1.4