Civi/API/SelectQuery.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2016                                |
   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 = array();
  54   public $where = array();
  55   public $orderBy = array();
  56   public $limit;
  57   public $offset;
  58   /**
  59    * @var array
  60    */
  61   protected $selectFields = array();
  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 = array();
  74   /**
  75    * @var array
  76    */
  77   protected $apiFieldSpec;
  78   /**
  79    * @var array
  80    */
  81   protected $entityFieldNames;
  82   /**
  83    * @var array
  84    */
  85   protected $aclFields = array();
  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
 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 = array();
 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] = array();
 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       // FIXME: What if the foreign key is not the "id" column?
 234       if (!$fieldInfo || !isset($fkField['FKApiSpec']['id'])) {
 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         array("$prev.$fk = $tableAlias.id"),
 244         $this->getAclClause($tableAlias, \_civicrm_api3_get_BAO($fkField['FKApiName']), $subStack)
 245       );
 246
 247       $this->join($side, $fkTable, $tableAlias, $joinCondition);
 248
 249       if (strpos($fieldName, 'custom_') === 0) {
 250         list($tableAlias, $fieldName) = $this->addCustomField($fieldInfo, $side, $tableAlias);
 251       }
 252
 253       // Get ready to recurse to the next level
 254       $fk = $fieldName;
 255       $fkField = &$fkField['FKApiSpec'][$fieldName];
 256       $prev = $tableAlias;
 257     }
 258     return array($tableAlias, $fieldName);
 259   }
 260
 261   /**
 262    * Get join info for dynamically-joined fields (e.g. "entity_id")
 263    *
 264    * @param $fkField
 265    * @param $stack
 266    */
 267   protected function getJoinInfo(&$fkField, $stack) {
 268     if ($fkField['name'] == 'entity_id') {
 269       $entityTableParam = substr(implode('.', $stack), 0, -2) . 'table';
 270       $entityTable = \CRM_Utils_Array::value($entityTableParam, $this->where);
 271       if ($entityTable && is_string($entityTable) && \CRM_Core_DAO_AllCoreTables::getClassForTable($entityTable)) {
 272         $fkField['FKClassName'] = \CRM_Core_DAO_AllCoreTables::getClassForTable($entityTable);
 273         $fkField['FKApiName'] = \CRM_Core_DAO_AllCoreTables::getBriefName($fkField['FKClassName']);
 274       }
 275     }
 276   }
 277
 278   /**
 279    * Joins onto a custom field
 280    *
 281    * Adds a join to the query to make this field available for use in a clause.
 282    *
 283    * @param array $customField
 284    * @param string $side
 285    * @param string $baseTable
 286    * @return array
 287    *   Returns the table and field name for adding this field to a SELECT or WHERE clause
 288    */
 289   protected function addCustomField($customField, $side, $baseTable = self::MAIN_TABLE_ALIAS) {
 290     $tableName = $customField["table_name"];
 291     $columnName = $customField["column_name"];
 292     $tableAlias = "{$baseTable}_to_$tableName";
 293     $this->join($side, $tableName, $tableAlias, array("`$tableAlias`.entity_id = `$baseTable`.id"));
 294     return array($tableAlias, $columnName);
 295   }
 296
 297   /**
 298    * Fetch a field from the getFields list
 299    *
 300    * @param string $fieldName
 301    * @return array|null
 302    */
 303   abstract protected function getField($fieldName);
 304
 305   /**
 306    * Perform input validation on params that use the join syntax
 307    *
 308    * Arguably this should be done at the api wrapper level, but doing it here provides a bit more consistency
 309    * in that api permissions to perform the join are checked first.
 310    *
 311    * @param $fieldName
 312    * @param $value
 313    * @throws \Exception
 314    */
 315   protected function validateNestedInput($fieldName, &$value) {
 316     $stack = explode('.', $fieldName);
 317     $spec = $this->apiFieldSpec;
 318     $fieldName = array_pop($stack);
 319     foreach ($stack as $depth => $name) {
 320       $entity = $spec[$name]['FKApiName'];
 321       $spec = $spec[$name]['FKApiSpec'];
 322     }
 323     $params = array($fieldName => $value);
 324     \_civicrm_api3_validate_fields($entity, 'get', $params, $spec);
 325     $value = $params[$fieldName];
 326   }
 327
 328   /**
 329    * Check permission to join onto another api entity
 330    *
 331    * @param string $entity
 332    * @param array $fieldStack
 333    *   The stack of fields leading up to this join
 334    * @return bool
 335    */
 336   protected function checkPermissionToJoin($entity, $fieldStack) {
 337     if (!$this->checkPermissions) {
 338       return TRUE;
 339     }
 340     // Build an array of params that relate to the joined entity
 341     $params = array(
 342       'version' => 3,
 343       'return' => array(),
 344       'check_permissions' => $this->checkPermissions,
 345     );
 346     $prefix = implode('.', $fieldStack) . '.';
 347     $len = strlen($prefix);
 348     foreach ($this->select as $key => $ret) {
 349       if (strpos($key, $prefix) === 0) {
 350         $params['return'][substr($key, $len)] = $ret;
 351       }
 352     }
 353     foreach ($this->where as $key => $param) {
 354       if (strpos($key, $prefix) === 0) {
 355         $params[substr($key, $len)] = $param;
 356       }
 357     }
 358
 359     return \Civi::service('civi_api_kernel')->runAuthorize($entity, 'get', $params);
 360   }
 361
 362   /**
 363    * Get acl clause for an entity
 364    *
 365    * @param string $tableAlias
 366    * @param string $baoName
 367    * @param array $stack
 368    * @return array
 369    */
 370   protected function getAclClause($tableAlias, $baoName, $stack = array()) {
 371     if (!$this->checkPermissions) {
 372       return array();
 373     }
 374     // Prevent (most) redundant acl sub clauses if they have already been applied to the main entity.
 375     // FIXME: Currently this only works 1 level deep, but tracking through multiple joins would increase complexity
 376     // and just doing it for the first join takes care of most acl clause deduping.
 377     if (count($stack) === 1 && in_array($stack[0], $this->aclFields)) {
 378       return array();
 379     }
 380     $clauses = $baoName::getSelectWhereClause($tableAlias);
 381     if (!$stack) {
 382       // Track field clauses added to the main entity
 383       $this->aclFields = array_keys($clauses);
 384     }
 385     return array_filter($clauses);
 386   }
 387
 388   /**
 389    * Orders the query by one or more fields
 390    *
 391    * @throws \API_Exception
 392    * @throws \Civi\API\Exception\UnauthorizedException
 393    */
 394   protected function buildOrderBy() {
 395     $orderBy = array();
 396     $sortParams = is_string($this->orderBy) ? explode(',', $this->orderBy) : (array) $this->orderBy;
 397     foreach ($sortParams as $item) {
 398       $words = preg_split("/[\s]+/", trim($item));
 399       if ($words) {
 400         // Direction defaults to ASC unless DESC is specified
 401         $direction = strtoupper(\CRM_Utils_Array::value(1, $words, '')) == 'DESC' ? ' DESC' : '';
 402         $field = $this->getField($words[0]);
 403         if ($field) {
 404           $orderBy[] = self::MAIN_TABLE_ALIAS . '.' . $field['name'] . $direction;
 405         }
 406         elseif (strpos($words[0], '.')) {
 407           $join = $this->addFkField($words[0], 'LEFT');
 408           if ($join) {
 409             $orderBy[] = "`{$join[0]}`.`{$join[1]}`$direction";
 410           }
 411         }
 412         else {
 413           throw new \API_Exception("Unknown field specified for sort. Cannot order by '$item'");
 414         }
 415       }
 416     }
 417     $this->query->orderBy($orderBy);
 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.
 459     $this->selectFields[self::MAIN_TABLE_ALIAS . ".id"] = "id";
 460
 461     // core return fields
 462     foreach ($return as $fieldName) {
 463       $field = $this->getField($fieldName);
 464       if ($field && in_array($field['name'], $this->entityFieldNames)) {
 465         $this->selectFields[self::MAIN_TABLE_ALIAS . ".{$field['name']}"] = $field['name'];
 466       }
 467       elseif (strpos($fieldName, '.')) {
 468         $fkField = $this->addFkField($fieldName, 'LEFT');
 469         if ($fkField) {
 470           $this->selectFields[implode('.', $fkField)] = $fieldName;
 471         }
 472       }
 473       elseif ($field && strpos($fieldName, 'custom_') === 0) {
 474         list($table_name, $column_name) = $this->addCustomField($field, 'LEFT');
 475
 476         if ($field['data_type'] != 'ContactReference') {
 477           // 'ordinary' custom field. We will select the value as custom_XX.
 478           $this->selectFields["$table_name.$column_name"] = $fieldName;
 479         }
 480         else {
 481           // contact reference custom field. The ID will be stored in custom_XX_id.
 482           // custom_XX will contain the sort name of the contact.
 483           $this->query->join("c_$fieldName", "LEFT JOIN civicrm_contact c_$fieldName ON c_$fieldName.id = `$table_name`.`$column_name`");
 484           $this->selectFields["$table_name.$column_name"] = $fieldName . "_id";
 485           // We will call the contact table for the join c_XX.
 486           $this->selectFields["c_$fieldName.sort_name"] = $fieldName;
 487         }
 488       }
 489     }
 490   }
 491
 492   /**
 493    * Load entity fields
 494    * @return array
 495    */
 496   abstract protected function getFields();
 497
 498 }