From bee644e5281001f127522770dcf7f278e69a29b9 Mon Sep 17 00:00:00 2001
From: Johan Vervloet <johanv@johanv.org>
Date: Thu, 19 Mar 2015 15:40:47 +0100
Subject: [PATCH] CRM-16036 - Now contact references are returned the right
 way.

----------------------------------------
* CRM-16036: API: searching on custom fields does not work
  https://issues.civicrm.org/jira/browse/CRM-16036
---
 api/v3/utils.php | 47 +++++++++++++++++++++++++++++++++++++++--------
 1 file changed, 39 insertions(+), 8 deletions(-)

diff --git a/api/v3/utils.php b/api/v3/utils.php
index bdb6102f58..d29dedd8c9 100644
--- a/api/v3/utils.php
+++ b/api/v3/utils.php
@@ -490,7 +490,10 @@ function _civicrm_api3_get_using_query_object_simple($dao_name, $params, $return
   $where_clauses = array();
 
   // Tables we need to join with to retrieve the custom values.
-  $tables_to_join = array();
+  $custom_value_tables = array();
+
+  // ID's of custom fields that refer to a contact.
+  $contact_reference_field_ids = array();
 
   // populate $select_fields
   $return_all_fields = (empty($options['return']) || !is_array($options['return']));
@@ -509,10 +512,22 @@ function _civicrm_api3_get_using_query_object_simple($dao_name, $params, $return
     if ($return_all_fields || !empty($options['return'][$field_name])) {
       $table_name = $custom_field["table_name"];
       $column_name = $custom_field["column_name"];
-      $select_fields["$table_name.$column_name"] = "custom_$cf_id";
       // remember that we will need to join the correct table.
-      if (!in_array($table_name, $tables_to_join)) {
-        $tables_to_join[] = $table_name;
+      if (!in_array($table_name, $custom_value_tables)) {
+        $custom_value_tables[] = $table_name;
+      }
+      if ($custom_field["data_type"] != "ContactReference") {
+        // 'ordinary' custom field. We will select the value as custom_XX.
+        $select_fields["$table_name.$column_name"] = $field_name;
+      }
+      else {
+        // contact reference custom field. The ID will be stored in
+        // custom_XX_id. custom_XX will contain the sort name of the
+        // contact.
+        $contact_reference_field_ids[] = $cf_id;
+        $select_fields["$table_name.$column_name"] = $field_name . "_id";
+        // We will call the contact table for the join c_XX.
+        $select_fields["c_$cf_id.sort_name"] = $field_name;
       }
     }
   }
@@ -551,8 +566,8 @@ function _civicrm_api3_get_using_query_object_simple($dao_name, $params, $return
           $operator,
           $rhs,
         );
-        if (!in_array($table_name, $tables_to_join)) {
-          $tables_to_join[] = $table_name;
+        if (!in_array($table_name, $custom_value_tables)) {
+          $custom_value_tables[] = $table_name;
         }
       }
     }
@@ -567,7 +582,8 @@ function _civicrm_api3_get_using_query_object_simple($dao_name, $params, $return
     $query = $query->select("!column_$i as !alias_$i", array("!column_$i" => $column, "!alias_$i" => $alias));
   }
 
-  foreach ($tables_to_join as $table_name) {
+  // join with custom value tables
+  foreach ($custom_value_tables as $table_name) {
     ++$i;
     $query = $query->join(
       "!table_name_$i",
@@ -576,6 +592,19 @@ function _civicrm_api3_get_using_query_object_simple($dao_name, $params, $return
     );
   }
 
+  // join with contact for contact reference fields
+  foreach ($contact_reference_field_ids as $field_id) {
+    ++$i;
+    $query = $query->join(
+      "!contact_table_name$i",
+      "LEFT OUTER JOIN civicrm_contact !contact_table_name_$i ON !contact_table_name_$i.id = !values_table_name_$i.!column_name_$i",
+      array(
+        "!contact_table_name_$i" => "c_$field_id",
+        "!values_table_name_$i" => $custom_fields[$field_id]["table_name"],
+        "!column_name_$i" => $custom_fields[$field_id]["column_name"],
+      ));
+  };
+
   foreach ($where_clauses as $clause) {
     ++$i;
     if (substr($clause[1], -4) == "NULL") {
@@ -668,7 +697,8 @@ function _civicrm_api3_field_names($fields) {
  *   {
  *     '1' => array {
  *       'table_name' => 'table_name_1',
- *       'column_name' => ''column_name_1',
+ *       'column_name' => 'column_name_1',
+ *       'data_type' => 'data_type_1',
  *     },
  *   }
  */
@@ -696,6 +726,7 @@ SELECT f.id, f.label, f.data_type,
     $result[$dao->id] = array(
       'table_name' => $dao->table_name,
       'column_name' => $dao->column_name,
+      'data_type' => $dao->data_type,
     );
   }
   $dao->free();
-- 
2.25.1