From fac3f2e0d813e8df321639bc711163b46802ea87 Mon Sep 17 00:00:00 2001
From: colemanw <coleman@civicrm.org>
Date: Sun, 4 Jun 2023 20:29:17 -0400
Subject: [PATCH] dev/core#4330 - Fix APIv4 orderBy to resolve ambiguous column
 names

See https://lab.civicrm.org/dev/core/-/issues/4330
This regressed from ff2a73c, the fix is to only use names from the select
clause if they are actual aliases.
---
 Civi/Api4/Query/Api4SelectQuery.php         |  7 ++++---
 tests/phpunit/api/v4/Entity/AddressTest.php | 18 ++++++++++++++++++
 2 files changed, 22 insertions(+), 3 deletions(-)

diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php
index 8430da8794..8fa35dd4ba 100644
--- a/Civi/Api4/Query/Api4SelectQuery.php
+++ b/Civi/Api4/Query/Api4SelectQuery.php
@@ -1346,10 +1346,11 @@ class Api4SelectQuery {
    */
   protected function renderExpr($expr) {
     $exprVal = explode(':', $expr->getExpr())[0];
-    // If this expression is already in use in the select clause, use the existing alias
-    // This allows calculated fields to be reused in SELECT, GROUP BY and ORDER BY
+    // If this expression is already aliased in the select clause, use the existing alias.
+    // This allows calculated fields to be reused in SELECT, GROUP BY and ORDER BY.
     foreach ($this->selectAliases as $alias => $selectVal) {
-      if ($exprVal === explode(':', $selectVal)[0]) {
+      $selectVal = explode(':', $selectVal)[0];
+      if ($alias !== $selectVal && $exprVal === $selectVal) {
         return "`$alias`";
       }
     }
diff --git a/tests/phpunit/api/v4/Entity/AddressTest.php b/tests/phpunit/api/v4/Entity/AddressTest.php
index c03f6a14e4..5cb60b9487 100644
--- a/tests/phpunit/api/v4/Entity/AddressTest.php
+++ b/tests/phpunit/api/v4/Entity/AddressTest.php
@@ -91,4 +91,22 @@ class AddressTest extends Api4TestBase implements TransactionalInterface {
     $this->assertNotContains($addreses[3], $result);
   }
 
+  public function testMasterAddressJoin() {
+    $contact = $this->createTestRecord('Contact');
+    $master = $this->createTestRecord('Address', [
+      'contact_id' => $contact['id'],
+    ]);
+    $address = $this->createTestRecord('Address', [
+      'master_id' => $master['id'],
+      'contact_id' => $this->createTestRecord('Contact')['id'],
+    ]);
+    $result = Address::get(FALSE)
+      ->addJoin('Contact AS master_contact', 'LEFT', ['master_id.contact_id', '=', 'master_contact.id'])
+      ->addSelect('master_contact.id')
+      // Ensure the query can handle the ambiguity of two joined entities with a `location_type_id` field
+      ->addOrderBy('location_type_id:label', 'ASC')
+      ->execute()->indexBy('id');
+    $this->assertEquals($contact['id'], $result[$address['id']]['master_contact.id']);
+  }
+
 }
-- 
2.25.1