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 = array_column($baoName::fields(), 'name');
  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 = $fkField['FKApiSpec'][$fieldName] ?? NULL;
 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
 221       // Skip if we don't have permission to access this field
 222       if ($this->checkPermissions && !empty($fieldInfo['permission']) && !\CRM_Core_Permission::check($fieldInfo['permission'])) {
 223         return NULL;
 224       }
 225
 226       $fkTable = \CRM_Core_DAO_AllCoreTables::getTableForClass($fkField['FKClassName']);
 227       $tableAlias = implode('_to_', $subStack) . "_to_$fkTable";
 228
 229       // Add acl condition
 230       $joinCondition = array_merge(
 231         ["$prev.$fk = $tableAlias.$keyColumn"],
 232         $this->getAclClause($tableAlias, \_civicrm_api3_get_BAO($fkField['FKApiName']), $subStack)
 233       );
 234
 235       if (!empty($fkField['FKCondition'])) {
 236         $joinCondition[] = str_replace($fkTable, $tableAlias, $fkField['FKCondition']);
 237       }
 238
 239       $this->join($side, $fkTable, $tableAlias, $joinCondition);
 240
 241       if (strpos($fieldName, 'custom_') === 0) {
 242         list($tableAlias, $fieldName) = $this->addCustomField($fieldInfo, $side, $tableAlias);
 243       }
 244
 245       // Get ready to recurse to the next level
 246       $fk = $fieldName;
 247       $fkField = &$fkField['FKApiSpec'][$fieldName];
 248       $prev = $tableAlias;
 249     }
 250     return [$tableAlias, $fieldName];
 251   }
 252
 253   /**
 254    * Get join info for dynamically-joined fields (e.g. "entity_id", "option_group")
 255    *
 256    * @param $fkField
 257    * @param $stack
 258    */
 259   protected function getJoinInfo(&$fkField, $stack) {
 260     if ($fkField['name'] == 'entity_id') {
 261       $entityTableParam = substr(implode('.', $stack), 0, -2) . 'table';
 262       $entityTable = $this->where[$entityTableParam] ?? NULL;
 263       if ($entityTable && is_string($entityTable) && \CRM_Core_DAO_AllCoreTables::getClassForTable($entityTable)) {
 264         $fkField['FKClassName'] = \CRM_Core_DAO_AllCoreTables::getClassForTable($entityTable);
 265         $fkField['FKApiName'] = \CRM_Core_DAO_AllCoreTables::getBriefName($fkField['FKClassName']);
 266       }
 267     }
 268     if (!empty($fkField['pseudoconstant']['optionGroupName'])) {
 269       $fkField['FKClassName'] = 'CRM_Core_DAO_OptionValue';
 270       $fkField['FKApiName'] = 'OptionValue';
 271       $fkField['FKKeyColumn'] = 'value';
 272       $fkField['FKCondition'] = "civicrm_option_value.option_group_id = (SELECT id FROM civicrm_option_group WHERE name = '{$fkField['pseudoconstant']['optionGroupName']}')";
 273     }
 274   }
 275
 276   /**
 277    * Joins onto a custom field
 278    *
 279    * Adds a join to the query to make this field available for use in a clause.
 280    *
 281    * @param array $customField
 282    * @param string $side
 283    * @param string $baseTable
 284    * @return array
 285    *   Returns the table and field name for adding this field to a SELECT or WHERE clause
 286    */
 287   protected function addCustomField($customField, $side, $baseTable = self::MAIN_TABLE_ALIAS) {
 288     $tableName = $customField["table_name"];
 289     $columnName = $customField["column_name"];
 290     $tableAlias = "{$baseTable}_to_$tableName";
 291     $this->join($side, $tableName, $tableAlias, ["`$tableAlias`.entity_id = `$baseTable`.id"]);
 292     return [$tableAlias, $columnName];
 293   }
 294
 295   /**
 296    * Fetch a field from the getFields list
 297    *
 298    * @param string $fieldName
 299    * @return array|null
 300    */
 301   abstract protected function getField($fieldName);
 302
 303   /**
 304    * Perform input validation on params that use the join syntax
 305    *
 306    * Arguably this should be done at the api wrapper level, but doing it here provides a bit more consistency
 307    * in that api permissions to perform the join are checked first.
 308    *
 309    * @param $fieldName
 310    * @param $value
 311    * @throws \Exception
 312    */
 313   protected function validateNestedInput($fieldName, &$value) {
 314     $stack = explode('.', $fieldName);
 315     $spec = $this->apiFieldSpec;
 316     $fieldName = array_pop($stack);
 317     foreach ($stack as $depth => $name) {
 318       $entity = $spec[$name]['FKApiName'];
 319       $spec = $spec[$name]['FKApiSpec'];
 320     }
 321     $params = [$fieldName => $value];
 322     \_civicrm_api3_validate_fields($entity, 'get', $params, $spec);
 323     $value = $params[$fieldName];
 324   }
 325
 326   /**
 327    * Check permission to join onto another api entity
 328    *
 329    * @param string $entity
 330    * @param array $fieldStack
 331    *   The stack of fields leading up to this join
 332    * @return bool
 333    */
 334   protected function checkPermissionToJoin($entity, $fieldStack) {
 335     if (!$this->checkPermissions) {
 336       return TRUE;
 337     }
 338     // Build an array of params that relate to the joined entity
 339     $params = [
 340       'version' => 3,
 341       'return' => [],
 342       'check_permissions' => $this->checkPermissions,
 343     ];
 344     $prefix = implode('.', $fieldStack) . '.';
 345     $len = strlen($prefix);
 346     foreach ($this->select as $key => $ret) {
 347       if (strpos($key, $prefix) === 0) {
 348         $params['return'][substr($key, $len)] = $ret;
 349       }
 350     }
 351     foreach ($this->where as $key => $param) {
 352       if (strpos($key, $prefix) === 0) {
 353         $params[substr($key, $len)] = $param;
 354       }
 355     }
 356
 357     return \Civi::service('civi_api_kernel')->runAuthorize($entity, 'get', $params);
 358   }
 359
 360   /**
 361    * Get acl clause for an entity
 362    *
 363    * @param string $tableAlias
 364    * @param \CRM_Core_DAO|string $baoName
 365    * @param array $stack
 366    * @return array
 367    */
 368   public function getAclClause($tableAlias, $baoName, $stack = []) {
 369     if (!$this->checkPermissions) {
 370       return [];
 371     }
 372     // Prevent (most) redundant acl sub clauses if they have already been applied to the main entity.
 373     // FIXME: Currently this only works 1 level deep, but tracking through multiple joins would increase complexity
 374     // and just doing it for the first join takes care of most acl clause deduping.
 375     if (count($stack) === 1 && in_array($stack[0], $this->aclFields)) {
 376       return [];
 377     }
 378     $clauses = $baoName::getSelectWhereClause($tableAlias);
 379     if (!$stack) {
 380       // Track field clauses added to the main entity
 381       $this->aclFields = array_keys($clauses);
 382     }
 383     return array_filter($clauses);
 384   }
 385
 386   /**
 387    * Orders the query by one or more fields
 388    *
 389    * @throws \API_Exception
 390    * @throws \Civi\API\Exception\UnauthorizedException
 391    */
 392   protected function buildOrderBy() {
 393     $sortParams = is_string($this->orderBy) ? explode(',', $this->orderBy) : (array) $this->orderBy;
 394     foreach ($sortParams as $index => $item) {
 395       $item = trim($item);
 396       if ($item == '(1)') {
 397         continue;
 398       }
 399       $words = preg_split("/[\s]+/", $item);
 400       if ($words) {
 401         // Direction defaults to ASC unless DESC is specified
 402         $direction = strtoupper(\CRM_Utils_Array::value(1, $words, '')) == 'DESC' ? ' DESC' : '';
 403         $field = $this->getField($words[0]);
 404         if ($field) {
 405           $this->query->orderBy(self::MAIN_TABLE_ALIAS . '.' . $field['name'] . $direction, NULL, $index);
 406         }
 407         elseif (strpos($words[0], '.')) {
 408           $join = $this->addFkField($words[0], 'LEFT');
 409           if ($join) {
 410             $this->query->orderBy("`{$join[0]}`.`{$join[1]}`$direction", NULL, $index);
 411           }
 412         }
 413         else {
 414           throw new \API_Exception("Unknown field specified for sort. Cannot order by '$item'");
 415         }
 416       }
 417     }
 418   }
 419
 420   /**
 421    * @param string $side
 422    * @param string $tableName
 423    * @param string $tableAlias
 424    * @param array $conditions
 425    */
 426   public function join($side, $tableName, $tableAlias, $conditions) {
 427     // INNER JOINs take precedence over LEFT JOINs
 428     if ($side != 'LEFT' || !isset($this->joins[$tableAlias])) {
 429       $this->joins[$tableAlias] = $side;
 430       $this->query->join($tableAlias, "$side JOIN `$tableName` `$tableAlias` ON " . implode(' AND ', $conditions));
 431     }
 432   }
 433
 434   /**
 435    * Populate where clauses
 436    *
 437    * @throws \Civi\API\Exception\UnauthorizedException
 438    * @throws \Exception
 439    */
 440   abstract protected function buildWhereClause();
 441
 442   /**
 443    * Populate $this->selectFields
 444    *
 445    * @throws \Civi\API\Exception\UnauthorizedException
 446    */
 447   protected function buildSelectFields() {
 448     $return_all_fields = (empty($this->select) || !is_array($this->select));
 449     $return = $return_all_fields ? $this->entityFieldNames : $this->select;
 450     if ($return_all_fields || in_array('custom', $this->select)) {
 451       foreach (array_keys($this->apiFieldSpec) as $fieldName) {
 452         if (strpos($fieldName, 'custom_') === 0) {
 453           $return[] = $fieldName;
 454         }
 455       }
 456     }
 457
 458     // Always select the ID if the table has one.
 459     if (array_key_exists('id', $this->apiFieldSpec)) {
 460       $this->selectFields[self::MAIN_TABLE_ALIAS . ".id"] = "id";
 461     }
 462
 463     // core return fields
 464     foreach ($return as $fieldName) {
 465       $field = $this->getField($fieldName);
 466       if ($field && in_array($field['name'], $this->entityFieldNames)) {
 467         $this->selectFields[self::MAIN_TABLE_ALIAS . ".{$field['name']}"] = $field['name'];
 468       }
 469       elseif (strpos($fieldName, '.')) {
 470         $fkField = $this->addFkField($fieldName, 'LEFT');
 471         if ($fkField) {
 472           $this->selectFields[implode('.', $fkField)] = $fieldName;
 473         }
 474       }
 475       elseif ($field && strpos($fieldName, 'custom_') === 0) {
 476         list($table_name, $column_name) = $this->addCustomField($field, 'LEFT');
 477
 478         if ($field['data_type'] != 'ContactReference') {
 479           // 'ordinary' custom field. We will select the value as custom_XX.
 480           $this->selectFields["$table_name.$column_name"] = $fieldName;
 481         }
 482         else {
 483           // contact reference custom field. The ID will be stored in custom_XX_id.
 484           // custom_XX will contain the sort name of the contact.
 485           $this->query->join("c_$fieldName", "LEFT JOIN civicrm_contact c_$fieldName ON c_$fieldName.id = `$table_name`.`$column_name`");
 486           $this->selectFields["$table_name.$column_name"] = $fieldName . "_id";
 487           // We will call the contact table for the join c_XX.
 488           $this->selectFields["c_$fieldName.sort_name"] = $fieldName;
 489         }
 490       }
 491     }
 492   }
 493
 494   /**
 495    * Load entity fields
 496    * @return array
 497    */
 498   abstract protected function getFields();
 499
 500 }