From c4b067f6beaea575ab7aaf69208f9adf606dbe97 Mon Sep 17 00:00:00 2001 From: Pradeep Nayak Date: Sun, 5 Mar 2017 12:32:21 +0530 Subject: [PATCH] CRM-20193, optimized sql to use INNER JOIN with contribution table rathe LEFT JOIN ---------------------------------------- * CRM-20193: Draw currency from better table on Batch Transaction form https://issues.civicrm.org/jira/browse/CRM-20193 --- CRM/Batch/BAO/Batch.php | 15 ++++++--------- 1 file changed, 6 insertions(+), 9 deletions(-) diff --git a/CRM/Batch/BAO/Batch.php b/CRM/Batch/BAO/Batch.php index f5ce3e4e04..568139db8b 100644 --- a/CRM/Batch/BAO/Batch.php +++ b/CRM/Batch/BAO/Batch.php @@ -648,10 +648,11 @@ class CRM_Batch_BAO_Batch extends CRM_Batch_DAO_Batch { } $from = "civicrm_financial_trxn -LEFT JOIN civicrm_entity_financial_trxn ON civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id +INNER JOIN civicrm_entity_financial_trxn ON civicrm_entity_financial_trxn.financial_trxn_id = civicrm_financial_trxn.id +INNER JOIN civicrm_contribution ON (civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id + AND civicrm_entity_financial_trxn.entity_table='civicrm_contribution') LEFT JOIN civicrm_entity_batch ON civicrm_entity_batch.entity_table = 'civicrm_financial_trxn' AND civicrm_entity_batch.entity_id = civicrm_financial_trxn.id -LEFT JOIN civicrm_contribution ON civicrm_contribution.id = civicrm_entity_financial_trxn.entity_id LEFT JOIN civicrm_financial_type ON civicrm_financial_type.id = civicrm_contribution.financial_type_id LEFT JOIN civicrm_contact contact_a ON contact_a.id = civicrm_contribution.contact_id LEFT JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id = civicrm_contribution.id @@ -722,8 +723,7 @@ LEFT JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id } if (!empty($query->_where[0])) { $where = implode(' AND ', $query->_where[0]) . - " AND civicrm_entity_batch.batch_id IS NULL - AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution'"; + " AND civicrm_entity_batch.batch_id IS NULL "; $where = str_replace('civicrm_contribution.payment_instrument_id', 'civicrm_financial_trxn.payment_instrument_id', $where); $searchValue = TRUE; } @@ -733,13 +733,10 @@ LEFT JOIN civicrm_contribution_soft ON civicrm_contribution_soft.contribution_id if (!$searchValue) { if (!$notPresent) { - $where = " ( civicrm_entity_batch.batch_id = {$entityID} - AND civicrm_entity_batch.entity_table = 'civicrm_financial_trxn' - AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution') "; + $where = " civicrm_entity_batch.batch_id = {$entityID} "; } else { - $where = " ( civicrm_entity_batch.batch_id IS NULL - AND civicrm_entity_financial_trxn.entity_table = 'civicrm_contribution')"; + $where = " civicrm_entity_batch.batch_id IS NULL "; } } -- 2.25.1