Merge pull request #10306 from aydun/CRM-20525

[civicrm-core.git] / Civi / API / SelectQuery.php

diff --git a/Civi/API/SelectQuery.php b/Civi/API/SelectQuery.php
index fc5d22cad5b5ed3ce4d191ffdc36300137a55c06..7e047bd182c1a5a1fc35629dd935efa977302b73 100644 (file)
--- a/Civi/API/SelectQuery.php
+++ b/Civi/API/SelectQuery.php
@@ -3,7 +3,7 @@
  +--------------------------------------------------------------------+
  | CiviCRM version 4.7                                                |
  +--------------------------------------------------------------------+
- | Copyright CiviCRM LLC (c) 2004-2016                                |
+ | Copyright CiviCRM LLC (c) 2004-2017                                |
  +--------------------------------------------------------------------+
  | This file is a part of CiviCRM.                                    |
  |                                                                    |
@@ -114,7 +114,7 @@ abstract class SelectQuery {
   /**
    * Build & execute the query and return results array
    *
-   * @return array
+   * @return array|int
    * @throws \API_Exception
    * @throws \CRM_Core_Exception
    * @throws \Exception
@@ -215,11 +215,12 @@ abstract class SelectQuery {
       if ($depth > self::MAX_JOINS) {
         throw new UnauthorizedException("Maximum number of joins exceeded in parameter $fkFieldName");
       }
+      $subStack = array_slice($stack, 0, $depth);
+      $this->getJoinInfo($fkField, $subStack);
       if (!isset($fkField['FKApiName']) || !isset($fkField['FKClassName'])) {
         // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it.
         return NULL;
       }
-      $subStack = array_slice($stack, 0, $depth);
       // Ensure we have permission to access the other api
       if (!$this->checkPermissionToJoin($fkField['FKApiName'], $subStack)) {
         throw new UnauthorizedException("Authorization failed to join onto {$fkField['FKApiName']} api in parameter $fkFieldName");
@@ -229,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;
       }
@@ -239,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) {
@@ -257,6 +262,29 @@ abstract class SelectQuery {
     return array($tableAlias, $fieldName);
   }
 
+  /**
+   * Get join info for dynamically-joined fields (e.g. "entity_id", "option_group")
+   *
+   * @param $fkField
+   * @param $stack
+   */
+  protected function getJoinInfo(&$fkField, $stack) {
+    if ($fkField['name'] == 'entity_id') {
+      $entityTableParam = substr(implode('.', $stack), 0, -2) . 'table';
+      $entityTable = \CRM_Utils_Array::value($entityTableParam, $this->where);
+      if ($entityTable && is_string($entityTable) && \CRM_Core_DAO_AllCoreTables::getClassForTable($entityTable)) {
+        $fkField['FKClassName'] = \CRM_Core_DAO_AllCoreTables::getClassForTable($entityTable);
+        $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']}')";
+    }
+  }
+
   /**
    * Joins onto a custom field
    *
@@ -374,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 {
@@ -396,7 +427,6 @@ abstract class SelectQuery {
         }
       }
     }
-    $this->query->orderBy($orderBy);
   }
 
   /**