Membership detail report uses deprecated method causing fatal error
Before
Generated SQL for Membership Detail report contained errors. More specifically, it was joining twice on the civicrm_acl_contact_cache
table AS WELL AS doing some ACL filtering in a where clause. See the example below
SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name AS civicrm_contact_sort_name,
contact_civireport.id AS civicrm_contact_id,
membership_civireport.membership_type_id AS civicrm_membership_membership_type_id,
membership_civireport.start_date AS civicrm_membership_membership_start_date,
membership_civireport.end_date AS civicrm_membership_membership_end_date,
membership_civireport.join_date AS civicrm_membership_join_date,
mem_status_civireport.name AS civicrm_membership_status_name,
contribution_civireport.id AS civicrm_contribution_contribution_id,
contribution_civireport.currency AS civicrm_contribution_currency
FROM civicrm_contact contact_civireport
INNER JOIN civicrm_acl_contact_cache aclContactCache ON contact_civireport.id = aclContactCache.contact_id
INNER JOIN civicrm_acl_contact_cache aclContactCache ON contact_civireport.id = aclContactCache.contact_id
INNER JOIN civicrm_membership membership_civireport ON contact_civireport.id = membership_civireport.contact_id
AND membership_civireport.is_test = 0
LEFT JOIN civicrm_membership_status mem_status_civireport ON mem_status_civireport.id = membership_civireport.status_id
LEFT JOIN civicrm_membership_payment cmp ON membership_civireport.id = cmp.membership_id
LEFT JOIN civicrm_contribution contribution_civireport ON cmp.contribution_id=contribution_civireport.id
WHERE (1)
AND (`contact_civireport`.`id` IS NULL
OR (`contact_civireport`.`id` IN
(SELECT contact_id
FROM civicrm_acl_contact_cache
WHERE user_id = 636)))
AND (`contact_civireport`.`is_deleted` IS NULL
OR (`contact_civireport`.`is_deleted` != 1))
GROUP BY membership_civireport.id
ORDER BY contact_civireport.sort_name ASC,
membership_civireport.membership_type_id ASC,
contribution_civireport.receive_date DESC
LIMIT 0,
50
This appears to be because it is calling the deprecated method CRM_Report_Form::buildACLClause()
and then later calling CRM_Report_Form::buildQuery
which then calls CRM_Report_Form::buildPermissionClause();
(the function that buildACLClause was deprecated in favour of.
After
When one removes the call to ::buildACLClause
from CRM_Report_Form_Member_Detail::postProcess
, the generated SQL is as follows:
SELECT SQL_CALC_FOUND_ROWS contact_civireport.sort_name AS civicrm_contact_sort_name,
contact_civireport.id AS civicrm_contact_id,
membership_civireport.membership_type_id AS civicrm_membership_membership_type_id,
membership_civireport.start_date AS civicrm_membership_membership_start_date,
membership_civireport.end_date AS civicrm_membership_membership_end_date,
membership_civireport.join_date AS civicrm_membership_join_date,
mem_status_civireport.name AS civicrm_membership_status_name,
contribution_civireport.id AS civicrm_contribution_contribution_id,
contribution_civireport.currency AS civicrm_contribution_currency
FROM civicrm_contact contact_civireport
INNER JOIN civicrm_membership membership_civireport ON contact_civireport.id = membership_civireport.contact_id
AND membership_civireport.is_test = 0
LEFT JOIN civicrm_membership_status mem_status_civireport ON mem_status_civireport.id = membership_civireport.status_id
LEFT JOIN civicrm_membership_payment cmp ON membership_civireport.id = cmp.membership_id
LEFT JOIN civicrm_contribution contribution_civireport ON cmp.contribution_id=contribution_civireport.id
WHERE (1)
AND (`contact_civireport`.`id` IS NULL
OR (`contact_civireport`.`id` IN
(SELECT contact_id
FROM civicrm_acl_contact_cache
WHERE user_id = 636)))
AND (`contact_civireport`.`is_deleted` IS NULL
OR (`contact_civireport`.`is_deleted` != 1))
GROUP BY membership_civireport.id
ORDER BY contact_civireport.sort_name ASC,
membership_civireport.membership_type_id ASC,
contribution_civireport.receive_date DESC
LIMIT 0,
50
This SQL works nicely and the report respects the ACL.
In turns out that once you have removed the call to the deprecated function, the postProcess method is identical to its parent, so it can be removed completely.