From 3636b5209a6a696fcf78baf0a9fcaa0675242371 Mon Sep 17 00:00:00 2001 From: jitendrapurohit Date: Thu, 2 Jun 2016 11:52:19 +0530 Subject: [PATCH] make it compatible with only 5.7+ --- CRM/Activity/BAO/Activity.php | 13 +++-- CRM/Admin/Page/Tag.php | 7 ++- CRM/Campaign/Form/Survey/Results.php | 2 +- CRM/Case/BAO/Case.php | 49 +++++++++---------- CRM/Contact/BAO/GroupContact.php | 2 +- CRM/Contact/BAO/Query.php | 19 +++++-- .../Form/Search/Custom/ContribSYBNT.php | 2 +- .../Search/Custom/ContributionAggregate.php | 2 +- CRM/Contact/Form/Search/Custom/DateAdded.php | 6 +-- .../Form/Search/Custom/FullText/Activity.php | 10 ++-- CRM/Contact/Form/Search/Custom/Group.php | 2 +- .../Form/Search/Custom/TagContributions.php | 2 +- CRM/Contribute/BAO/Contribution.php | 14 +++--- CRM/Event/BAO/Participant.php | 10 ++-- CRM/Export/BAO/Export.php | 17 ++++--- CRM/Mailing/BAO/Mailing.php | 4 +- CRM/Mailing/BAO/MailingAB.php | 3 +- CRM/PCP/BAO/PCP.php | 4 +- CRM/Pledge/Selector/Search.php | 2 +- CRM/Price/BAO/PriceSet.php | 2 +- CRM/Report/Form/Contribute/Detail.php | 4 +- 21 files changed, 94 insertions(+), 82 deletions(-) diff --git a/CRM/Activity/BAO/Activity.php b/CRM/Activity/BAO/Activity.php index a31dc4ab9c..e323eb1bea 100644 --- a/CRM/Activity/BAO/Activity.php +++ b/CRM/Activity/BAO/Activity.php @@ -717,7 +717,7 @@ class CRM_Activity_BAO_Activity extends CRM_Activity_DAO_Activity { $insertSQL = "INSERT INTO {$activityTempTable} (" . implode(',', $insertValueSQL) . " ) "; $order = $limit = $groupBy = ''; - $groupBy = " GROUP BY " . implode(',', $insertValueSQL); + $groupBy = " GROUP BY tbl.activity_id, tbl.activity_type, tbl.case_id, tbl.case_subject "; if (!empty($input['sort'])) { if (is_a($input['sort'], 'CRM_Utils_Sort')) { @@ -791,21 +791,20 @@ WHERE ac.record_type_id != %1 // for each activity insert one target contact // if we load all target contacts the performance will suffer a lot for mass-activities. - $select = "ac.activity_id, + $query = " +INSERT INTO {$activityContactTempTable} ( activity_id, contact_id, record_type_id, contact_name, is_deleted, counter ) +SELECT ac.activity_id, ac.contact_id, ac.record_type_id, c.sort_name, - c.is_deleted"; - $query = " -INSERT INTO {$activityContactTempTable} ( activity_id, contact_id, record_type_id, contact_name, is_deleted, counter ) -SELECT {$select}, + c.is_deleted, count(ac.contact_id) FROM {$activityTempTable} INNER JOIN civicrm_activity a ON ( a.id = {$activityTempTable}.activity_id ) INNER JOIN civicrm_activity_contact ac ON ( ac.activity_id = {$activityTempTable}.activity_id ) INNER JOIN civicrm_contact c ON c.id = ac.contact_id WHERE ac.record_type_id = %1 -GROUP BY {$select} +GROUP BY ac.activity_id, ac.contact_id "; CRM_Core_DAO::executeQuery($query, $params); diff --git a/CRM/Admin/Page/Tag.php b/CRM/Admin/Page/Tag.php index aeb1eb740a..938746037a 100644 --- a/CRM/Admin/Page/Tag.php +++ b/CRM/Admin/Page/Tag.php @@ -164,11 +164,10 @@ WHERE t2.id IS NULL {$reservedClause}"; $usedFor = CRM_Core_OptionGroup::values('tag_used_for'); - $select = "t1.id, t1.name, t1.description, t1.used_for, t1.is_tagset, - t1.is_reserved, t1.used_for"; - $query = "SELECT {$select}, t1.parent_id, t2.name as parent + $query = "SELECT t1.name, t1.id, t2.name as parent, t1.description, t1.used_for, t1.is_tagset, + t1.is_reserved, t1.parent_id, t1.used_for FROM civicrm_tag t1 LEFT JOIN civicrm_tag t2 ON t1.parent_id = t2.id - GROUP BY t1.parent_id, {$select}, t2.name"; + GROUP BY t1.parent_id, t1.id"; $tag = CRM_Core_DAO::executeQuery($query); $values = array(); diff --git a/CRM/Campaign/Form/Survey/Results.php b/CRM/Campaign/Form/Survey/Results.php index 864ce2a236..97cc04bbee 100644 --- a/CRM/Campaign/Form/Survey/Results.php +++ b/CRM/Campaign/Form/Survey/Results.php @@ -61,7 +61,7 @@ class CRM_Campaign_Form_Survey_Results extends CRM_Campaign_Form_Survey { $this->set('values', $this->_values); } - $query = "SELECT MAX(id) as id, title FROM civicrm_report_instance WHERE name = %1 GROUP BY id, title"; + $query = "SELECT MAX(id) as id, title FROM civicrm_report_instance WHERE name = %1 GROUP BY id"; $params = array(1 => array("survey_{$this->_surveyId}", 'String')); $result = CRM_Core_DAO::executeQuery($query, $params); if ($result->fetch()) { diff --git a/CRM/Case/BAO/Case.php b/CRM/Case/BAO/Case.php index a9a2b2d49c..3af84c56a4 100644 --- a/CRM/Case/BAO/Case.php +++ b/CRM/Case/BAO/Case.php @@ -757,7 +757,7 @@ AND civicrm_case.status_id != $closedId"; $myCaseWhereClause = " AND case_relationship.contact_id_b = {$userID}"; $myGroupByClause = " GROUP BY CONCAT(case_relationship.case_id,'-',case_relationship.contact_id_b)"; } - $myGroupByClause .= ", case_status.label, status_id, civicrm_case_type.title, case_type_id, case_relationship.contact_id_b"; + $myGroupByClause .= ", case_status.label, status_id, case_type_id"; // FIXME: This query could be a lot more efficient if it used COUNT() instead of returning all rows and then counting them with php $query = " @@ -878,33 +878,31 @@ SELECT case_status.label AS case_status, status_id, civicrm_case_type.title AS c $assigneeID = CRM_Utils_Array::key('Activity Assignees', $activityContacts); $sourceID = CRM_Utils_Array::key('Activity Source', $activityContacts); $targetID = CRM_Utils_Array::key('Activity Targets', $activityContacts); - $selectClause = array( - "ca.id AS id", - "ca.activity_type_id AS type", - "ca.activity_type_id AS activity_type_id", - "tcc.sort_name AS target_contact_name", - "tcc.id AS target_contact_id", - "scc.sort_name AS source_contact_name", - "scc.id AS source_contact_id", - "scc.sort_name AS source_contact_name", - "acc.sort_name AS assignee_contact_name", - "acc.id AS assignee_contact_id", - "ca.status_id AS status", - "ca.subject AS subject", - "ca.is_deleted AS deleted", - "ca.priority_id AS priority", - "ca.weight AS weight", - "DATE_FORMAT(IF(ca.activity_date_time < NOW() AND ca.status_id=ov.value, - ca.activity_date_time, - DATE_ADD(NOW(), INTERVAL 1 YEAR) - ), '%Y%m%d%H%i00') AS overdue_date", - "DATE_FORMAT(ca.activity_date_time, '%Y%m%d%H%i00') AS display_date", - ); // CRM-5081 - formatting the dates to omit seconds. // Note the 00 in the date format string is needed otherwise later on it thinks scheduled ones are overdue. $select = " - SELECT SQL_CALC_FOUND_ROWS COUNT(ca.id) AS ismultiple, " . implode(', ', $selectClause) . ", + SELECT SQL_CALC_FOUND_ROWS COUNT(ca.id) AS ismultiple, + ca.id AS id, + ca.activity_type_id AS type, + ca.activity_type_id AS activity_type_id, + tcc.sort_name AS target_contact_name, + tcc.id AS target_contact_id, + scc.sort_name AS source_contact_name, + scc.id AS source_contact_id, + acc.sort_name AS assignee_contact_name, + acc.id AS assignee_contact_id, + DATE_FORMAT( + IF(ca.activity_date_time < NOW() AND ca.status_id=ov.value, + ca.activity_date_time, + DATE_ADD(NOW(), INTERVAL 1 YEAR) + ), '%Y%m%d%H%i00') AS overdue_date, + DATE_FORMAT(ca.activity_date_time, '%Y%m%d%H%i00') AS display_date, + ca.status_id AS status, + ca.subject AS subject, + ca.is_deleted AS deleted, + ca.priority_id AS priority, + ca.weight AS weight, GROUP_CONCAT(ef.file_id) AS attachment_ids "; $from = " @@ -986,7 +984,8 @@ SELECT case_status.label AS case_status, status_id, civicrm_case_type.title AS c AND ca.activity_date_time <= '{$toActivityDate}'"; } - $groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($selectClause, 'ca.id'); + $groupBy = " + GROUP BY ca.id, tcc.id, scc.id, acc.id, ov.value"; $sortBy = CRM_Utils_Array::value('sortBy', $params); if (!$sortBy) { diff --git a/CRM/Contact/BAO/GroupContact.php b/CRM/Contact/BAO/GroupContact.php index 599aff3bf9..444ab8bb73 100644 --- a/CRM/Contact/BAO/GroupContact.php +++ b/CRM/Contact/BAO/GroupContact.php @@ -292,7 +292,7 @@ class CRM_Contact_BAO_GroupContact extends CRM_Contact_DAO_GroupContact { if ($visibility) { $where .= " AND civicrm_group.visibility != 'User and User Admin Only'"; } - $groupBy = " GROUP BY civicrm_group.id, civicrm_group.title"; + $groupBy = " GROUP BY civicrm_group.id"; $orderby = " ORDER BY civicrm_group.name"; $sql = $select . $from . $where . $groupBy . $orderby; diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 37289701c8..4cbf220535 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -4530,11 +4530,15 @@ civicrm_relationship.is_permission_a_b = 0 */ public static function getGroupByFromSelectColumns($selectClauses, $groupBy = NULL) { $groupBy = (array) $groupBy; + $mysqlVersion = CRM_Core_DAO::singleValueQuery('SELECT VERSION()'); $sqlMode = CRM_Core_DAO::singleValueQuery('SELECT @@sql_mode'); //return if ONLY_FULL_GROUP_BY is not enabled. - if (!empty($sqlMode) && !in_array('ONLY_FULL_GROUP_BY', explode(',', $sqlMode))) { - return " GROUP BY " . implode(', ', $groupBy); + if (version_compare($mysqlVersion, '5.7', '<') || !empty($sqlMode) && !in_array('ONLY_FULL_GROUP_BY', explode(',', $sqlMode))) { + if (!empty($groupBy)) { + return " GROUP BY " . implode(', ', $groupBy); + } + return ''; } $regexToExclude = '/(ROUND|AVG|COUNT|GROUP_CONCAT|SUM|MAX|MIN)\(/i'; @@ -4552,9 +4556,12 @@ civicrm_relationship.is_permission_a_b = 0 } else { list($selectColumn, $alias) = array_pad($aliasArray, 2, NULL); - $dateRegex = '/^(DATE_FORMAT|DATE_ADD)/i'; + $dateRegex = '/^(DATE_FORMAT|DATE_ADD|CASE)/i'; + $tableName = current(explode('.', $selectColumn)); + $primaryKey = "{$tableName}.id"; // exclude columns which are already included in groupBy and aggregate functions from select - if (!in_array($selectColumn, $groupBy) && preg_match($regexToExclude, trim($selectColumn)) !== 1) { + // CRM-18439 - Also exclude the columns which are functionally dependent on columns in $groupBy (MySQL 5.7+) + if (!in_array($selectColumn, $groupBy) && !in_array($primaryKey, $groupBy) && preg_match($regexToExclude, trim($selectColumn)) !== 1) { if (!empty($alias) && preg_match($dateRegex, trim($selectColumn))) { $groupBy[] = $alias; } @@ -4836,7 +4843,7 @@ SELECT COUNT( conts.total_amount ) as total_count, $innerQuery = "SELECT civicrm_contribution.total_amount, COUNT(civicrm_contribution.total_amount) as civicrm_contribution_total_amount_count, civicrm_contribution.currency $from $completedWhere"; $query = "$select FROM ( - $innerQuery GROUP BY civicrm_contribution.id, civicrm_contribution.total_amount, civicrm_contribution.currency + $innerQuery GROUP BY civicrm_contribution.id ) as conts GROUP BY currency"; @@ -4881,6 +4888,7 @@ SELECT COUNT( conts.total_amount ) as total_count, $query = " $select FROM ( SELECT civicrm_contribution_soft.amount as total_amount, civicrm_contribution_soft.currency $from $softCreditWhere + GROUP BY civicrm_contribution_soft.id ) as conts GROUP BY currency"; $dao = CRM_Core_DAO::executeQuery($query); @@ -4927,6 +4935,7 @@ SELECT COUNT( conts.total_amount ) as cancel_count, $query = "$select FROM ( SELECT civicrm_contribution.total_amount, civicrm_contribution.currency $from $where + GROUP BY civicrm_contribution.id ) as conts GROUP BY currency"; diff --git a/CRM/Contact/Form/Search/Custom/ContribSYBNT.php b/CRM/Contact/Form/Search/Custom/ContribSYBNT.php index 05a09a77ab..c0e33bf8b0 100644 --- a/CRM/Contact/Form/Search/Custom/ContribSYBNT.php +++ b/CRM/Contact/Form/Search/Custom/ContribSYBNT.php @@ -186,7 +186,7 @@ LEFT JOIN civicrm_contribution contrib_1 ON contrib_1.contact_id = contact_a.id WHERE contrib_1.contact_id = contact_a.id AND contrib_1.is_test = 0 $where -GROUP BY contact_a.id, contact_a.display_name +GROUP BY contact_a.id $having ORDER BY donation_amount desc "; diff --git a/CRM/Contact/Form/Search/Custom/ContributionAggregate.php b/CRM/Contact/Form/Search/Custom/ContributionAggregate.php index 23e3cac479..c5015b0a3a 100644 --- a/CRM/Contact/Form/Search/Custom/ContributionAggregate.php +++ b/CRM/Contact/Form/Search/Custom/ContributionAggregate.php @@ -148,7 +148,7 @@ count(contrib.id) AS donation_count SELECT $select FROM $from WHERE $where -GROUP BY contact_a.id, contact_a.sort_name +GROUP BY contact_a.id $having "; //for only contact ids ignore order. diff --git a/CRM/Contact/Form/Search/Custom/DateAdded.php b/CRM/Contact/Form/Search/Custom/DateAdded.php index b482f53a0e..2186d33860 100644 --- a/CRM/Contact/Form/Search/Custom/DateAdded.php +++ b/CRM/Contact/Form/Search/Custom/DateAdded.php @@ -163,7 +163,7 @@ class CRM_Contact_Form_Search_Custom_DateAdded extends CRM_Contact_Form_Search_C contact_a.contact_type as contact_type, contact_a.sort_name as sort_name, d.date_added as date_added"; - $groupBy = " GROUP BY contact_id, contact_a.contact_type, contact_a.sort_name, d.date_added "; + $groupBy = " GROUP BY contact_id "; } return $this->sql($selectClause, @@ -206,9 +206,9 @@ class CRM_Contact_Form_Search_Custom_DateAdded extends CRM_Contact_Form_Search_C ON (civicrm_contact.id = civicrm_log.entity_id AND civicrm_log.entity_table = 'civicrm_contact') GROUP BY - civicrm_contact.id, civicrm_log.modified_date + civicrm_contact.id HAVING - civicrm_log.modified_date >= '$startDate' + date_added >= '$startDate' $endDateFix"; if ($this->_debug > 0) { diff --git a/CRM/Contact/Form/Search/Custom/FullText/Activity.php b/CRM/Contact/Form/Search/Custom/FullText/Activity.php index e8386952d7..e839c0521b 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Activity.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Activity.php @@ -132,15 +132,14 @@ AND (ca.is_deleted = 0 OR ca.is_deleted IS NULL) * @param int $limit */ public function moveIDs($fromTable, $toTable, $limit) { - $select = 'ca.id, substr(ca.subject, 1, 50), substr(ca.details, 1, 250), - c1.id, c1.sort_name, cac.record_type_id, - ca.activity_type_id, - cca.case_id'; $sql = " INSERT INTO {$toTable} ( table_name, activity_id, subject, details, contact_id, sort_name, record_type, activity_type_id, case_id, client_id ) -SELECT 'Activity', {$select}, +SELECT 'Activity', ca.id, substr(ca.subject, 1, 50), substr(ca.details, 1, 250), + c1.id, c1.sort_name, cac.record_type_id, + ca.activity_type_id, + cca.case_id, ccc.contact_id as client_id FROM {$fromTable} eid INNER JOIN civicrm_activity ca ON ca.id = eid.entity_id @@ -149,7 +148,6 @@ INNER JOIN civicrm_contact c1 ON cac.contact_id = c1.id LEFT JOIN civicrm_case_activity cca ON cca.activity_id = ca.id LEFT JOIN civicrm_case_contact ccc ON ccc.case_id = cca.case_id WHERE (ca.is_deleted = 0 OR ca.is_deleted IS NULL) -GROUP BY {$select}, ccc.contact_id {$this->toLimit($limit)} "; CRM_Core_DAO::executeQuery($sql); diff --git a/CRM/Contact/Form/Search/Custom/Group.php b/CRM/Contact/Form/Search/Custom/Group.php index cb342654e5..8b889dfd0e 100644 --- a/CRM/Contact/Form/Search/Custom/Group.php +++ b/CRM/Contact/Form/Search/Custom/Group.php @@ -195,7 +195,7 @@ class CRM_Contact_Form_Search_Custom_Group extends CRM_Contact_Form_Search_Custo $where = $this->where($includeContactIDs); if (!$justIDs && !$this->_allSearch) { - $groupBy = " GROUP BY contact_a.id, contact_a.contact_type, contact_a.sort_name"; + $groupBy = " GROUP BY contact_a.id"; } else { // CRM-10850 diff --git a/CRM/Contact/Form/Search/Custom/TagContributions.php b/CRM/Contact/Form/Search/Custom/TagContributions.php index 3806b155a6..d7d153f01d 100644 --- a/CRM/Contact/Form/Search/Custom/TagContributions.php +++ b/CRM/Contact/Form/Search/Custom/TagContributions.php @@ -133,7 +133,7 @@ FROM $from WHERE $where "; - $sql .= " GROUP BY contact_a.id, contact_a.sort_name, contact_a.first_name, contact_a.last_name"; + $sql .= " GROUP BY contact_a.id"; // Define ORDER BY for query in $sort, with default value if (!empty($sort)) { if (is_string($sort)) { diff --git a/CRM/Contribute/BAO/Contribution.php b/CRM/Contribute/BAO/Contribution.php index 9e8f3a182d..a04b72a3bc 100644 --- a/CRM/Contribute/BAO/Contribution.php +++ b/CRM/Contribute/BAO/Contribution.php @@ -4005,12 +4005,14 @@ WHERE eft.financial_trxn_id IN ({$trxnId}, {$baseTrxnId['financialTrxnId']}) if ($getTrxnInfo && $baseTrxnId) { $arRelationTypeId = key(CRM_Core_PseudoConstant::accountOptionValues('account_relationship', NULL, " AND v.name LIKE 'Accounts Receivable Account is' ")); $arAccount = CRM_Contribute_PseudoConstant::financialAccountType($financialTypeId, $arRelationTypeId); - $select = "ft.total_amount, - ft.payment_instrument_id, - ft.trxn_date, ft.trxn_id, ft.status_id, ft.check_number, con.currency"; + // Need to exclude fee trxn rows so filter out rows where TO FINANCIAL ACCOUNT is expense account $sql = " - SELECT GROUP_CONCAT(fa.`name`) as financial_account, {$select} + SELECT GROUP_CONCAT(fa.`name`) as financial_account, + ft.total_amount, + ft.payment_instrument_id, + ft.trxn_date, ft.trxn_id, ft.status_id, ft.check_number, con.currency + FROM civicrm_contribution con LEFT JOIN civicrm_entity_financial_trxn eft ON (eft.entity_id = con.id AND eft.entity_table = 'civicrm_contribution') INNER JOIN civicrm_financial_trxn ft ON ft.id = eft.financial_trxn_id @@ -4019,9 +4021,9 @@ WHERE eft.financial_trxn_id IN ({$trxnId}, {$baseTrxnId['financialTrxnId']}) LEFT JOIN civicrm_financial_item fi ON fi.id = ef.entity_id INNER JOIN civicrm_financial_account fa ON fa.id = fi.financial_account_id - WHERE con.id = %1 AND ft.to_financial_account_id <> %3"; + WHERE con.id = %1 AND ft.to_financial_account_id <> %3 + GROUP BY ft.id"; - $sql .= " GROUP BY ft.id, {$select}"; $queryParams = array( 1 => array($contributionId, 'Integer'), 2 => array($feeFinancialAccount, 'Integer'), diff --git a/CRM/Event/BAO/Participant.php b/CRM/Event/BAO/Participant.php index bcf797f97e..49921a411e 100644 --- a/CRM/Event/BAO/Participant.php +++ b/CRM/Event/BAO/Participant.php @@ -1926,14 +1926,12 @@ WHERE (li.entity_table = 'civicrm_participant' AND li.entity_id = {$participantI "; CRM_Core_DAO::executeQuery($updateLineItem); - $fiColumns = "fi.contact_id, fi.description, fi.amount, fi.currency, - fi.financial_account_id, fi.status_id, fi.entity_table, fi.entity_id"; // gathering necessary info to record negative (deselected) financial_item $updateFinancialItem = " - SELECT {$fiColumns}, SUM(fi.amount) as differenceAmt, price_field_value_id, financial_type_id, tax_amount + SELECT fi.*, SUM(fi.amount) as differenceAmt, price_field_value_id, financial_type_id, tax_amount FROM civicrm_financial_item fi LEFT JOIN civicrm_line_item li ON (li.id = fi.entity_id AND fi.entity_table = 'civicrm_line_item') WHERE (li.entity_table = 'civicrm_participant' AND li.entity_id = {$participantId}) -GROUP BY li.entity_table, li.entity_id, price_field_value_id, fi.id, {$fiColumns} +GROUP BY li.entity_table, li.entity_id, price_field_value_id, fi.id "; $updateFinancialItemInfoDAO = CRM_Core_DAO::executeQuery($updateFinancialItem); $trxn = CRM_Core_BAO_FinancialTrxn::getFinancialTrxnId($contributionId, 'DESC', TRUE); @@ -1945,7 +1943,9 @@ GROUP BY li.entity_table, li.entity_id, price_field_value_id, fi.id, {$fiColumns while ($updateFinancialItemInfoDAO->fetch()) { $updateFinancialItemInfoValues = (array) $updateFinancialItemInfoDAO; $updateFinancialItemInfoValues['transaction_date'] = date('YmdHis'); - + // the below params are not needed + unset($updateFinancialItemInfoValues['id']); + unset($updateFinancialItemInfoValues['created_date']); // if not submitted and difference is not 0 make it negative if (!in_array($updateFinancialItemInfoValues['price_field_value_id'], $submittedFieldValueIds) && $updateFinancialItemInfoValues['differenceAmt'] != 0) { // INSERT negative financial_items diff --git a/CRM/Export/BAO/Export.php b/CRM/Export/BAO/Export.php index 3fd75e4a29..6077d1a83d 100644 --- a/CRM/Export/BAO/Export.php +++ b/CRM/Export/BAO/Export.php @@ -172,37 +172,40 @@ class CRM_Export_BAO_Export { * Group By Clause */ public static function getGroupBy($exportMode, $queryMode, $returnProperties, $query) { + $groupBy = ''; if (!empty($returnProperties['tags']) || !empty($returnProperties['groups']) || CRM_Utils_Array::value('notes', $returnProperties) || // CRM-9552 ($queryMode & CRM_Contact_BAO_Query::MODE_CONTACTS && $query->_useGroupBy) ) { - $groupBy = " GROUP BY contact_a.id"; + $groupBy = "contact_a.id"; } switch ($exportMode) { case CRM_Export_Form_Select::CONTRIBUTE_EXPORT: - $groupBy = 'GROUP BY civicrm_contribution.id'; + $groupBy = 'civicrm_contribution.id'; if (CRM_Contribute_BAO_Query::isSoftCreditOptionEnabled()) { // especial group by when soft credit columns are included - $groupBy = 'GROUP BY contribution_search_scredit_combined.id, contribution_search_scredit_combined.scredit_id'; + $groupBy = array('contribution_search_scredit_combined.id', 'contribution_search_scredit_combined.scredit_id'); } break; case CRM_Export_Form_Select::EVENT_EXPORT: - $groupBy = 'GROUP BY civicrm_participant.id'; + $groupBy = 'civicrm_participant.id'; break; case CRM_Export_Form_Select::MEMBER_EXPORT: - $groupBy = " GROUP BY civicrm_membership.id"; + $groupBy = "civicrm_membership.id"; break; } if ($queryMode & CRM_Contact_BAO_Query::MODE_ACTIVITY) { - $groupBy = " GROUP BY civicrm_activity.id "; + $groupBy = "civicrm_activity.id "; } - $groupBy = !empty($groupBy) ? $groupBy : ''; + if (!empty($groupBy)) { + $groupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($query->_select, $groupBy); + } return $groupBy; } diff --git a/CRM/Mailing/BAO/Mailing.php b/CRM/Mailing/BAO/Mailing.php index fa995fb55f..bf190fa049 100644 --- a/CRM/Mailing/BAO/Mailing.php +++ b/CRM/Mailing/BAO/Mailing.php @@ -542,7 +542,7 @@ WHERE mailing_id = %1 $groupBy = $groupJoin = ''; if ($dedupeEmail) { $groupJoin = " INNER JOIN civicrm_email e ON e.id = i.email_id"; - $groupBy = " GROUP BY e.email, i.contact_id, i.email_id "; + $groupBy = " GROUP BY e.email, i.contact_id "; } $sql = " @@ -2518,7 +2518,7 @@ LEFT JOIN civicrm_mailing_group g ON g.mailing_id = m.id // we only care about parent jobs, since that holds all the info on // the mailing $selectClause = implode(', ', $select); - $groupFromSelect = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($select); + $groupFromSelect = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($select, "$mailing.id"); $query = " SELECT {$selectClause}, MIN($job.scheduled_date) as scheduled_date, diff --git a/CRM/Mailing/BAO/MailingAB.php b/CRM/Mailing/BAO/MailingAB.php index 966bb7ea7a..570a3c168e 100644 --- a/CRM/Mailing/BAO/MailingAB.php +++ b/CRM/Mailing/BAO/MailingAB.php @@ -172,7 +172,8 @@ class CRM_Mailing_BAO_MailingAB extends CRM_Mailing_DAO_MailingAB { $query = "SELECT * FROM `civicrm_mailing_abtest` ab where (ab.mailing_id_a = %1 OR ab.mailing_id_b = %1 - OR ab.mailing_id_c = %1)"; + OR ab.mailing_id_c = %1) + GROUP BY ab.id"; $params = array(1 => array($mailingID, 'Integer')); $abTest = CRM_Core_DAO::executeQuery($query, $params); $abTest->fetch(); diff --git a/CRM/PCP/BAO/PCP.php b/CRM/PCP/BAO/PCP.php index d9d1656c2d..1c1ce614cf 100644 --- a/CRM/PCP/BAO/PCP.php +++ b/CRM/PCP/BAO/PCP.php @@ -189,12 +189,12 @@ AND target_entity_id NOT IN ( " . implode(',', $entityIds) . ") )"; } $query = " -SELECT block.target_entity_type, block.target_entity_id +SELECT * FROM civicrm_pcp_block block LEFT JOIN civicrm_pcp pcp ON pcp.pcp_block_id = block.id WHERE block.is_active = 1 {$clause} -GROUP BY block.id, block.target_entity_type, block.target_entity_id +GROUP BY block.id, pcp.id ORDER BY target_entity_type, target_entity_id "; $pcpBlockDao = CRM_Core_DAO::executeQuery($query); diff --git a/CRM/Pledge/Selector/Search.php b/CRM/Pledge/Selector/Search.php index 0a139d3f30..5882970c04 100644 --- a/CRM/Pledge/Selector/Search.php +++ b/CRM/Pledge/Selector/Search.php @@ -169,7 +169,7 @@ class CRM_Pledge_Selector_Search extends CRM_Core_Selector_Base { ); $this->_query->_distinctComponentClause = "civicrm_pledge.id"; - $this->_query->_groupByComponentClause = " GROUP BY civicrm_pledge.id, civicrm_pledge.financial_type_id "; + $this->_query->_groupByComponentClause = " GROUP BY civicrm_pledge.id "; } /** diff --git a/CRM/Price/BAO/PriceSet.php b/CRM/Price/BAO/PriceSet.php index 971dc21497..74e3e5da46 100644 --- a/CRM/Price/BAO/PriceSet.php +++ b/CRM/Price/BAO/PriceSet.php @@ -499,7 +499,7 @@ WHERE ct.id = cp.financial_type_id AND // Do not display any price sets $query .= " AND 0 "; } - $query .= " GROUP BY s.id, f.price_set_id, s.title"; + $query .= " GROUP BY s.id"; $dao = CRM_Core_DAO::executeQuery($query); while ($dao->fetch()) { $priceSets[$dao->id] = $dao->$column; diff --git a/CRM/Report/Form/Contribute/Detail.php b/CRM/Report/Form/Contribute/Detail.php index b20152aec9..a5f00c288a 100644 --- a/CRM/Report/Form/Contribute/Detail.php +++ b/CRM/Report/Form/Contribute/Detail.php @@ -593,8 +593,10 @@ GROUP BY {$this->_aliases['civicrm_contribution']}.currency"; $this->customDataFrom(); $select = str_ireplace('contribution_civireport.total_amount', 'contribution_soft_civireport.amount', $this->_select); - $this->_groupBy = str_ireplace('contribution_civireport.total_amount', 'contribution_soft_civireport.amount', $this->_groupBy); $select = str_ireplace("'Contribution' as", "'Soft Credit' as", $select); + if (!empty($this->_groupBy)) { + $this->_groupBy .= ', contribution_soft_civireport.amount'; + } // we inner join with temp1 to restrict soft contributions to those in temp1 table $sql = "{$select} {$this->_from} {$this->_where} {$this->_groupBy}"; $tempQuery = 'CREATE TEMPORARY TABLE civireport_contribution_detail_temp2 AS ' . $sql; -- 2.25.1