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