From d13e2973d0df05b86c6ddfe0329f3231d1c73f74 Mon Sep 17 00:00:00 2001 From: Alan Dixon Date: Thu, 27 Apr 2023 10:22:27 -0400 Subject: [PATCH] Only filter to the most recent contribution when grouping by membership --- CRM/Report/Form/Member/Detail.php | 9 +++++++-- 1 file changed, 7 insertions(+), 2 deletions(-) diff --git a/CRM/Report/Form/Member/Detail.php b/CRM/Report/Form/Member/Detail.php index eb79610c47..544c3583f8 100644 --- a/CRM/Report/Form/Member/Detail.php +++ b/CRM/Report/Form/Member/Detail.php @@ -294,10 +294,15 @@ class CRM_Report_Form_Member_Detail extends CRM_Report_Form { //used when contribution field is selected. if ($this->isTableSelected('civicrm_contribution')) { + // if we're grouping (by membership), we need to make sure the inner join picks the most recent contribution. + $groupedBy = !empty($this->_params['group_bys']['id']); $this->_from .= " LEFT JOIN civicrm_membership_payment cmp - ON ({$this->_aliases['civicrm_membership']}.id = cmp.membership_id - AND cmp.id = (SELECT MAX(id) FROM civicrm_membership_payment WHERE civicrm_membership_payment.membership_id = {$this->_aliases['civicrm_membership']}.id)) + ON ({$this->_aliases['civicrm_membership']}.id = cmp.membership_id"; + $this->_from .= $groupedBy ? " + AND cmp.id = (SELECT MAX(id) FROM civicrm_membership_payment WHERE civicrm_membership_payment.membership_id = {$this->_aliases['civicrm_membership']}.id))" + : ")"; + $this->_from .= " LEFT JOIN civicrm_contribution {$this->_aliases['civicrm_contribution']} ON cmp.contribution_id={$this->_aliases['civicrm_contribution']}.id\n"; } -- 2.25.1