From 9c846e15a590a60be32c5c2810762cdf38b572f4 Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Thu, 29 Dec 2016 12:22:36 -0500
Subject: [PATCH] CRM-19810 - Add api joins on optionValue table

---
 Civi/API/SelectQuery.php                | 18 ++++++++++++++----
 templates/CRM/Admin/Page/APIExplorer.js |  3 +++
 tests/phpunit/api/v3/EntityJoinTest.php |  5 +++--
 3 files changed, 20 insertions(+), 6 deletions(-)

diff --git a/Civi/API/SelectQuery.php b/Civi/API/SelectQuery.php
index 357dc92ceb..f44365f08d 100644
--- a/Civi/API/SelectQuery.php
+++ b/Civi/API/SelectQuery.php
@@ -230,8 +230,8 @@ abstract class SelectQuery {
       }
       $fieldInfo = \CRM_Utils_Array::value($fieldName, $fkField['FKApiSpec']);
 
-      // FIXME: What if the foreign key is not the "id" column?
-      if (!$fieldInfo || !isset($fkField['FKApiSpec']['id'])) {
+      $keyColumn = \CRM_Utils_Array::value('FKKeyColumn', $fkField, 'id');
+      if (!$fieldInfo || !isset($fkField['FKApiSpec'][$keyColumn])) {
         // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it.
         return NULL;
       }
@@ -240,10 +240,14 @@ abstract class SelectQuery {
 
       // Add acl condition
       $joinCondition = array_merge(
-        array("$prev.$fk = $tableAlias.id"),
+        array("$prev.$fk = $tableAlias.$keyColumn"),
         $this->getAclClause($tableAlias, \_civicrm_api3_get_BAO($fkField['FKApiName']), $subStack)
       );
 
+      if (!empty($fkField['FKCondition'])) {
+        $joinCondition[] = str_replace($fkTable, $tableAlias, $fkField['FKCondition']);
+      }
+
       $this->join($side, $fkTable, $tableAlias, $joinCondition);
 
       if (strpos($fieldName, 'custom_') === 0) {
@@ -259,7 +263,7 @@ abstract class SelectQuery {
   }
 
   /**
-   * Get join info for dynamically-joined fields (e.g. "entity_id")
+   * Get join info for dynamically-joined fields (e.g. "entity_id", "option_group")
    *
    * @param $fkField
    * @param $stack
@@ -273,6 +277,12 @@ abstract class SelectQuery {
         $fkField['FKApiName'] = \CRM_Core_DAO_AllCoreTables::getBriefName($fkField['FKClassName']);
       }
     }
+    if (!empty($fkField['pseudoconstant']['optionGroupName'])) {
+      $fkField['FKClassName'] = 'CRM_Core_DAO_OptionValue';
+      $fkField['FKApiName'] = 'OptionValue';
+      $fkField['FKKeyColumn'] = 'value';
+      $fkField['FKCondition'] = "civicrm_option_value.option_group_id = (SELECT id FROM civicrm_option_group WHERE name = '{$fkField['pseudoconstant']['optionGroupName']}')";
+    }
   }
 
   /**
diff --git a/templates/CRM/Admin/Page/APIExplorer.js b/templates/CRM/Admin/Page/APIExplorer.js
index 7c3a4b1894..bbcc726095 100644
--- a/templates/CRM/Admin/Page/APIExplorer.js
+++ b/templates/CRM/Admin/Page/APIExplorer.js
@@ -139,6 +139,9 @@
         field.FKApiName = getField(entityTableParam).options[params[entityTableParam]];
       }
     }
+    if (field.pseudoconstant && field.pseudoconstant.optionGroupName) {
+      field.FKApiName = 'OptionValue';
+    }
   }
 
   /**
diff --git a/tests/phpunit/api/v3/EntityJoinTest.php b/tests/phpunit/api/v3/EntityJoinTest.php
index 30713a962d..25c598d4d8 100644
--- a/tests/phpunit/api/v3/EntityJoinTest.php
+++ b/tests/phpunit/api/v3/EntityJoinTest.php
@@ -42,14 +42,15 @@ class api_v3_EntityJoinTest extends CiviUnitTestCase {
     $first = 'firstthisisatest';
     $last = 'lastthisisatest';
     $org = $this->organizationCreate(array('organization_name' => 'Employer of one'));
-    $person1 = $this->individualCreate(array('employer_id' => $org, 'first_name' => $first, 'last_name' => $last));
+    $person1 = $this->individualCreate(array('employer_id' => $org, 'first_name' => $first, 'last_name' => $last, 'gender_id' => 1));
     $person2 = $this->individualCreate(array(), 1);
     $result = $this->callAPISuccessGetSingle('Email', array(
-      'return' => 'contact_id.employer_id.display_name',
+      'return' => 'contact_id.employer_id.display_name,contact_id.gender_id.label',
       'contact_id.last_name' => $last,
       'contact_id.first_name' => $first,
     ));
     $this->assertEquals('Employer of one', $result['contact_id.employer_id.display_name']);
+    $this->assertEquals('Female', $result['contact_id.gender_id.label']);
   }
 
 }
-- 
2.25.1