From 0ee5581ea0632c777e1267154eb6aba710320b26 Mon Sep 17 00:00:00 2001 From: jitendrapurohit Date: Mon, 21 Nov 2016 15:44:23 +0530 Subject: [PATCH] CRM-19662 - Fix remaining full group by clause --- CRM/Contact/Form/Task.php | 2 +- CRM/Contribute/BAO/ContributionRecur.php | 3 +-- CRM/Event/Page/AJAX.php | 4 ++-- CRM/Export/BAO/Export.php | 2 +- CRM/Mailing/BAO/Mailing.php | 9 +++++++-- CRM/Mailing/Event/BAO/Bounce.php | 2 +- CRM/Mailing/Event/BAO/Delivered.php | 2 +- CRM/Mailing/Event/BAO/Forward.php | 4 ++-- CRM/Mailing/Event/BAO/Opened.php | 2 +- CRM/Mailing/Event/BAO/Reply.php | 2 +- CRM/Mailing/Event/BAO/TrackableURLOpen.php | 2 +- CRM/Mailing/Event/BAO/Unsubscribe.php | 2 +- 12 files changed, 20 insertions(+), 16 deletions(-) diff --git a/CRM/Contact/Form/Task.php b/CRM/Contact/Form/Task.php index 8f3771cd76..4fbedd6d18 100644 --- a/CRM/Contact/Form/Task.php +++ b/CRM/Contact/Form/Task.php @@ -428,7 +428,7 @@ class CRM_Contact_Form_Task extends CRM_Core_Form { $today = date('Ymd'); $relationActive = " AND (crel.is_active = 1 AND ( crel.end_date is NULL OR crel.end_date >= {$today} ) )"; $relationWhere = " WHERE contact_household.is_deleted = 0 AND crel.{$contactA} IN ( {$relID} ) {$relationActive}"; - $relationGroupBy = " GROUP BY crel.{$contactA}"; + $relationGroupBy = " GROUP BY crel.{$contactA}, contact_household.id"; $relationQueryString = "$relationSelect $relationFrom $relationWhere $relationGroupBy"; $householdsDAO = CRM_Core_DAO::executeQuery($relationQueryString); diff --git a/CRM/Contribute/BAO/ContributionRecur.php b/CRM/Contribute/BAO/ContributionRecur.php index 276c7677d0..937f8af301 100644 --- a/CRM/Contribute/BAO/ContributionRecur.php +++ b/CRM/Contribute/BAO/ContributionRecur.php @@ -395,8 +395,7 @@ SELECT rec.id as recur_id, FROM civicrm_contribution_recur rec LEFT JOIN civicrm_contribution con ON ( con.contribution_recur_id = rec.id ) LEFT JOIN civicrm_membership_payment mp ON ( mp.contribution_id = con.id ) - WHERE rec.id = %1 - GROUP BY rec.id"; + WHERE rec.id = %1"; } elseif ($entity == 'contribution') { $sql .= " diff --git a/CRM/Event/Page/AJAX.php b/CRM/Event/Page/AJAX.php index 60718f37a5..7edc9c09ed 100644 --- a/CRM/Event/Page/AJAX.php +++ b/CRM/Event/Page/AJAX.php @@ -57,8 +57,8 @@ cv.label FROM civicrm_price_field_value cv LEFT JOIN civicrm_price_field cf ON cv.price_field_id = cf.id LEFT JOIN civicrm_price_set_entity ce ON ce.price_set_id = cf.price_set_id -WHERE ce.entity_table = 'civicrm_event' AND {$whereClause} -GROUP BY cv.label"; +WHERE ce.entity_table = 'civicrm_event' AND {$whereClause}"; + $dao = CRM_Core_DAO::executeQuery($query); $results = array(); while ($dao->fetch()) { diff --git a/CRM/Export/BAO/Export.php b/CRM/Export/BAO/Export.php index 78db170108..cd05826b1a 100644 --- a/CRM/Export/BAO/Export.php +++ b/CRM/Export/BAO/Export.php @@ -645,7 +645,7 @@ INSERT INTO {$componentTable} SELECT distinct gc.contact_id FROM civicrm_group_c $today = date('Ymd'); $relationActive = " AND (crel.is_active = 1 AND ( crel.end_date is NULL OR crel.end_date >= {$today} ) )"; $relationWhere = " WHERE contact_a.is_deleted = 0 {$relationshipClause} {$relationActive}"; - $relationGroupBy = " GROUP BY crel.{$contactA}"; + $relationGroupBy = CRM_Contact_BAO_Query::getGroupByFromSelectColumns($relationQuery[$rel]->_select, "crel.{$contactA}"); $relationSelect = "{$relationSelect}, {$contactA} as refContact "; $relationQueryString = "$relationSelect $relationFrom $relationWhere $relationHaving $relationGroupBy"; diff --git a/CRM/Mailing/BAO/Mailing.php b/CRM/Mailing/BAO/Mailing.php index a04124a476..19b7e774f1 100644 --- a/CRM/Mailing/BAO/Mailing.php +++ b/CRM/Mailing/BAO/Mailing.php @@ -538,23 +538,28 @@ WHERE mailing_id = %1 $params = array(1 => array($mailing_id, 'Integer')); CRM_Core_DAO::executeQuery($sql, $params); + $selectClause = array('%1', 'i.contact_id', "i.{$tempColumn}"); + $select = "SELECT " . implode(', ', $selectClause); // CRM-3975 $groupBy = $groupJoin = ''; + $orderBy = "i.contact_id, i.{$tempColumn}"; if ($dedupeEmail) { + $orderBy = "MIN(i.contact_id), MIN(i.{$tempColumn})"; $groupJoin = " INNER JOIN civicrm_email e ON e.id = i.email_id"; $groupBy = " GROUP BY e.email "; + $select = CRM_Contact_BAO_Query::appendAnyValueToSelect($selectClause, 'e.email'); } $sql = " INSERT INTO civicrm_mailing_recipients ( mailing_id, contact_id, {$tempColumn} ) -SELECT %1, i.contact_id, i.{$tempColumn} +{$select} FROM civicrm_contact contact_a INNER JOIN I_$job_id i ON contact_a.id = i.contact_id $groupJoin {$aclFrom} {$aclWhere} $groupBy -ORDER BY i.contact_id, i.{$tempColumn} +ORDER BY {$orderBy} "; CRM_Core_DAO::executeQuery($sql, $params); diff --git a/CRM/Mailing/Event/BAO/Bounce.php b/CRM/Mailing/Event/BAO/Bounce.php index d0e005a3c0..84cdd4b277 100644 --- a/CRM/Mailing/Event/BAO/Bounce.php +++ b/CRM/Mailing/Event/BAO/Bounce.php @@ -238,7 +238,7 @@ class CRM_Mailing_Event_BAO_Bounce extends CRM_Mailing_Event_DAO_Bounce { } if ($is_distinct) { - $query .= " GROUP BY $queue.id "; + $query .= " GROUP BY $queue.id, $bounce.time_stamp, $bounce.bounce_reason, $bounceType.name "; } $orderBy = "sort_name ASC, {$bounce}.time_stamp DESC"; diff --git a/CRM/Mailing/Event/BAO/Delivered.php b/CRM/Mailing/Event/BAO/Delivered.php index ee20d437b1..17bcaf0300 100644 --- a/CRM/Mailing/Event/BAO/Delivered.php +++ b/CRM/Mailing/Event/BAO/Delivered.php @@ -200,7 +200,7 @@ class CRM_Mailing_Event_BAO_Delivered extends CRM_Mailing_Event_DAO_Delivered { } if ($is_distinct) { - $query .= " GROUP BY $queue.id "; + $query .= " GROUP BY $queue.id, $delivered.id"; } $orderBy = "sort_name ASC, {$delivered}.time_stamp DESC"; diff --git a/CRM/Mailing/Event/BAO/Forward.php b/CRM/Mailing/Event/BAO/Forward.php index ea3faed8e5..3061d271ad 100644 --- a/CRM/Mailing/Event/BAO/Forward.php +++ b/CRM/Mailing/Event/BAO/Forward.php @@ -339,10 +339,10 @@ class CRM_Mailing_Event_BAO_Forward extends CRM_Mailing_Event_DAO_Forward { } if ($is_distinct) { - $query .= " GROUP BY $queue.id "; + $query .= " GROUP BY $queue.id, dest_contact.id, dest_email.email, $forward.time_stamp "; } - $orderBy = "sort_name ASC, {$forward}.time_stamp DESC"; + $orderBy = "$contact.sort_name ASC, {$forward}.time_stamp DESC"; if ($sort) { if (is_string($sort)) { $sort = CRM_Utils_Type::escape($sort, 'String'); diff --git a/CRM/Mailing/Event/BAO/Opened.php b/CRM/Mailing/Event/BAO/Opened.php index 451e8b9b90..3e8eda136c 100644 --- a/CRM/Mailing/Event/BAO/Opened.php +++ b/CRM/Mailing/Event/BAO/Opened.php @@ -267,7 +267,7 @@ class CRM_Mailing_Event_BAO_Opened extends CRM_Mailing_Event_DAO_Opened { } if ($is_distinct) { - $query .= " GROUP BY $queue.id "; + $query .= " GROUP BY $queue.id, $open.time_stamp "; } $orderBy = "sort_name ASC, {$open}.time_stamp DESC"; diff --git a/CRM/Mailing/Event/BAO/Reply.php b/CRM/Mailing/Event/BAO/Reply.php index 6a8e7a8c16..57a5da2b27 100644 --- a/CRM/Mailing/Event/BAO/Reply.php +++ b/CRM/Mailing/Event/BAO/Reply.php @@ -403,7 +403,7 @@ class CRM_Mailing_Event_BAO_Reply extends CRM_Mailing_Event_DAO_Reply { } if ($is_distinct) { - $query .= " GROUP BY $queue.id "; + $query .= " GROUP BY $queue.id, $contact.id, $reply.time_stamp "; } $orderBy = "sort_name ASC, {$reply}.time_stamp DESC"; diff --git a/CRM/Mailing/Event/BAO/TrackableURLOpen.php b/CRM/Mailing/Event/BAO/TrackableURLOpen.php index 94e05b2e7c..1b765e5740 100644 --- a/CRM/Mailing/Event/BAO/TrackableURLOpen.php +++ b/CRM/Mailing/Event/BAO/TrackableURLOpen.php @@ -337,7 +337,7 @@ class CRM_Mailing_Event_BAO_TrackableURLOpen extends CRM_Mailing_Event_DAO_Track } if ($is_distinct) { - $query .= " GROUP BY $queue.id "; + $query .= " GROUP BY $queue.id, $click.time_stamp, $url.url "; } $orderBy = "sort_name ASC, {$click}.time_stamp DESC"; diff --git a/CRM/Mailing/Event/BAO/Unsubscribe.php b/CRM/Mailing/Event/BAO/Unsubscribe.php index 71171d1f1b..290b3e90ae 100644 --- a/CRM/Mailing/Event/BAO/Unsubscribe.php +++ b/CRM/Mailing/Event/BAO/Unsubscribe.php @@ -549,7 +549,7 @@ WHERE email = %2 } if ($is_distinct) { - $query .= " GROUP BY $queue.id "; + $query .= " GROUP BY $queue.id, $unsub.time_stamp, $unsub.org_unsubscribe"; } $orderBy = "sort_name ASC, {$unsub}.time_stamp DESC"; -- 2.25.1