From e62cd61b2724fc19cede0ffd0bc7927f7ebe2ef9 Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Fri, 12 Feb 2021 11:06:28 -0500
Subject: [PATCH] APIv4 - Allow field aliases in ORDER BY clause

---
 Civi/Api4/Query/Api4SelectQuery.php           | 35 +++++++++++++------
 .../phpunit/api/v4/Action/SqlFunctionTest.php |  1 +
 2 files changed, 25 insertions(+), 11 deletions(-)

diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php
index 49a3760b31..073e36ac29 100644
--- a/Civi/Api4/Query/Api4SelectQuery.php
+++ b/Civi/Api4/Query/Api4SelectQuery.php
@@ -275,19 +275,32 @@ class Api4SelectQuery {
       if ($dir !== 'ASC' && $dir !== 'DESC') {
         throw new \API_Exception("Invalid sort direction. Cannot order by $item $dir");
       }
-      $expr = $this->getExpression($item);
-      $column = $expr->render($this->apiFieldSpec);
-
-      // Use FIELD() function to sort on pseudoconstant values
-      $suffix = strstr($item, ':');
-      if ($suffix && $expr->getType() === 'SqlField') {
-        $field = $this->getField($item);
-        $options = FormattingUtil::getPseudoconstantList($field, substr($suffix, 1));
-        if ($options) {
-          asort($options);
-          $column = "FIELD($column,'" . implode("','", array_keys($options)) . "')";
+
+      try {
+        $expr = $this->getExpression($item);
+        $column = $expr->render($this->apiFieldSpec);
+
+        // Use FIELD() function to sort on pseudoconstant values
+        $suffix = strstr($item, ':');
+        if ($suffix && $expr->getType() === 'SqlField') {
+          $field = $this->getField($item);
+          $options = FormattingUtil::getPseudoconstantList($field, substr($suffix, 1));
+          if ($options) {
+            asort($options);
+            $column = "FIELD($column,'" . implode("','", array_keys($options)) . "')";
+          }
         }
       }
+      // If the expression could not be rendered, it might be a field alias
+      catch (\API_Exception $e) {
+        if (!empty($this->selectAliases[$item])) {
+          $column = '`' . $item . '`';
+        }
+        else {
+          throw new \API_Exception("Invalid field '{$item}'");
+        }
+      }
+
       $this->query->orderBy("$column $dir");
     }
   }
diff --git a/tests/phpunit/api/v4/Action/SqlFunctionTest.php b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
index 27815be6c3..a4ecd8d11f 100644
--- a/tests/phpunit/api/v4/Action/SqlFunctionTest.php
+++ b/tests/phpunit/api/v4/Action/SqlFunctionTest.php
@@ -57,6 +57,7 @@ class SqlFunctionTest extends UnitTestCase {
       ->addSelect('MAX(total_amount)')
       ->addSelect('MIN(total_amount)')
       ->addSelect('COUNT(*) AS count')
+      ->addOrderBy('average')
       ->execute()
       ->first();
     $this->assertTrue(250.0 === $agg['average']);
-- 
2.25.1