Civi/API/SelectQuery.php

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