From 52cda5dc04b89fb809b90a52b688c015d68bdb7d Mon Sep 17 00:00:00 2001 From: "Matthew Wire (MJW Consulting)" Date: Thu, 28 Feb 2019 21:25:17 +0000 Subject: [PATCH] Move UPPER() from sql to php domain --- CRM/Activity/Selector/Search.php | 2 +- CRM/Case/Selector/Search.php | 2 +- CRM/Contact/BAO/Query.php | 30 ++++++++++++++++++++---------- CRM/Contact/Selector.php | 2 +- CRM/Contribute/Selector/Search.php | 2 +- CRM/Event/Selector/Search.php | 2 +- CRM/Mailing/Selector/Search.php | 2 +- CRM/Member/Selector/Search.php | 2 +- CRM/Utils/PagerAToZ.php | 2 +- 9 files changed, 28 insertions(+), 18 deletions(-) diff --git a/CRM/Activity/Selector/Search.php b/CRM/Activity/Selector/Search.php index 8fb4278334..0d9e36c5d1 100644 --- a/CRM/Activity/Selector/Search.php +++ b/CRM/Activity/Selector/Search.php @@ -431,7 +431,7 @@ class CRM_Activity_Selector_Search extends CRM_Core_Selector_Base implements CRM * @return mixed */ public function alphabetQuery() { - return $this->_query->searchQuery(NULL, NULL, NULL, FALSE, FALSE, TRUE); + return $this->_query->alphabetQuery(); } /** diff --git a/CRM/Case/Selector/Search.php b/CRM/Case/Selector/Search.php index 5bba715cec..d068a128af 100644 --- a/CRM/Case/Selector/Search.php +++ b/CRM/Case/Selector/Search.php @@ -456,7 +456,7 @@ class CRM_Case_Selector_Search extends CRM_Core_Selector_Base { * @return mixed */ public function alphabetQuery() { - return $this->_query->searchQuery(NULL, NULL, NULL, FALSE, FALSE, TRUE); + return $this->_query->alphabetQuery(); } /** diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php index 9008ac6a09..0781e1695b 100644 --- a/CRM/Contact/BAO/Query.php +++ b/CRM/Contact/BAO/Query.php @@ -1384,7 +1384,7 @@ class CRM_Contact_BAO_Query { } } elseif ($sortByChar) { - $select = 'SELECT DISTINCT UPPER(LEFT(contact_a.sort_name, 1)) as sort_name'; + $select = 'SELECT DISTINCT LEFT(contact_a.sort_name, 1) as sort_name'; $from = $this->_simpleFromClause; } elseif ($groupContacts) { @@ -4896,6 +4896,22 @@ civicrm_relationship.start_date > {$today} return $dao; } + /** + * Create and query the db for a contact search. + * + * @return CRM_Core_DAO + */ + public function alphabetQuery() { + $query = $this->getSearchSQL(NULL, NULL, NULL, FALSE, FALSE, TRUE); + + $dao = CRM_Core_DAO::executeQuery($query); + + // We can always call this - it will only re-enable if it was originally enabled. + CRM_Core_DAO::reenableFullGroupByMode(); + + return $dao; + } + /** * Fetch a list of contacts for displaying a search results page * @@ -6249,25 +6265,19 @@ AND displayRelType.is_active = 1 } } elseif ($sortByChar) { - $orderByArray = array("UPPER(LEFT(contact_a.sort_name, 1)) asc"); + $orderBy = " sort_name asc"; } else { $orderBy = " contact_a.sort_name ASC, contact_a.id"; } } - if (!$orderBy && empty($orderByArray)) { + if (!$orderBy) { return [NULL, $additionalFromClause]; } // Remove this here & add it at the end for simplicity. $order = trim($orderBy); + $orderByArray = explode(',', $order); - // hack for order clause - if (!empty($orderByArray)) { - $order = implode(', ', $orderByArray); - } - else { - $orderByArray = explode(',', $order); - } foreach ($orderByArray as $orderByClause) { $orderByClauseParts = explode(' ', trim($orderByClause)); $field = $orderByClauseParts[0]; diff --git a/CRM/Contact/Selector.php b/CRM/Contact/Selector.php index 9c3c63eb8e..7f3952a169 100644 --- a/CRM/Contact/Selector.php +++ b/CRM/Contact/Selector.php @@ -1192,7 +1192,7 @@ class CRM_Contact_Selector extends CRM_Core_Selector_Base implements CRM_Core_Se * @return CRM_Contact_DAO_Contact */ public function alphabetQuery() { - return $this->_query->searchQuery(NULL, NULL, NULL, FALSE, FALSE, TRUE); + return $this->_query->alphabetQuery(); } /** diff --git a/CRM/Contribute/Selector/Search.php b/CRM/Contribute/Selector/Search.php index 143e840bac..0606f9a207 100644 --- a/CRM/Contribute/Selector/Search.php +++ b/CRM/Contribute/Selector/Search.php @@ -619,7 +619,7 @@ class CRM_Contribute_Selector_Search extends CRM_Core_Selector_Base implements C * @return mixed */ public function alphabetQuery() { - return $this->_query->searchQuery(NULL, NULL, NULL, FALSE, FALSE, TRUE); + return $this->_query->alphabetQuery(); } /** diff --git a/CRM/Event/Selector/Search.php b/CRM/Event/Selector/Search.php index 5a71846be4..cbe2da5182 100644 --- a/CRM/Event/Selector/Search.php +++ b/CRM/Event/Selector/Search.php @@ -530,7 +530,7 @@ class CRM_Event_Selector_Search extends CRM_Core_Selector_Base implements CRM_Co * @return mixed */ public function alphabetQuery() { - return $this->_query->searchQuery(NULL, NULL, NULL, FALSE, FALSE, TRUE); + return $this->_query->alphabetQuery(); } /** diff --git a/CRM/Mailing/Selector/Search.php b/CRM/Mailing/Selector/Search.php index 5621f89191..51f41f469d 100644 --- a/CRM/Mailing/Selector/Search.php +++ b/CRM/Mailing/Selector/Search.php @@ -389,7 +389,7 @@ class CRM_Mailing_Selector_Search extends CRM_Core_Selector_Base implements CRM_ * @return mixed */ public function alphabetQuery() { - return $this->_query->searchQuery(NULL, NULL, NULL, FALSE, FALSE, TRUE); + return $this->_query->alphabetQuery(); } /** diff --git a/CRM/Member/Selector/Search.php b/CRM/Member/Selector/Search.php index a52b96b4c9..f46782627a 100644 --- a/CRM/Member/Selector/Search.php +++ b/CRM/Member/Selector/Search.php @@ -543,7 +543,7 @@ class CRM_Member_Selector_Search extends CRM_Core_Selector_Base implements CRM_C * @return mixed */ public function alphabetQuery() { - return $this->_query->searchQuery(NULL, NULL, NULL, FALSE, FALSE, TRUE); + return $this->_query->alphabetQuery(); } /** diff --git a/CRM/Utils/PagerAToZ.php b/CRM/Utils/PagerAToZ.php index 2176b5b002..e73e46fd48 100644 --- a/CRM/Utils/PagerAToZ.php +++ b/CRM/Utils/PagerAToZ.php @@ -113,7 +113,7 @@ class CRM_Utils_PagerAToZ { $dynamicAlphabets = array(); while ($result->fetch()) { - $dynamicAlphabets[] = $result->sort_name; + $dynamicAlphabets[] = strtoupper($result->sort_name); } return $dynamicAlphabets; } -- 2.25.1