From d9d23a9195337a5e0947b0f8dd34286c75ae9942 Mon Sep 17 00:00:00 2001 From: Seamus Lee Date: Sat, 4 Jan 2020 07:50:40 +1100 Subject: [PATCH] dev/core#1143 Support MySQL 8 by wrapping the word groups around in backticks as it is now a reserved word --- CRM/Contact/BAO/Group.php | 42 +++++++++++++++++++-------------------- CRM/Contact/BAO/Query.php | 2 +- 2 files changed, 22 insertions(+), 22 deletions(-) diff --git a/CRM/Contact/BAO/Group.php b/CRM/Contact/BAO/Group.php index 12a024f367..215b999ddb 100644 --- a/CRM/Contact/BAO/Group.php +++ b/CRM/Contact/BAO/Group.php @@ -581,7 +581,7 @@ class CRM_Contact_BAO_Group extends CRM_Contact_DAO_Group { $groups = CRM_ACL_API::group(CRM_ACL_API::VIEW); if (!empty($groups)) { $groupList = implode(', ', array_values($groups)); - $clause = "groups.id IN ( $groupList ) "; + $clause = "`groups`.id IN ( $groupList ) "; } else { $clause = '1 = 0'; @@ -811,7 +811,7 @@ class CRM_Contact_BAO_Group extends CRM_Contact_DAO_Group { $limit = " LIMIT {$params['offset']}, {$params['rowCount']} "; } - $orderBy = ' ORDER BY groups.title asc'; + $orderBy = ' ORDER BY `groups`.title asc'; if (!empty($params['sort'])) { $orderBy = ' ORDER BY ' . CRM_Utils_Type::escape($params['sort'], 'String'); @@ -828,7 +828,7 @@ class CRM_Contact_BAO_Group extends CRM_Contact_DAO_Group { ) { $select = ", contact.display_name as org_name, contact.id as org_id"; $from = " LEFT JOIN civicrm_group_organization gOrg - ON gOrg.group_id = groups.id + ON gOrg.group_id = `groups`.id LEFT JOIN civicrm_contact contact ON contact.id = gOrg.organization_id "; @@ -842,10 +842,10 @@ class CRM_Contact_BAO_Group extends CRM_Contact_DAO_Group { } $query = " - SELECT groups.*, createdBy.sort_name as created_by {$select} - FROM civicrm_group groups + SELECT `groups`.*, createdBy.sort_name as created_by {$select} + FROM civicrm_group `groups` LEFT JOIN civicrm_contact createdBy - ON createdBy.id = groups.created_id + ON createdBy.id = `groups`.created_id {$from} WHERE $whereClause {$where} {$orderBy} @@ -1130,12 +1130,12 @@ WHERE id IN $groupIdString */ public static function getGroupCount(&$params) { $whereClause = self::whereClause($params, FALSE); - $query = "SELECT COUNT(*) FROM civicrm_group groups"; + $query = "SELECT COUNT(*) FROM civicrm_group `groups`"; if (!empty($params['created_by'])) { $query .= " INNER JOIN civicrm_contact createdBy - ON createdBy.id = groups.created_id"; + ON createdBy.id = `groups`.created_id"; } $query .= " WHERE {$whereClause}"; @@ -1154,7 +1154,7 @@ WHERE {$whereClause}"; $values = []; $title = CRM_Utils_Array::value('title', $params); if ($title) { - $clauses[] = "groups.title LIKE %1"; + $clauses[] = "`groups`.title LIKE %1"; if (strpos($title, '%') !== FALSE) { $params[1] = [$title, 'String', FALSE]; } @@ -1167,7 +1167,7 @@ WHERE {$whereClause}"; if ($groupType) { $types = explode(',', $groupType); if (!empty($types)) { - $clauses[] = 'groups.group_type LIKE %2'; + $clauses[] = '`groups`.group_type LIKE %2'; $typeString = CRM_Core_DAO::VALUE_SEPARATOR . implode(CRM_Core_DAO::VALUE_SEPARATOR, $types) . CRM_Core_DAO::VALUE_SEPARATOR; $params[2] = [$typeString, 'String', TRUE]; } @@ -1175,7 +1175,7 @@ WHERE {$whereClause}"; $visibility = CRM_Utils_Array::value('visibility', $params); if ($visibility) { - $clauses[] = 'groups.visibility = %3'; + $clauses[] = '`groups`.visibility = %3'; $params[3] = [$visibility, 'String']; } @@ -1183,30 +1183,30 @@ WHERE {$whereClause}"; if ($groupStatus) { switch ($groupStatus) { case 1: - $clauses[] = 'groups.is_active = 1'; + $clauses[] = '`groups`.is_active = 1'; $params[4] = [$groupStatus, 'Integer']; break; case 2: - $clauses[] = 'groups.is_active = 0'; + $clauses[] = '`groups`.is_active = 0'; $params[4] = [$groupStatus, 'Integer']; break; case 3: - $clauses[] = '(groups.is_active = 0 OR groups.is_active = 1 )'; + $clauses[] = '(`groups`.is_active = 0 OR `groups`.is_active = 1 )'; break; } } $parentsOnly = CRM_Utils_Array::value('parentsOnly', $params); if ($parentsOnly) { - $clauses[] = 'groups.parents IS NULL'; + $clauses[] = '`groups`.parents IS NULL'; } // only show child groups of a specific parent group $parent_id = CRM_Utils_Array::value('parent_id', $params); if ($parent_id) { - $clauses[] = 'groups.id IN (SELECT child_group_id FROM civicrm_group_nesting WHERE parent_group_id = %5)'; + $clauses[] = '`groups`.id IN (SELECT child_group_id FROM civicrm_group_nesting WHERE parent_group_id = %5)'; $params[5] = [$parent_id, 'Integer']; } @@ -1221,11 +1221,11 @@ WHERE {$whereClause}"; } if (empty($clauses)) { - $clauses[] = 'groups.is_active = 1'; + $clauses[] = '`groups`.is_active = 1'; } if ($excludeHidden) { - $clauses[] = 'groups.is_hidden = 0'; + $clauses[] = '`groups`.is_hidden = 0'; } $clauses[] = self::getPermissionClause(); @@ -1287,10 +1287,10 @@ WHERE {$whereClause}"; */ public function pagerAtoZ($whereClause, $whereParams) { $query = " - SELECT DISTINCT UPPER(LEFT(groups.title, 1)) as sort_name - FROM civicrm_group groups + SELECT DISTINCT UPPER(LEFT(`groups`.title, 1)) as sort_name + FROM civicrm_group `groups` WHERE $whereClause - ORDER BY LEFT(groups.title, 1) + ORDER BY LEFT(`groups`.title, 1) "; $dao = CRM_Core_DAO::executeQuery($query, $whereParams); diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index f0eaf25884..37c70b555f 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -946,7 +946,7 @@ class CRM_Contact_BAO_Query { GROUP_CONCAT(DISTINCT IF(civicrm_group_contact.status = 'Added', civicrm_group_contact.group_id, '')), GROUP_CONCAT(DISTINCT civicrm_group_contact_cache.group_id) ) - as groups"; + as `groups`"; $this->_element[$name] = 1; $this->_tables['civicrm_group_contact'] = 1; $this->_tables['civicrm_group_contact_cache'] = 1; -- 2.25.1