From 4c6cc3647eb7b19a05529bd40b02e26623e8effe Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Wed, 29 Mar 2017 00:48:38 -0400 Subject: [PATCH] CRM-20345 - Allow weight to be specified for an orderBy Functions wishing to extend & override an api sort properties can use the placeholder (1) to maintain sort param order. --- CRM/Utils/SQL/Select.php | 14 ++++++++++---- Civi/API/SelectQuery.php | 14 ++++++++------ api/v3/Case.php | 6 +++--- api/v3/utils.php | 7 +++++-- tests/phpunit/api/v3/CaseTest.php | 6 +++--- 5 files changed, 29 insertions(+), 18 deletions(-) diff --git a/CRM/Utils/SQL/Select.php b/CRM/Utils/SQL/Select.php index 6296e2702c..a467baf4c9 100644 --- a/CRM/Utils/SQL/Select.php +++ b/CRM/Utils/SQL/Select.php @@ -323,13 +323,17 @@ class CRM_Utils_SQL_Select implements ArrayAccess { * * @param string|array $exprs list of SQL expressions * @param null|array $args use NULL to disable interpolation; use an array of variables to enable - * @return CRM_Utils_SQL_Select + * @param int $weight + * @return \CRM_Utils_SQL_Select */ - public function orderBy($exprs, $args = NULL) { + public function orderBy($exprs, $args = NULL, $weight = NULL) { $exprs = (array) $exprs; + if ($weight === NULL) { + $weight = count($this->orderBys); + } foreach ($exprs as $expr) { $evaluatedExpr = $this->interpolate($expr, $args); - $this->orderBys[$evaluatedExpr] = $evaluatedExpr; + $this->orderBys[$evaluatedExpr] = array('value' => $evaluatedExpr, 'weight' => $weight++); } return $this; } @@ -574,7 +578,9 @@ class CRM_Utils_SQL_Select implements ArrayAccess { $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n"; } if ($this->orderBys) { - $sql .= 'ORDER BY ' . implode(', ', $this->orderBys) . "\n"; + $orderBys = CRM_Utils_Array::crmArraySortByField($this->orderBys, 'weight'); + $orderBys = CRM_Utils_Array::collect('value', $orderBys); + $sql .= 'ORDER BY ' . implode(', ', $orderBys) . "\n"; } if ($this->limit !== NULL) { $sql .= 'LIMIT ' . $this->limit . "\n"; diff --git a/Civi/API/SelectQuery.php b/Civi/API/SelectQuery.php index 06ad6c73f4..7e047bd182 100644 --- a/Civi/API/SelectQuery.php +++ b/Civi/API/SelectQuery.php @@ -402,21 +402,24 @@ abstract class SelectQuery { * @throws \Civi\API\Exception\UnauthorizedException */ protected function buildOrderBy() { - $orderBy = array(); $sortParams = is_string($this->orderBy) ? explode(',', $this->orderBy) : (array) $this->orderBy; - foreach ($sortParams as $item) { - $words = preg_split("/[\s]+/", trim($item)); + foreach ($sortParams as $index => $item) { + $item = trim($item); + if ($item == '(1)') { + continue; + } + $words = preg_split("/[\s]+/", $item); if ($words) { // Direction defaults to ASC unless DESC is specified $direction = strtoupper(\CRM_Utils_Array::value(1, $words, '')) == 'DESC' ? ' DESC' : ''; $field = $this->getField($words[0]); if ($field) { - $orderBy[] = self::MAIN_TABLE_ALIAS . '.' . $field['name'] . $direction; + $this->query->orderBy(self::MAIN_TABLE_ALIAS . '.' . $field['name'] . $direction, NULL, $index); } elseif (strpos($words[0], '.')) { $join = $this->addFkField($words[0], 'LEFT'); if ($join) { - $orderBy[] = "`{$join[0]}`.`{$join[1]}`$direction"; + $this->query->orderBy("`{$join[0]}`.`{$join[1]}`$direction", NULL, $index); } } else { @@ -424,7 +427,6 @@ abstract class SelectQuery { } } } - $this->query->orderBy($orderBy); } /** diff --git a/api/v3/Case.php b/api/v3/Case.php index 143e65ae14..849e32228d 100644 --- a/api/v3/Case.php +++ b/api/v3/Case.php @@ -249,10 +249,10 @@ function civicrm_api3_case_get($params, $sql = NULL) { if (!empty($options['sort']) && strpos($options['sort'], 'contact_id') !== FALSE) { $sort = explode(', ', $options['sort']); $contactSort = NULL; - foreach ($sort as $index => $sortString) { + foreach ($sort as $index => &$sortString) { if (strpos($sortString, 'contact_id') === 0) { $contactSort = $sortString; - unset($sort[$index]); + $sortString = '(1)'; // Get sort field and direction list($sortField, $dir) = array_pad(explode(' ', $contactSort), 2, 'ASC'); list(, $sortField) = array_pad(explode('.', $sortField), 2, 'id'); @@ -260,7 +260,7 @@ function civicrm_api3_case_get($params, $sql = NULL) { if (!array_key_exists($sortField, CRM_Contact_DAO_Contact::fieldKeys()) || ($dir != 'ASC' && $dir != 'DESC')) { throw new API_Exception("Unknown field specified for sort. Cannot order by '$contactSort'"); } - $sql->orderBy("case_contact.$sortField $dir"); + $sql->orderBy("case_contact.$sortField $dir", NULL, $index); } } // Remove contact sort params so the basic_get function doesn't see them diff --git a/api/v3/utils.php b/api/v3/utils.php index 1e5400b419..1e78f7b80e 100644 --- a/api/v3/utils.php +++ b/api/v3/utils.php @@ -825,8 +825,11 @@ function _civicrm_api3_get_options_from_params(&$params, $queryObject = FALSE, $ $finalSort = array(); $options['sort'] = NULL; if (!empty($sort)) { - foreach ((array) $sort as $s) { - if (CRM_Utils_Rule::mysqlOrderBy($s)) { + if (!is_array($sort)) { + $sort = array_map('trim', explode(',', $sort)); + } + foreach ($sort as $s) { + if ($s == '(1)' || CRM_Utils_Rule::mysqlOrderBy($s)) { if ($entity && $action == 'get') { switch (trim(strtolower($s))) { case 'id': diff --git a/tests/phpunit/api/v3/CaseTest.php b/tests/phpunit/api/v3/CaseTest.php index f178ed0e57..be0d687cc1 100644 --- a/tests/phpunit/api/v3/CaseTest.php +++ b/tests/phpunit/api/v3/CaseTest.php @@ -570,7 +570,7 @@ class api_v3_CaseTest extends CiviCaseTestCase { */ public function testCaseGetOrderByClient() { $contact1 = $this->individualCreate(array('first_name' => 'Aa', 'last_name' => 'Zz')); - $contact2 = $this->individualCreate(array('first_name' => 'Bb', 'last_name' => 'Yy')); + $contact2 = $this->individualCreate(array('first_name' => 'Bb', 'last_name' => 'Zz')); $contact3 = $this->individualCreate(array('first_name' => 'Cc', 'last_name' => 'Xx')); $case1 = $this->callAPISuccess('Case', 'create', array( @@ -603,7 +603,7 @@ class api_v3_CaseTest extends CiviCaseTestCase { 'contact_id' => array('IN' => array($contact1, $contact2, $contact3)), 'sequential' => 1, 'return' => 'id', - 'options' => array('sort' => 'contact_id.last_name ASC'), + 'options' => array('sort' => 'contact_id.last_name ASC, contact_id.first_name DESC'), )); $this->assertEquals($case1['id'], $result['values'][2]['id']); $this->assertEquals($case2['id'], $result['values'][1]['id']); @@ -623,7 +623,7 @@ class api_v3_CaseTest extends CiviCaseTestCase { 'contact_id' => array('IN' => array($contact1, $contact2, $contact3)), 'sequential' => 1, 'return' => 'id', - 'options' => array('sort' => 'contact_id DESC'), + 'options' => array('sort' => 'case_type_id, contact_id DESC, status_id'), )); $this->assertEquals($case1['id'], $result['values'][2]['id']); $this->assertEquals($case2['id'], $result['values'][1]['id']); -- 2.25.1