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