From e5cceea5938f416baac0235b6d8cabe3e3baef33 Mon Sep 17 00:00:00 2001 From: jitendrapurohit Date: Tue, 17 May 2016 12:05:18 +0530 Subject: [PATCH] CRM-18439 - Fix Queries to include Full Group By sql mode --- CRM/Activity/BAO/Activity.php | 13 ++-- CRM/Admin/Page/Tag.php | 7 +- CRM/Campaign/BAO/Survey.php | 3 +- CRM/Campaign/Form/Survey/Results.php | 2 +- CRM/Case/BAO/Case.php | 54 +++++++-------- CRM/Contact/BAO/Group.php | 1 - CRM/Contact/BAO/Query.php | 69 +++++++++++++++++-- .../Form/Search/Custom/ContribSYBNT.php | 2 +- .../Search/Custom/ContributionAggregate.php | 2 +- CRM/Contact/Form/Search/Custom/DateAdded.php | 6 +- .../Form/Search/Custom/EventAggregate.php | 5 +- .../Form/Search/Custom/FullText/Activity.php | 11 +-- CRM/Contact/Form/Search/Custom/Group.php | 2 +- .../Form/Search/Custom/TagContributions.php | 2 +- CRM/Contribute/BAO/Contribution.php | 15 ++-- CRM/Contribute/BAO/Query.php | 2 +- CRM/Event/BAO/Event.php | 1 - CRM/Mailing/BAO/Mailing.php | 29 ++++---- CRM/Mailing/BAO/MailingAB.php | 3 +- CRM/Mailing/PseudoConstant.php | 2 +- CRM/PCP/BAO/PCP.php | 4 +- CRM/Pledge/Selector/Search.php | 2 +- CRM/Price/BAO/PriceSet.php | 2 +- CRM/Utils/Weight.php | 2 +- 24 files changed, 146 insertions(+), 95 deletions(-) diff --git a/CRM/Activity/BAO/Activity.php b/CRM/Activity/BAO/Activity.php index 62f50c8290..a31dc4ab9c 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 tbl.activity_id "; + $groupBy = " GROUP BY " . implode(',', $insertValueSQL); if (!empty($input['sort'])) { if (is_a($input['sort'], 'CRM_Utils_Sort')) { @@ -791,20 +791,21 @@ 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. - $query = " -INSERT INTO {$activityContactTempTable} ( activity_id, contact_id, record_type_id, contact_name, is_deleted, counter ) -SELECT ac.activity_id, + $select = "ac.activity_id, ac.contact_id, ac.record_type_id, c.sort_name, - c.is_deleted, + c.is_deleted"; + $query = " +INSERT INTO {$activityContactTempTable} ( activity_id, contact_id, record_type_id, contact_name, is_deleted, counter ) +SELECT {$select}, 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 ac.activity_id +GROUP BY {$select} "; CRM_Core_DAO::executeQuery($query, $params); diff --git a/CRM/Admin/Page/Tag.php b/CRM/Admin/Page/Tag.php index 938746037a..aeb1eb740a 100644 --- a/CRM/Admin/Page/Tag.php +++ b/CRM/Admin/Page/Tag.php @@ -164,10 +164,11 @@ WHERE t2.id IS NULL {$reservedClause}"; $usedFor = CRM_Core_OptionGroup::values('tag_used_for'); - $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 + $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 FROM civicrm_tag t1 LEFT JOIN civicrm_tag t2 ON t1.parent_id = t2.id - GROUP BY t1.parent_id, t1.id"; + GROUP BY t1.parent_id, {$select}, t2.name"; $tag = CRM_Core_DAO::executeQuery($query); $values = array(); diff --git a/CRM/Campaign/BAO/Survey.php b/CRM/Campaign/BAO/Survey.php index ff49c73629..26226a474a 100644 --- a/CRM/Campaign/BAO/Survey.php +++ b/CRM/Campaign/BAO/Survey.php @@ -479,8 +479,7 @@ SELECT survey.id as id, $query = " SELECT contact.id as contactId, $selectClause FROM $fromClause - WHERE $whereClause -Group By contact.id"; + WHERE $whereClause"; $contact = CRM_Core_DAO::executeQuery($query); while ($contact->fetch()) { diff --git a/CRM/Campaign/Form/Survey/Results.php b/CRM/Campaign/Form/Survey/Results.php index 3021fe16e9..864ce2a236 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"; + $query = "SELECT MAX(id) as id, title FROM civicrm_report_instance WHERE name = %1 GROUP BY id, title"; $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 ad5b33a74d..5555623883 100644 --- a/CRM/Case/BAO/Case.php +++ b/CRM/Case/BAO/Case.php @@ -458,7 +458,6 @@ t_act.act_type AS case_activity_type "; FROM civicrm_view_case_activity_upcoming ORDER BY activity_date_time ASC, id ASC ) AS upcomingOrdered - GROUP BY case_id ) AS act LEFT JOIN civicrm_option_group aog ON aog.name='activity_type' LEFT JOIN civicrm_option_value aov ON ( aov.option_group_id = aog.id AND aov.value = act.activity_type_id ) @@ -478,7 +477,6 @@ t_act.act_type AS case_activity_type "; FROM civicrm_view_case_activity_recent ORDER BY activity_date_time DESC, id ASC ) AS recentOrdered - GROUP BY case_id ) AS act LEFT JOIN civicrm_option_group aog ON aog.name='activity_type' LEFT JOIN civicrm_option_value aov ON ( aov.option_group_id = aog.id AND aov.value = act.activity_type_id ) @@ -759,6 +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"; // 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 = " @@ -879,31 +878,33 @@ 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, - 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, + SELECT SQL_CALC_FOUND_ROWS COUNT(ca.id) AS ismultiple, " . implode(', ', $selectClause) . ", GROUP_CONCAT(ef.file_id) AS attachment_ids "; $from = " @@ -986,7 +987,7 @@ SELECT case_status.label AS case_status, status_id, civicrm_case_type.title AS c } $groupBy = " - GROUP BY ca.id "; + GROUP BY id " . CRM_Contact_BAO_Query::getGroupByFromSelectColumns($selectClause, 'ca.id'); $sortBy = CRM_Utils_Array::value('sortBy', $params); if (!$sortBy) { @@ -1246,8 +1247,7 @@ SELECT case_status.label AS case_status, status_id, civicrm_case_type.title AS c LEFT JOIN civicrm_email ce ON ce.contact_id = cc.id AND ce.is_primary= 1 - WHERE cr.case_id = %1 - GROUP BY cc.id'; + WHERE cr.case_id = %1'; $params = array(1 => array($caseID, 'Integer')); $dao = CRM_Core_DAO::executeQuery($query, $params); @@ -1732,7 +1732,7 @@ SELECT case_status.label AS case_status, status_id, civicrm_case_type.title AS c if (!empty($criteriaParams['activity_type_id'])) { $where .= " AND ca.activity_type_id = " . CRM_Utils_Type::escape($criteriaParams['activity_type_id'], 'Integer'); $where .= " AND ca.is_current_revision = 1"; - $groupBy .= " GROUP BY ca.activity_type_id"; + $groupBy .= " GROUP BY ca.activity_type_id, ca.id"; } if (!empty($criteriaParams['newest'])) { diff --git a/CRM/Contact/BAO/Group.php b/CRM/Contact/BAO/Group.php index df0ae58ae8..2a5b7e04a6 100644 --- a/CRM/Contact/BAO/Group.php +++ b/CRM/Contact/BAO/Group.php @@ -854,7 +854,6 @@ class CRM_Contact_BAO_Group extends CRM_Contact_DAO_Group { ON createdBy.id = groups.created_id {$from} WHERE $whereClause {$where} - GROUP BY groups.id {$orderBy} {$limit}"; diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 8eb6257ddf..ca8f0291c9 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -4335,6 +4335,7 @@ civicrm_relationship.is_permission_a_b = 0 // add group by if ($query->_useGroupBy) { $sql .= ' GROUP BY contact_a.id'; + $sql .= self::getGroupByFromSelectColumns($query->_select, 'contact_a.id'); } if (!empty($sort)) { $sort = CRM_Utils_Type::escape($sort, 'String'); @@ -4520,6 +4521,51 @@ civicrm_relationship.is_permission_a_b = 0 } } + /** + * Include Select columns in groupBy clause. + * + * @param array $selectArray + * @param array $groupBy - Columns already included in GROUP By clause. + * + * @return string + */ + public static function getGroupByFromSelectColumns($selectClauses, $groupBy = NULL) { + $groupBy = (array) $groupBy; + $regexToExclude = '/(COALESCE|ROUND|AVG|COUNT|GROUP_CONCAT|SUM|MAX|MIN)\(/i'; + foreach ($selectClauses as $key => $val) { + $aliasArray = preg_split('/ as /i', $val); + // if more than 1 alias we need to split by ','. + if (count($aliasArray) > 2) { + $aliasArray = preg_split('/,/', $val); + foreach ($aliasArray as $key => $value) { + $alias = current(preg_split('/ as /i', $value)); + if (!in_array($alias, $groupBy) && preg_match($regexToExclude, trim($alias)) !== 1) { + $selectColAlias[] = $alias; + } + } + } + else { + list($selectColumn, $alias) = array_pad($aliasArray, 2, NULL); + $dateRegex = '/^(DATE_FORMAT|DATE_ADD)/i'; + // exclude columns which are alreagy included in groupBy and aggregate functions from select + if (!in_array($selectColumn, $groupBy) && preg_match($regexToExclude, trim($selectColumn)) !== 1) { + if (!empty($alias) && preg_match($dateRegex, trim($selectColumn))) { + $selectColAlias[] = $alias; + } + else { + $selectColAlias[] = $selectColumn; + } + } + } + } + + if (!empty($selectColAlias)) { + $groupStmt = implode(', ', $selectColAlias); + return empty($groupBy) ? $groupStmt : ", {$groupStmt}"; + } + return ''; + } + /** * Create and query the db for an contact search. * @@ -4584,12 +4630,16 @@ civicrm_relationship.is_permission_a_b = 0 if (!$count) { if (isset($this->_groupByComponentClause)) { $groupBy = $this->_groupByComponentClause; + $groupCols = preg_replace('/^' . preg_quote('GROUP BY ', '/') . '/', '', $this->_groupByComponentClause); + $groupByCol = explode(', ', $groupCols); } elseif ($this->_useGroupBy) { + $groupByCol = 'contact_a.id'; $groupBy = ' GROUP BY contact_a.id'; } } if ($this->_mode & CRM_Contact_BAO_Query::MODE_ACTIVITY && (!$count)) { + $groupByCol = 'civicrm_activity.id'; $groupBy = 'GROUP BY civicrm_activity.id '; } @@ -4612,6 +4662,10 @@ civicrm_relationship.is_permission_a_b = 0 list($select, $from, $where, $having) = $this->query($count, $sortByChar, $groupContacts, $onlyDeleted); + if (!empty($groupBy)) { + $groupBy .= self::getGroupByFromSelectColumns($this->_select, $groupByCol); + } + if ($additionalWhereClause) { $where = $where . ' AND ' . $additionalWhereClause; } @@ -4670,6 +4724,7 @@ civicrm_relationship.is_permission_a_b = 0 $from = " FROM civicrm_prevnext_cache pnc INNER JOIN civicrm_contact contact_a ON contact_a.id = pnc.entity_id1 AND pnc.cacheKey = '$cacheKey' " . substr($from, 31); $order = " ORDER BY pnc.id"; $groupBy = " GROUP BY contact_a.id"; + $groupBy .= self::getGroupByFromSelectColumns($this->_select, 'contact_a.id'); $limit = " LIMIT $offset, $rowCount"; $query = "$select $from $where $groupBy $order $limit"; @@ -4749,7 +4804,6 @@ civicrm_relationship.is_permission_a_b = 0 SELECT COUNT( conts.total_amount ) as total_count, SUM( conts.total_amount ) as total_amount, AVG( conts.total_amount ) as total_avg, - conts.total_amount as amount, conts.currency as currency"; if ($this->_permissionWhereClause) { $where .= " AND " . $this->_permissionWhereClause; @@ -4777,7 +4831,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 + $innerQuery GROUP BY civicrm_contribution.id, civicrm_contribution.total_amount, civicrm_contribution.currency ) as conts GROUP BY currency"; @@ -4793,9 +4847,12 @@ SELECT COUNT( conts.total_amount ) as total_count, $orderBy = 'ORDER BY civicrm_contribution_total_amount_count DESC'; $groupBy = 'GROUP BY currency, civicrm_contribution.total_amount'; - $modeSQL = "$select, conts.civicrm_contribution_total_amount_count as civicrm_contribution_total_amount_count FROM ($innerQuery - $groupBy $orderBy) as conts - GROUP BY currency"; + $modeSQL = "$select, SUBSTRING_INDEX(GROUP_CONCAT(conts.total_amount + ORDER BY conts.civicrm_contribution_total_amount_count DESC SEPARATOR ';'), ';', 1) as amount, + MAX(conts.civicrm_contribution_total_amount_count) as civicrm_contribution_total_amount_count + FROM ($innerQuery + $groupBy $orderBy) as conts + GROUP BY currency"; $summary['total']['mode'] = CRM_Contribute_BAO_Contribution::computeStats('mode', $modeSQL); @@ -4819,7 +4876,6 @@ 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); @@ -4866,7 +4922,6 @@ 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 c0e33bf8b0..05a09a77ab 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 +GROUP BY contact_a.id, contact_a.display_name $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 c5015b0a3a..23e3cac479 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 +GROUP BY contact_a.id, contact_a.sort_name $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 2186d33860..b482f53a0e 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 "; + $groupBy = " GROUP BY contact_id, contact_a.contact_type, contact_a.sort_name, d.date_added "; } 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_contact.id, civicrm_log.modified_date HAVING - date_added >= '$startDate' + civicrm_log.modified_date >= '$startDate' $endDateFix"; if ($this->_debug > 0) { diff --git a/CRM/Contact/Form/Search/Custom/EventAggregate.php b/CRM/Contact/Form/Search/Custom/EventAggregate.php index 199f93564f..e2bb5aa3b7 100644 --- a/CRM/Contact/Form/Search/Custom/EventAggregate.php +++ b/CRM/Contact/Form/Search/Custom/EventAggregate.php @@ -159,10 +159,11 @@ class CRM_Contact_Form_Search_Custom_EventAggregate extends CRM_Contact_Form_Sea } $where = $this->where(); + $groupFromSelect = "civicrm_option_value.label, civicrm_contribution.payment_instrument_id"; - $groupBy = "event_id"; + $groupBy = "event_id, event_type_id, {$groupFromSelect}"; if (!empty($this->_formValues['event_type_id'])) { - $groupBy = "event_type_id"; + $groupBy = "event_type_id, event_id, {$groupFromSelect}"; } $sql = " diff --git a/CRM/Contact/Form/Search/Custom/FullText/Activity.php b/CRM/Contact/Form/Search/Custom/FullText/Activity.php index 2e30090b51..e8386952d7 100644 --- a/CRM/Contact/Form/Search/Custom/FullText/Activity.php +++ b/CRM/Contact/Form/Search/Custom/FullText/Activity.php @@ -132,14 +132,15 @@ 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', 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, +SELECT 'Activity', {$select}, ccc.contact_id as client_id FROM {$fromTable} eid INNER JOIN civicrm_activity ca ON ca.id = eid.entity_id @@ -148,7 +149,7 @@ 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 ca.id +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 8b889dfd0e..cb342654e5 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"; + $groupBy = " GROUP BY contact_a.id, contact_a.contact_type, contact_a.sort_name"; } else { // CRM-10850 diff --git a/CRM/Contact/Form/Search/Custom/TagContributions.php b/CRM/Contact/Form/Search/Custom/TagContributions.php index d7d153f01d..3806b155a6 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"; + $sql .= " GROUP BY contact_a.id, contact_a.sort_name, contact_a.first_name, contact_a.last_name"; // 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 0f54881f0b..9e8f3a182d 100644 --- a/CRM/Contribute/BAO/Contribution.php +++ b/CRM/Contribute/BAO/Contribution.php @@ -4005,13 +4005,12 @@ 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, - ft.total_amount, - ft.payment_instrument_id, - ft.trxn_date, ft.trxn_id, ft.status_id, ft.check_number, con.currency - + SELECT GROUP_CONCAT(fa.`name`) as financial_account, {$select} 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 @@ -4020,9 +4019,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 - GROUP BY ft.id - "; + WHERE con.id = %1 AND ft.to_financial_account_id <> %3"; + + $sql .= " GROUP BY ft.id, {$select}"; $queryParams = array( 1 => array($contributionId, 'Integer'), 2 => array($feeFinancialAccount, 'Integer'), diff --git a/CRM/Contribute/BAO/Query.php b/CRM/Contribute/BAO/Query.php index eb0314e60e..60769cedc8 100644 --- a/CRM/Contribute/BAO/Query.php +++ b/CRM/Contribute/BAO/Query.php @@ -825,7 +825,7 @@ class CRM_Contribute_BAO_Query { SELECT con.id as id, con.contact_id, cso.id as filter_id, NULL as scredit_id FROM civicrm_contribution con LEFT JOIN civicrm_contribution_soft cso ON con.id = cso.contribution_id - GROUP BY id, contact_id, scredit_id + GROUP BY id, contact_id, scredit_id, cso.id UNION ALL SELECT scredit.contribution_id as id, scredit.contact_id, scredit.id as filter_id, scredit.id as scredit_id FROM civicrm_contribution_soft as scredit"; diff --git a/CRM/Event/BAO/Event.php b/CRM/Event/BAO/Event.php index d5419f8458..a68f2305e4 100644 --- a/CRM/Event/BAO/Event.php +++ b/CRM/Event/BAO/Event.php @@ -416,7 +416,6 @@ WHERE civicrm_event.is_active = 1 AND ( civicrm_event.is_template IS NULL OR civicrm_event.is_template = 0) AND civicrm_event.start_date >= DATE_SUB( NOW(), INTERVAL 7 day ) $validEventIDs -GROUP BY civicrm_event.id ORDER BY civicrm_event.start_date ASC $event_summary_limit "; diff --git a/CRM/Mailing/BAO/Mailing.php b/CRM/Mailing/BAO/Mailing.php index e09c1fe591..012a2338c4 100644 --- a/CRM/Mailing/BAO/Mailing.php +++ b/CRM/Mailing/BAO/Mailing.php @@ -539,7 +539,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 "; + $groupBy = " GROUP BY e.email, i.contact_id, i.email_id "; } $sql = " @@ -2466,7 +2466,7 @@ LEFT JOIN civicrm_mailing_group g ON g.mailing_id = m.id $mailings = implode(',', $mailingIDs); $mailingQuery = " SELECT DISTINCT ( m.id ) as id - FROM civicrm_mailing m + FROM civicrm_mailing m LEFT JOIN civicrm_mailing_group g ON g.mailing_id = m.id WHERE g.entity_table like 'civicrm_mailing%' AND g.entity_id IN ($mailings)"; $mailingDao = CRM_Core_DAO::executeQuery($mailingQuery); @@ -2505,31 +2505,28 @@ LEFT JOIN civicrm_mailing_group g ON g.mailing_id = m.id //get all campaigns. $allCampaigns = CRM_Campaign_BAO_Campaign::getCampaigns(NULL, NULL, FALSE, FALSE, FALSE, TRUE); + $select = array( + "$mailing.id", "$mailing.name", "$job.status", + "$mailing.approval_status_id", "createdContact.sort_name as created_by", "scheduledContact.sort_name as scheduled_by", + "$mailing.created_id as created_id", "$mailing.scheduled_id as scheduled_id", "$mailing.is_archived as archived", + "$mailing.created_date as created_date", "campaign_id", "$mailing.sms_provider_id as sms_provider_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); $query = " - SELECT $mailing.id, - $mailing.name, - $job.status, - $mailing.approval_status_id, + SELECT {$selectClause}, MIN($job.scheduled_date) as scheduled_date, MIN($job.start_date) as start_date, - MAX($job.end_date) as end_date, - createdContact.sort_name as created_by, - scheduledContact.sort_name as scheduled_by, - $mailing.created_id as created_id, - $mailing.scheduled_id as scheduled_id, - $mailing.is_archived as archived, - $mailing.created_date as created_date, - campaign_id, - $mailing.sms_provider_id as sms_provider_id + MAX($job.end_date) as end_date FROM $mailing LEFT JOIN $job ON ( $job.mailing_id = $mailing.id AND $job.is_test = 0 AND $job.parent_id IS NULL ) LEFT JOIN civicrm_contact createdContact ON ( civicrm_mailing.created_id = createdContact.id ) LEFT JOIN civicrm_contact scheduledContact ON ( civicrm_mailing.scheduled_id = scheduledContact.id ) WHERE $mailingACL $additionalClause - GROUP BY $mailing.id "; + GROUP BY {$groupFromSelect}"; if ($sort) { $orderBy = trim($sort->orderBy()); diff --git a/CRM/Mailing/BAO/MailingAB.php b/CRM/Mailing/BAO/MailingAB.php index 570a3c168e..966bb7ea7a 100644 --- a/CRM/Mailing/BAO/MailingAB.php +++ b/CRM/Mailing/BAO/MailingAB.php @@ -172,8 +172,7 @@ 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) - GROUP BY ab.id"; + OR ab.mailing_id_c = %1)"; $params = array(1 => array($mailingID, 'Integer')); $abTest = CRM_Core_DAO::executeQuery($query, $params); $abTest->fetch(); diff --git a/CRM/Mailing/PseudoConstant.php b/CRM/Mailing/PseudoConstant.php index e92e46e869..9099a9e321 100644 --- a/CRM/Mailing/PseudoConstant.php +++ b/CRM/Mailing/PseudoConstant.php @@ -196,7 +196,7 @@ class CRM_Mailing_PseudoConstant extends CRM_Core_PseudoConstant { FROM civicrm_mailing_component WHERE is_active = 1 AND is_default = 1 - GROUP BY component_type"; + GROUP BY component_type, id"; $dao = CRM_Core_DAO::executeQuery($queryDefaultComponents); diff --git a/CRM/PCP/BAO/PCP.php b/CRM/PCP/BAO/PCP.php index 417aa27936..d9d1656c2d 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 * +SELECT block.target_entity_type, block.target_entity_id 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 +GROUP BY block.id, block.target_entity_type, block.target_entity_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 5882970c04..0a139d3f30 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 "; + $this->_query->_groupByComponentClause = " GROUP BY civicrm_pledge.id, civicrm_pledge.financial_type_id "; } /** diff --git a/CRM/Price/BAO/PriceSet.php b/CRM/Price/BAO/PriceSet.php index 74e3e5da46..971dc21497 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"; + $query .= " GROUP BY s.id, f.price_set_id, s.title"; $dao = CRM_Core_DAO::executeQuery($query); while ($dao->fetch()) { $priceSets[$dao->id] = $dao->$column; diff --git a/CRM/Utils/Weight.php b/CRM/Utils/Weight.php index 352b6616e2..8da6d84a15 100644 --- a/CRM/Utils/Weight.php +++ b/CRM/Utils/Weight.php @@ -53,7 +53,7 @@ class CRM_Utils_Weight { */ public static function correctDuplicateWeights($daoName, $fieldValues = NULL, $weightField = 'weight') { $selectField = "MIN(id) AS dupeId, count(id) as dupeCount, $weightField as dupeWeight"; - $groupBy = "$weightField having dupeCount>1"; + $groupBy = "$weightField having count(id)>1"; $minDupeID = CRM_Utils_Weight::query('SELECT', $daoName, $fieldValues, $selectField, NULL, NULL, $groupBy); -- 2.25.1