Civi/API/SelectQuery.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11 namespace Civi\API;
  12
  13 use Civi\API\Exception\UnauthorizedException;
  14
  15 /**
  16  * Query builder for civicrm_api_basic_get.
  17  *
  18  * Fetches an entity based on specified params for the "where" clause,
  19  * return properties for the "select" clause,
  20  * as well as limit and order.
  21  *
  22  * Automatically joins on custom fields to return or filter by them.
  23  *
  24  * Supports an additional sql fragment which the calling api can provide.
  25  *
  26  * @package Civi\API
  27  */
  28 abstract class SelectQuery {
  29
  30   const
  31     MAX_JOINS = 4,
  32     MAIN_TABLE_ALIAS = 'a';
  33
  34   /**
  35    * @var string
  36    */
  37   protected $entity;
  38   public $select = [];
  39   public $where = [];
  40   public $orderBy = [];
  41   public $limit;
  42   public $offset;
  43   /**
  44    * @var array
  45    */
  46   protected $selectFields = [];
  47   /**
  48    * @var bool
  49    */
  50   public $isFillUniqueFields = FALSE;
  51   /**
  52    * @var \CRM_Utils_SQL_Select
  53    */
  54   protected $query;
  55   /**
  56    * @var array
  57    */
  58   protected $joins = [];
  59   /**
  60    * @var array
  61    */
  62   protected $apiFieldSpec;
  63   /**
  64    * @var array
  65    */
  66   protected $entityFieldNames;
  67   /**
  68    * @var array
  69    */
  70   protected $aclFields = [];
  71   /**
  72    * @var string|bool
  73    */
  74   protected $checkPermissions;
  75
  76   protected $apiVersion;
  77
  78   /**
  79    * @param string $entity
  80    * @param bool $checkPermissions
  81    */
  82   public function __construct($entity, $checkPermissions) {
  83     $this->entity = $entity;
  84     require_once 'api/v3/utils.php';
  85     $baoName = _civicrm_api3_get_BAO($entity);
  86     $bao = new $baoName();
  87
  88     $this->entityFieldNames = _civicrm_api3_field_names(_civicrm_api3_build_fields_array($bao));
  89     $this->apiFieldSpec = $this->getFields();
  90
  91     $this->query = \CRM_Utils_SQL_Select::from($bao->tableName() . ' ' . self::MAIN_TABLE_ALIAS);
  92
  93     // Add ACLs first to avoid redundant subclauses
  94     $this->checkPermissions = $checkPermissions;
  95     $this->query->where($this->getAclClause(self::MAIN_TABLE_ALIAS, $baoName));
  96   }
  97
  98   /**
  99    * Build & execute the query and return results array
 100    *
 101    * @return array|int
 102    * @throws \API_Exception
 103    * @throws \CRM_Core_Exception
 104    * @throws \Exception
 105    */
 106   public function run() {
 107     $this->buildSelectFields();
 108
 109     $this->buildWhereClause();
 110     if (in_array('count_rows', $this->select)) {
 111       $this->query->select("count(*) as c");
 112     }
 113     else {
 114       foreach ($this->selectFields as $column => $alias) {
 115         $this->query->select("$column as `$alias`");
 116       }
 117       // Order by
 118       $this->buildOrderBy();
 119     }
 120
 121     // Limit
 122     if (!empty($this->limit) || !empty($this->offset)) {
 123       $this->query->limit($this->limit, $this->offset);
 124     }
 125
 126     $result_entities = [];
 127     $result_dao = \CRM_Core_DAO::executeQuery($this->query->toSQL());
 128
 129     while ($result_dao->fetch()) {
 130       if (in_array('count_rows', $this->select)) {
 131         return (int) $result_dao->c;
 132       }
 133       $result_entities[$result_dao->id] = [];
 134       foreach ($this->selectFields as $column => $alias) {
 135         $returnName = $alias;
 136         $alias = str_replace('.', '_', $alias);
 137         if (property_exists($result_dao, $alias) && $result_dao->$alias != NULL) {
 138           $result_entities[$result_dao->id][$returnName] = $result_dao->$alias;
 139         }
 140         // Backward compatibility on fields names.
 141         if ($this->isFillUniqueFields && !empty($this->apiFieldSpec[$alias]['uniqueName'])) {
 142           $result_entities[$result_dao->id][$this->apiFieldSpec[$alias]['uniqueName']] = $result_dao->$alias;
 143         }
 144         foreach ($this->apiFieldSpec as $returnName => $spec) {
 145           if (empty($result_entities[$result_dao->id][$returnName]) && !empty($result_entities[$result_dao->id][$spec['name']])) {
 146             $result_entities[$result_dao->id][$returnName] = $result_entities[$result_dao->id][$spec['name']];
 147           }
 148         }
 149       };
 150     }
 151     return $result_entities;
 152   }
 153
 154   /**
 155    * @param \CRM_Utils_SQL_Select $sqlFragment
 156    * @return SelectQuery
 157    */
 158   public function merge($sqlFragment) {
 159     $this->query->merge($sqlFragment);
 160     return $this;
 161   }
 162
 163   /**
 164    * Joins onto an fk field
 165    *
 166    * Adds one or more joins to the query to make this field available for use in a clause.
 167    *
 168    * Enforces permissions at the api level and by appending the acl clause for that entity to the join.
 169    *
 170    * @param $fkFieldName
 171    * @param $side
 172    *
 173    * @return array|null
 174    *   Returns the table and field name for adding this field to a SELECT or WHERE clause
 175    * @throws \API_Exception
 176    * @throws \Civi\API\Exception\UnauthorizedException
 177    */
 178   protected function addFkField($fkFieldName, $side) {
 179     $stack = explode('.', $fkFieldName);
 180     if (count($stack) < 2) {
 181       return NULL;
 182     }
 183     $prev = self::MAIN_TABLE_ALIAS;
 184     foreach ($stack as $depth => $fieldName) {
 185       // Setup variables then skip the first level
 186       if (!$depth) {
 187         $fk = $fieldName;
 188         // We only join on core fields
 189         // @TODO: Custom contact ref fields could be supported too
 190         if (!in_array($fk, $this->entityFieldNames)) {
 191           return NULL;
 192         }
 193         $fkField = &$this->apiFieldSpec[$fk];
 194         continue;
 195       }
 196       // More than 4 joins deep seems excessive - DOS attack?
 197       if ($depth > self::MAX_JOINS) {
 198         throw new UnauthorizedException("Maximum number of joins exceeded in parameter $fkFieldName");
 199       }
 200       $subStack = array_slice($stack, 0, $depth);
 201       $this->getJoinInfo($fkField, $subStack);
 202       if (!isset($fkField['FKApiName']) || !isset($fkField['FKClassName'])) {
 203         // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it.
 204         return NULL;
 205       }
 206       // Ensure we have permission to access the other api
 207       if (!$this->checkPermissionToJoin($fkField['FKApiName'], $subStack)) {
 208         throw new UnauthorizedException("Authorization failed to join onto {$fkField['FKApiName']} api in parameter $fkFieldName");
 209       }
 210       if (!isset($fkField['FKApiSpec'])) {
 211         $fkField['FKApiSpec'] = \_civicrm_api_get_fields($fkField['FKApiName']);
 212       }
 213       $fieldInfo = \CRM_Utils_Array::value($fieldName, $fkField['FKApiSpec']);
 214
 215       $keyColumn = \CRM_Utils_Array::value('FKKeyColumn', $fkField, 'id');
 216       if (!$fieldInfo || !isset($fkField['FKApiSpec'][$keyColumn])) {
 217         // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it.
 218         return NULL;
 219       }
 220       $fkTable = \CRM_Core_DAO_AllCoreTables::getTableForClass($fkField['FKClassName']);
 221       $tableAlias = implode('_to_', $subStack) . "_to_$fkTable";
 222
 223       // Add acl condition
 224       $joinCondition = array_merge(
 225         ["$prev.$fk = $tableAlias.$keyColumn"],
 226         $this->getAclClause($tableAlias, \_civicrm_api3_get_BAO($fkField['FKApiName']), $subStack)
 227       );
 228
 229       if (!empty($fkField['FKCondition'])) {
 230         $joinCondition[] = str_replace($fkTable, $tableAlias, $fkField['FKCondition']);
 231       }
 232
 233       $this->join($side, $fkTable, $tableAlias, $joinCondition);
 234
 235       if (strpos($fieldName, 'custom_') === 0) {
 236         list($tableAlias, $fieldName) = $this->addCustomField($fieldInfo, $side, $tableAlias);
 237       }
 238
 239       // Get ready to recurse to the next level
 240       $fk = $fieldName;
 241       $fkField = &$fkField['FKApiSpec'][$fieldName];
 242       $prev = $tableAlias;
 243     }
 244     return [$tableAlias, $fieldName];
 245   }
 246
 247   /**
 248    * Get join info for dynamically-joined fields (e.g. "entity_id", "option_group")
 249    *
 250    * @param $fkField
 251    * @param $stack
 252    */
 253   protected function getJoinInfo(&$fkField, $stack) {
 254     if ($fkField['name'] == 'entity_id') {
 255       $entityTableParam = substr(implode('.', $stack), 0, -2) . 'table';
 256       $entityTable = \CRM_Utils_Array::value($entityTableParam, $this->where);
 257       if ($entityTable && is_string($entityTable) && \CRM_Core_DAO_AllCoreTables::getClassForTable($entityTable)) {
 258         $fkField['FKClassName'] = \CRM_Core_DAO_AllCoreTables::getClassForTable($entityTable);
 259         $fkField['FKApiName'] = \CRM_Core_DAO_AllCoreTables::getBriefName($fkField['FKClassName']);
 260       }
 261     }
 262     if (!empty($fkField['pseudoconstant']['optionGroupName'])) {
 263       $fkField['FKClassName'] = 'CRM_Core_DAO_OptionValue';
 264       $fkField['FKApiName'] = 'OptionValue';
 265       $fkField['FKKeyColumn'] = 'value';
 266       $fkField['FKCondition'] = "civicrm_option_value.option_group_id = (SELECT id FROM civicrm_option_group WHERE name = '{$fkField['pseudoconstant']['optionGroupName']}')";
 267     }
 268   }
 269
 270   /**
 271    * Joins onto a custom field
 272    *
 273    * Adds a join to the query to make this field available for use in a clause.
 274    *
 275    * @param array $customField
 276    * @param string $side
 277    * @param string $baseTable
 278    * @return array
 279    *   Returns the table and field name for adding this field to a SELECT or WHERE clause
 280    */
 281   protected function addCustomField($customField, $side, $baseTable = self::MAIN_TABLE_ALIAS) {
 282     $tableName = $customField["table_name"];
 283     $columnName = $customField["column_name"];
 284     $tableAlias = "{$baseTable}_to_$tableName";
 285     $this->join($side, $tableName, $tableAlias, ["`$tableAlias`.entity_id = `$baseTable`.id"]);
 286     return [$tableAlias, $columnName];
 287   }
 288
 289   /**
 290    * Fetch a field from the getFields list
 291    *
 292    * @param string $fieldName
 293    * @return array|null
 294    */
 295   abstract protected function getField($fieldName);
 296
 297   /**
 298    * Perform input validation on params that use the join syntax
 299    *
 300    * Arguably this should be done at the api wrapper level, but doing it here provides a bit more consistency
 301    * in that api permissions to perform the join are checked first.
 302    *
 303    * @param $fieldName
 304    * @param $value
 305    * @throws \Exception
 306    */
 307   protected function validateNestedInput($fieldName, &$value) {
 308     $stack = explode('.', $fieldName);
 309     $spec = $this->apiFieldSpec;
 310     $fieldName = array_pop($stack);
 311     foreach ($stack as $depth => $name) {
 312       $entity = $spec[$name]['FKApiName'];
 313       $spec = $spec[$name]['FKApiSpec'];
 314     }
 315     $params = [$fieldName => $value];
 316     \_civicrm_api3_validate_fields($entity, 'get', $params, $spec);
 317     $value = $params[$fieldName];
 318   }
 319
 320   /**
 321    * Check permission to join onto another api entity
 322    *
 323    * @param string $entity
 324    * @param array $fieldStack
 325    *   The stack of fields leading up to this join
 326    * @return bool
 327    */
 328   protected function checkPermissionToJoin($entity, $fieldStack) {
 329     if (!$this->checkPermissions) {
 330       return TRUE;
 331     }
 332     // Build an array of params that relate to the joined entity
 333     $params = [
 334       'version' => 3,
 335       'return' => [],
 336       'check_permissions' => $this->checkPermissions,
 337     ];
 338     $prefix = implode('.', $fieldStack) . '.';
 339     $len = strlen($prefix);
 340     foreach ($this->select as $key => $ret) {
 341       if (strpos($key, $prefix) === 0) {
 342         $params['return'][substr($key, $len)] = $ret;
 343       }
 344     }
 345     foreach ($this->where as $key => $param) {
 346       if (strpos($key, $prefix) === 0) {
 347         $params[substr($key, $len)] = $param;
 348       }
 349     }
 350
 351     return \Civi::service('civi_api_kernel')->runAuthorize($entity, 'get', $params);
 352   }
 353
 354   /**
 355    * Get acl clause for an entity
 356    *
 357    * @param string $tableAlias
 358    * @param string $baoName
 359    * @param array $stack
 360    * @return array
 361    */
 362   protected function getAclClause($tableAlias, $baoName, $stack = []) {
 363     if (!$this->checkPermissions) {
 364       return [];
 365     }
 366     // Prevent (most) redundant acl sub clauses if they have already been applied to the main entity.
 367     // FIXME: Currently this only works 1 level deep, but tracking through multiple joins would increase complexity
 368     // and just doing it for the first join takes care of most acl clause deduping.
 369     if (count($stack) === 1 && in_array($stack[0], $this->aclFields)) {
 370       return [];
 371     }
 372     $clauses = $baoName::getSelectWhereClause($tableAlias);
 373     if (!$stack) {
 374       // Track field clauses added to the main entity
 375       $this->aclFields = array_keys($clauses);
 376     }
 377     return array_filter($clauses);
 378   }
 379
 380   /**
 381    * Orders the query by one or more fields
 382    *
 383    * @throws \API_Exception
 384    * @throws \Civi\API\Exception\UnauthorizedException
 385    */
 386   protected function buildOrderBy() {
 387     $sortParams = is_string($this->orderBy) ? explode(',', $this->orderBy) : (array) $this->orderBy;
 388     foreach ($sortParams as $index => $item) {
 389       $item = trim($item);
 390       if ($item == '(1)') {
 391         continue;
 392       }
 393       $words = preg_split("/[\s]+/", $item);
 394       if ($words) {
 395         // Direction defaults to ASC unless DESC is specified
 396         $direction = strtoupper(\CRM_Utils_Array::value(1, $words, '')) == 'DESC' ? ' DESC' : '';
 397         $field = $this->getField($words[0]);
 398         if ($field) {
 399           $this->query->orderBy(self::MAIN_TABLE_ALIAS . '.' . $field['name'] . $direction, NULL, $index);
 400         }
 401         elseif (strpos($words[0], '.')) {
 402           $join = $this->addFkField($words[0], 'LEFT');
 403           if ($join) {
 404             $this->query->orderBy("`{$join[0]}`.`{$join[1]}`$direction", NULL, $index);
 405           }
 406         }
 407         else {
 408           throw new \API_Exception("Unknown field specified for sort. Cannot order by '$item'");
 409         }
 410       }
 411     }
 412   }
 413
 414   /**
 415    * @param string $side
 416    * @param string $tableName
 417    * @param string $tableAlias
 418    * @param array $conditions
 419    */
 420   public function join($side, $tableName, $tableAlias, $conditions) {
 421     // INNER JOINs take precedence over LEFT JOINs
 422     if ($side != 'LEFT' || !isset($this->joins[$tableAlias])) {
 423       $this->joins[$tableAlias] = $side;
 424       $this->query->join($tableAlias, "$side JOIN `$tableName` `$tableAlias` ON " . implode(' AND ', $conditions));
 425     }
 426   }
 427
 428   /**
 429    * Populate where clauses
 430    *
 431    * @throws \Civi\API\Exception\UnauthorizedException
 432    * @throws \Exception
 433    */
 434   abstract protected function buildWhereClause();
 435
 436   /**
 437    * Populate $this->selectFields
 438    *
 439    * @throws \Civi\API\Exception\UnauthorizedException
 440    */
 441   protected function buildSelectFields() {
 442     $return_all_fields = (empty($this->select) || !is_array($this->select));
 443     $return = $return_all_fields ? $this->entityFieldNames : $this->select;
 444     if ($return_all_fields || in_array('custom', $this->select)) {
 445       foreach (array_keys($this->apiFieldSpec) as $fieldName) {
 446         if (strpos($fieldName, 'custom_') === 0) {
 447           $return[] = $fieldName;
 448         }
 449       }
 450     }
 451
 452     // Always select the ID if the table has one.
 453     if (array_key_exists('id', $this->apiFieldSpec)) {
 454       $this->selectFields[self::MAIN_TABLE_ALIAS . ".id"] = "id";
 455     }
 456
 457     // core return fields
 458     foreach ($return as $fieldName) {
 459       $field = $this->getField($fieldName);
 460       if ($field && in_array($field['name'], $this->entityFieldNames)) {
 461         $this->selectFields[self::MAIN_TABLE_ALIAS . ".{$field['name']}"] = $field['name'];
 462       }
 463       elseif (strpos($fieldName, '.')) {
 464         $fkField = $this->addFkField($fieldName, 'LEFT');
 465         if ($fkField) {
 466           $this->selectFields[implode('.', $fkField)] = $fieldName;
 467         }
 468       }
 469       elseif ($field && strpos($fieldName, 'custom_') === 0) {
 470         list($table_name, $column_name) = $this->addCustomField($field, 'LEFT');
 471
 472         if ($field['data_type'] != 'ContactReference') {
 473           // 'ordinary' custom field. We will select the value as custom_XX.
 474           $this->selectFields["$table_name.$column_name"] = $fieldName;
 475         }
 476         else {
 477           // contact reference custom field. The ID will be stored in custom_XX_id.
 478           // custom_XX will contain the sort name of the contact.
 479           $this->query->join("c_$fieldName", "LEFT JOIN civicrm_contact c_$fieldName ON c_$fieldName.id = `$table_name`.`$column_name`");
 480           $this->selectFields["$table_name.$column_name"] = $fieldName . "_id";
 481           // We will call the contact table for the join c_XX.
 482           $this->selectFields["c_$fieldName.sort_name"] = $fieldName;
 483         }
 484       }
 485     }
 486   }
 487
 488   /**
 489    * Load entity fields
 490    * @return array
 491    */
 492   abstract protected function getFields();
 493
 494 }