From 5062a7b3d2766f13c7475b459b462a134da9f6a7 Mon Sep 17 00:00:00 2001
From: "Matthew Wire (MJW Consulting)" <mjw@mjwconsult.co.uk>
Date: Sun, 7 Jul 2019 12:05:13 +0100
Subject: [PATCH] Improve performance on getSoftContribution details - only run
 one query instead of one per contribution

---
 CRM/Contribute/BAO/ContributionSoft.php | 49 ++++++++++++++++---------
 api/v3/Contribution.php                 | 17 +++++----
 2 files changed, 40 insertions(+), 26 deletions(-)

diff --git a/CRM/Contribute/BAO/ContributionSoft.php b/CRM/Contribute/BAO/ContributionSoft.php
index 1ddfbedc56..e219d687dc 100644
--- a/CRM/Contribute/BAO/ContributionSoft.php
+++ b/CRM/Contribute/BAO/ContributionSoft.php
@@ -299,39 +299,53 @@ class CRM_Contribute_BAO_ContributionSoft extends CRM_Contribute_DAO_Contributio
    *   Array of soft contribution ids, amounts, and associated contact ids
    */
   public static function getSoftContribution($contributionID, $all = FALSE) {
-    $pcpFields = array(
+    $softContributionFields = self::getSoftCreditContributionFields([$contributionID], $all);
+    return isset($softContributionFields[$contributionID]) ? $softContributionFields[$contributionID] : [];
+  }
+
+  /**
+   * Retrieve soft contributions for an array of contribution records.
+   *
+   * @param array $contributionIDs
+   * @param bool $all
+   *   Include PCP data.
+   *
+   * @return array
+   *   Array of soft contribution ids, amounts, and associated contact ids
+   */
+  public static function getSoftCreditContributionFields($contributionIDs, $all = FALSE) {
+    $pcpFields = [
       'pcp_id',
       'pcp_title',
       'pcp_display_in_roll',
       'pcp_roll_nickname',
       'pcp_personal_note',
-    );
+    ];
 
-    $query = '
-    SELECT ccs.id, pcp_id, cpcp.title as pcp_title, pcp_display_in_roll, pcp_roll_nickname, pcp_personal_note, ccs.currency as currency, amount, ccs.contact_id as contact_id, c.display_name, ccs.soft_credit_type_id
+    $query = "
+    SELECT ccs.id, pcp_id, ccs.contribution_id as contribution_id, cpcp.title as pcp_title, pcp_display_in_roll, pcp_roll_nickname, pcp_personal_note, ccs.currency as currency, amount, ccs.contact_id as contact_id, c.display_name, ccs.soft_credit_type_id
     FROM civicrm_contribution_soft ccs INNER JOIN civicrm_contact c on c.id = ccs.contact_id
     LEFT JOIN civicrm_pcp cpcp ON ccs.pcp_id = cpcp.id
-    WHERE contribution_id = %1;
-    ';
-
-    $params = array(1 => array($contributionID, 'Integer'));
-
-    $dao = CRM_Core_DAO::executeQuery($query, $params);
+    WHERE contribution_id IN (%1)
+    ";
+    $queryParams = [1 => [implode(',', $contributionIDs), 'CommaSeparatedIntegers']];
+    $dao = CRM_Core_DAO::executeQuery($query, $queryParams);
 
-    $softContribution = array();
-    $count = 1;
+    $softContribution = $indexes = [];
     while ($dao->fetch()) {
       if ($dao->pcp_id) {
         if ($all) {
           foreach ($pcpFields as $val) {
-            $softContribution[$val] = $dao->$val;
+            $softContribution[$dao->contribution_id][$val] = $dao->$val;
           }
-          $softContribution['pcp_soft_credit_to_name'] = $dao->display_name;
-          $softContribution['pcp_soft_credit_to_id'] = $dao->contact_id;
+          $softContribution[$dao->contribution_id]['pcp_soft_credit_to_name'] = $dao->display_name;
+          $softContribution[$dao->contribution_id]['pcp_soft_credit_to_id'] = $dao->contact_id;
         }
       }
       else {
-        $softContribution['soft_credit'][$count] = array(
+        // Use a 1-based array because that's what this function returned before refactoring in https://github.com/civicrm/civicrm-core/pull/14747
+        $indexes[$dao->contribution_id] = isset($indexes[$dao->contribution_id]) ? $indexes[$dao->contribution_id] + 1 : 1;
+        $softContribution[$dao->contribution_id]['soft_credit'][$indexes[$dao->contribution_id]] = [
           'contact_id' => $dao->contact_id,
           'soft_credit_id' => $dao->id,
           'currency' => $dao->currency,
@@ -339,8 +353,7 @@ class CRM_Contribute_BAO_ContributionSoft extends CRM_Contribute_DAO_Contributio
           'contact_name' => $dao->display_name,
           'soft_credit_type' => $dao->soft_credit_type_id,
           'soft_credit_type_label' => CRM_Core_PseudoConstant::getLabel('CRM_Contribute_BAO_ContributionSoft', 'soft_credit_type_id', $dao->soft_credit_type_id),
-        );
-        $count++;
+        ];
       }
     }
 
diff --git a/api/v3/Contribution.php b/api/v3/Contribution.php
index f59951194c..6c710fdc6c 100644
--- a/api/v3/Contribution.php
+++ b/api/v3/Contribution.php
@@ -265,15 +265,16 @@ function civicrm_api3_contribution_get($params) {
   $additionalOptions = _civicrm_api3_contribution_get_support_nonunique_returns($params);
   $returnProperties = CRM_Contribute_BAO_Query::defaultReturnProperties($mode);
 
+  // Get the contributions based on parameters passed in
   $contributions = _civicrm_api3_get_using_query_object('Contribution', $params, $additionalOptions, NULL, $mode, $returnProperties);
-
-  foreach ($contributions as $id => $contribution) {
-    $softContribution = CRM_Contribute_BAO_ContributionSoft::getSoftContribution($id, TRUE);
-    $contributions[$id] = array_merge($contribution, $softContribution);
-    // format soft credit for backward compatibility
-    _civicrm_api3_format_soft_credit($contributions[$id]);
-    _civicrm_api3_contribution_add_supported_fields($contributions[$id]);
-
+  if (!empty($contributions)) {
+    $softContributions = CRM_Contribute_BAO_ContributionSoft::getSoftCreditContributionFields(array_keys($contributions), TRUE);
+    foreach ($contributions as $id => $contribution) {
+      $contributions[$id] = isset($softContributions[$id]) ? array_merge($contribution, $softContributions[$id]) : $contribution;
+      // format soft credit for backward compatibility
+      _civicrm_api3_format_soft_credit($contributions[$id]);
+      _civicrm_api3_contribution_add_supported_fields($contributions[$id]);
+    }
   }
   return civicrm_api3_create_success($contributions, $params, 'Contribution', 'get');
 }
-- 
2.25.1