Civi/API/SelectQuery.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | CiviCRM version 4.7                                                |
   5  +--------------------------------------------------------------------+
   6  | Copyright CiviCRM LLC (c) 2004-2015                                |
   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 class SelectQuery {
  44
  45   const
  46     MAX_JOINS = 4,
  47     MAIN_TABLE_ALIAS = 'a';
  48
  49   /**
  50    * @var string
  51    */
  52   protected $entity;
  53   /**
  54    * @var array
  55    */
  56   protected $params;
  57   /**
  58    * @var array
  59    */
  60   protected $options;
  61   /**
  62    * @var bool
  63    */
  64   protected $isFillUniqueFields;
  65   /**
  66    * @var \CRM_Utils_SQL_Select
  67    */
  68   protected $query;
  69   /**
  70    * @var array
  71    */
  72   protected $apiFieldSpec;
  73   /**
  74    * @var array
  75    */
  76   protected $entityFieldNames;
  77   /**
  78    * @var array
  79    */
  80   protected $aclFields = array();
  81   /**
  82    * @var string|bool
  83    */
  84   protected $checkPermissions;
  85
  86   /**
  87    * @param string $baoName
  88    *   Name of BAO
  89    * @param array $params
  90    *   As passed into api get function.
  91    * @param bool $isFillUniqueFields
  92    *   Do we need to ensure unique fields continue to be populated for this api? (backward compatibility).
  93    */
  94   public function __construct($baoName, $params, $isFillUniqueFields) {
  95     $bao = new $baoName();
  96     $this->entity = _civicrm_api_get_entity_name_from_dao($bao);
  97     $this->params = $params;
  98     $this->isFillUniqueFields = $isFillUniqueFields;
  99     $this->checkPermissions = \CRM_Utils_Array::value('check_permissions', $this->params, FALSE);
 100     $this->options = _civicrm_api3_get_options_from_params($this->params);
 101
 102     $this->entityFieldNames = _civicrm_api3_field_names(_civicrm_api3_build_fields_array($bao));
 103     // Call this function directly instead of using the api wrapper to force unique field names off
 104     require_once 'api/v3/Generic.php';
 105     $apiSpec = \civicrm_api3_generic_getfields(array('entity' => $this->entity, 'version' => 3, 'params' => array('action' => 'get')), FALSE);
 106     $this->apiFieldSpec = $apiSpec['values'];
 107
 108     $this->query = \CRM_Utils_SQL_Select::from($bao->tableName() . ' ' . self::MAIN_TABLE_ALIAS);
 109     $bao->free();
 110
 111     // Add ACLs first to avoid redundant subclauses
 112     $this->query->where($this->getAclClause(self::MAIN_TABLE_ALIAS, $baoName));
 113   }
 114
 115   /**
 116    * Build & execute the query and return results array
 117    *
 118    * @return array
 119    * @throws \API_Exception
 120    * @throws \CRM_Core_Exception
 121    * @throws \Exception
 122    */
 123   public function run() {
 124     // $select_fields maps column names to the field names of the result values.
 125     $select_fields = $custom_fields = array();
 126
 127     // populate $select_fields
 128     $return_all_fields = (empty($this->options['return']) || !is_array($this->options['return']));
 129     $return = $return_all_fields ? array_fill_keys($this->entityFieldNames, 1) : $this->options['return'];
 130
 131     // core return fields
 132     foreach ($return as $field_name => $include) {
 133       if ($include) {
 134         $field = $this->getField($field_name);
 135         if ($field && in_array($field['name'], $this->entityFieldNames)) {
 136           $select_fields[self::MAIN_TABLE_ALIAS . ".{$field['name']}"] = $field['name'];
 137         }
 138         elseif ($include && strpos($field_name, '.')) {
 139           $fkField = $this->addFkField($field_name);
 140           if ($fkField) {
 141             $select_fields[implode('.', $fkField)] = $field_name;
 142           }
 143         }
 144       }
 145     }
 146
 147     // Do custom fields IF the params contain the word "custom" or we are returning *
 148     if ($return_all_fields || strpos(json_encode($this->params), 'custom')) {
 149       $custom_fields = _civicrm_api3_custom_fields_for_entity($this->entity);
 150       foreach ($custom_fields as $cf_id => $custom_field) {
 151         $field_name = "custom_$cf_id";
 152         if ($return_all_fields || !empty($this->options['return'][$field_name])
 153           ||
 154           // This is a tested format so we support it.
 155           !empty($this->options['return']['custom'])
 156         ) {
 157           list($table_name, $column_name) = $this->addCustomField($custom_field);
 158
 159           if ($custom_field["data_type"] != "ContactReference") {
 160             // 'ordinary' custom field. We will select the value as custom_XX.
 161             $select_fields["$table_name.$column_name"] = $field_name;
 162           }
 163           else {
 164             // contact reference custom field. The ID will be stored in custom_XX_id.
 165             // custom_XX will contain the sort name of the contact.
 166             $this->query->join("c_$cf_id", "LEFT JOIN civicrm_contact c_$cf_id ON c_$cf_id.id = `$table_name`.`$column_name`");
 167             $select_fields["$table_name.$column_name"] = $field_name . "_id";
 168             // We will call the contact table for the join c_XX.
 169             $select_fields["c_$cf_id.sort_name"] = $field_name;
 170           }
 171         }
 172       }
 173     }
 174     // Always select the ID.
 175     $select_fields[self::MAIN_TABLE_ALIAS . ".id"] = "id";
 176
 177     // populate where_clauses
 178     foreach ($this->params as $key => $value) {
 179       $table_name = NULL;
 180       $column_name = NULL;
 181
 182       if (substr($key, 0, 7) == 'filter.') {
 183         // Legacy support for old filter syntax per the test contract.
 184         // (Convert the style to the later one & then deal with them).
 185         $filterArray = explode('.', $key);
 186         $value = array($filterArray[1] => $value);
 187         $key = 'filters';
 188       }
 189
 190       // Legacy support for 'filter's construct.
 191       if ($key == 'filters') {
 192         foreach ($value as $filterKey => $filterValue) {
 193           if (substr($filterKey, -4, 4) == 'high') {
 194             $key = substr($filterKey, 0, -5);
 195             $value = array('<=' => $filterValue);
 196           }
 197
 198           if (substr($filterKey, -3, 3) == 'low') {
 199             $key = substr($filterKey, 0, -4);
 200             $value = array('>=' => $filterValue);
 201           }
 202
 203           if ($filterKey == 'is_current' || $filterKey == 'isCurrent') {
 204             // Is current is almost worth creating as a 'sql filter' in the DAO function since several entities have the concept.
 205             $todayStart = date('Ymd000000', strtotime('now'));
 206             $todayEnd = date('Ymd235959', strtotime('now'));
 207             $a = self::MAIN_TABLE_ALIAS;
 208             $this->query->where("($a.start_date <= '$todayStart' OR $a.start_date IS NULL)
 209               AND ($a.end_date >= '$todayEnd' OR $a.end_date IS NULL)
 210               AND a.is_active = 1");
 211           }
 212         }
 213       }
 214       // Ignore the "options" param if it is referring to api options and not a field in this entity
 215       if (
 216         $key === 'options' && is_array($value)
 217         && !in_array(\CRM_Utils_Array::first(array_keys($value)), \CRM_Core_DAO::acceptedSQLOperators())
 218       ) {
 219         continue;
 220       }
 221       $field = $this->getField($key);
 222       if ($field) {
 223         $key = $field['name'];
 224       }
 225       if (in_array($key, $this->entityFieldNames)) {
 226         $table_name = self::MAIN_TABLE_ALIAS;
 227         $column_name = $key;
 228       }
 229       elseif (($cf_id = \CRM_Core_BAO_CustomField::getKeyID($key)) != FALSE) {
 230         list($table_name, $column_name) = $this->addCustomField($custom_fields[$cf_id]);
 231       }
 232       elseif (strpos($key, '.')) {
 233         $fkInfo = $this->addFkField($key);
 234         if ($fkInfo) {
 235           list($table_name, $column_name) = $fkInfo;
 236           $this->validateNestedInput($key, $value);
 237         }
 238       }
 239       // I don't know why I had to specifically exclude 0 as a key - wouldn't the others have caught it?
 240       // We normally silently ignore null values passed in - if people want IS_NULL they can use acceptedSqlOperator syntax.
 241       if ((!$table_name) || empty($key) || is_null($value)) {
 242         // No valid filter field. This might be a chained call or something.
 243         // Just ignore this for the $where_clause.
 244         continue;
 245       }
 246       if (!is_array($value)) {
 247         $this->query->where(array("`$table_name`.`$column_name` = @value"), array(
 248           "@value" => $value,
 249         ));
 250       }
 251       else {
 252         // We expect only one element in the array, of the form
 253         // "operator" => "rhs".
 254         $operator = \CRM_Utils_Array::first(array_keys($value));
 255         if (!in_array($operator, \CRM_Core_DAO::acceptedSQLOperators())) {
 256           $this->query->where(array(
 257             "{$table_name}.{$column_name} = @value"), array("@value" => $value)
 258           );
 259         }
 260         else {
 261           $this->query->where(\CRM_Core_DAO::createSQLFilter("{$table_name}.{$column_name}", $value));
 262         }
 263       }
 264     }
 265
 266     if (!$this->options['is_count']) {
 267       foreach ($select_fields as $column => $alias) {
 268         $this->query->select("$column as `$alias`");
 269       }
 270     }
 271     else {
 272       $this->query->select("count(*) as c");
 273     }
 274
 275     // Order by
 276     if (!empty($this->options['sort'])) {
 277       $this->orderBy($this->options['sort']);
 278     }
 279
 280     // Limit
 281     if (!empty($this->options['limit']) || !empty($this->options['offset'])) {
 282       $this->query->limit($this->options['limit'], $this->options['offset']);
 283     }
 284
 285     $result_entities = array();
 286     $result_dao = \CRM_Core_DAO::executeQuery($this->query->toSQL());
 287
 288     while ($result_dao->fetch()) {
 289       if ($this->options['is_count']) {
 290         $result_dao->free();
 291         return (int) $result_dao->c;
 292       }
 293       $result_entities[$result_dao->id] = array();
 294       foreach ($select_fields as $column => $alias) {
 295         $returnName = $alias;
 296         $alias = str_replace('.', '_', $alias);
 297         if (property_exists($result_dao, $alias) && $result_dao->$alias != NULL) {
 298           $result_entities[$result_dao->id][$returnName] = $result_dao->$alias;
 299         }
 300         // Backward compatibility on fields names.
 301         if ($this->isFillUniqueFields && !empty($this->apiFieldSpec[$alias]['uniqueName'])) {
 302           $result_entities[$result_dao->id][$this->apiFieldSpec[$alias]['uniqueName']] = $result_dao->$alias;
 303         }
 304         foreach ($this->apiFieldSpec as $returnName => $spec) {
 305           if (empty($result_entities[$result_dao->id][$returnName]) && !empty($result_entities[$result_dao->id][$spec['name']])) {
 306             $result_entities[$result_dao->id][$returnName] = $result_entities[$result_dao->id][$spec['name']];
 307           }
 308         }
 309       };
 310     }
 311     $result_dao->free();
 312     return $result_entities;
 313   }
 314
 315   /**
 316    * @param \CRM_Utils_SQL_Select $sqlFragment
 317    * @return $this
 318    */
 319   public function merge($sqlFragment) {
 320     $this->query->merge($sqlFragment);
 321     return $this;
 322   }
 323
 324   /**
 325    * Joins onto an fk field
 326    *
 327    * Adds one or more joins to the query to make this field available for use in a clause.
 328    *
 329    * Enforces permissions at the api level and by appending the acl clause for that entity to the join.
 330    *
 331    * @param $fkFieldName
 332    * @return array|null
 333    *   Returns the table and field name for adding this field to a SELECT or WHERE clause
 334    * @throws \API_Exception
 335    * @throws \Civi\API\Exception\UnauthorizedException
 336    */
 337   private function addFkField($fkFieldName) {
 338     $stack = explode('.', $fkFieldName);
 339     if (count($stack) < 2) {
 340       return NULL;
 341     }
 342     $prev = self::MAIN_TABLE_ALIAS;
 343     foreach ($stack as $depth => $fieldName) {
 344       // Setup variables then skip the first level
 345       if (!$depth) {
 346         $fk = $fieldName;
 347         // We only join on core fields
 348         // @TODO: Custom contact ref fields could be supported too
 349         if (!in_array($fk, $this->entityFieldNames)) {
 350           return NULL;
 351         }
 352         $fkField = &$this->apiFieldSpec[$fk];
 353         continue;
 354       }
 355       // More than 4 joins deep seems excessive - DOS attack?
 356       if ($depth > self::MAX_JOINS) {
 357         throw new UnauthorizedException("Maximum number of joins exceeded in parameter $fkFieldName");
 358       }
 359       if (!isset($fkField['FKApiName']) && !isset($fkField['FKClassName'])) {
 360         // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it.
 361         return NULL;
 362       }
 363       $subStack = array_slice($stack, 0, $depth);
 364       // Ensure we have permission to access the other api
 365       if (!$this->checkPermissionToJoin($fkField['FKApiName'], $subStack)) {
 366         throw new UnauthorizedException("Authorization failed to join onto {$fkField['FKApiName']} api in parameter $fkFieldName");
 367       }
 368       if (!isset($fkField['FKApiSpec'])) {
 369         $fkField['FKApiSpec'] = \_civicrm_api_get_fields($fkField['FKApiName']);
 370       }
 371       $fieldInfo = \CRM_Utils_Array::value($fieldName, $fkField['FKApiSpec']);
 372
 373       // FIXME: What if the foreign key is not the "id" column?
 374       if (!$fieldInfo || !isset($fkField['FKApiSpec']['id'])) {
 375         // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it.
 376         return NULL;
 377       }
 378       $fkTable = \CRM_Core_DAO_AllCoreTables::getTableForClass($fkField['FKClassName']);
 379       $tableAlias = implode('_to_', $subStack) . "_to_$fkTable";
 380       $joinClause = "LEFT JOIN $fkTable $tableAlias ON $prev.$fk = $tableAlias.id";
 381
 382       // Add acl condition
 383       $joinCondition = $this->getAclClause($tableAlias, \_civicrm_api3_get_BAO($fkField['FKApiName']), $subStack);
 384       if ($joinCondition !== NULL) {
 385         $joinClause .= " AND $joinCondition";
 386       }
 387
 388       $this->query->join($tableAlias, $joinClause);
 389
 390       if (strpos($fieldName, 'custom_') === 0) {
 391         list($tableAlias, $fieldName) = $this->addCustomField($fieldInfo, $tableAlias);
 392       }
 393
 394       // Get ready to recurse to the next level
 395       $fk = $fieldName;
 396       $fkField = &$fkField['FKApiSpec'][$fieldName];
 397       $prev = $tableAlias;
 398     }
 399     return array($tableAlias, $fieldName);
 400   }
 401
 402   /**
 403    * Joins onto a custom field
 404    *
 405    * Adds a join to the query to make this field available for use in a clause.
 406    *
 407    * @param array $customField
 408    * @param string $baseTable
 409    * @return array
 410    *   Returns the table and field name for adding this field to a SELECT or WHERE clause
 411    */
 412   private function addCustomField($customField, $baseTable = self::MAIN_TABLE_ALIAS) {
 413     $tableName = $customField["table_name"];
 414     $columnName = $customField["column_name"];
 415     $tableAlias = "{$baseTable}_to_$tableName";
 416     $this->query->join($tableAlias, "LEFT JOIN `$tableName` `$tableAlias` ON `$tableAlias`.entity_id = `$baseTable`.id");
 417     return array($tableAlias, $columnName);
 418   }
 419
 420   /**
 421    * Fetch a field from the getFields list
 422    *
 423    * Searches by name, uniqueName, and api.aliases
 424    *
 425    * @param string $fieldName
 426    * @return array|null
 427    */
 428   private function getField($fieldName) {
 429     if (!$fieldName) {
 430       return NULL;
 431     }
 432     if (isset($this->apiFieldSpec[$fieldName])) {
 433       return $this->apiFieldSpec[$fieldName];
 434     }
 435     foreach ($this->apiFieldSpec as $field) {
 436       if (
 437         $fieldName == \CRM_Utils_Array::value('uniqueName', $field) ||
 438         array_search($fieldName, \CRM_Utils_Array::value('api.aliases', $field, array())) !== FALSE
 439       ) {
 440         return $field;
 441       }
 442     }
 443     return NULL;
 444   }
 445
 446   /**
 447    * Perform input validation on params that use the join syntax
 448    *
 449    * Arguably this should be done at the api wrapper level, but doing it here provides a bit more consistency
 450    * in that api permissions to perform the join are checked first.
 451    *
 452    * @param $fieldName
 453    * @param $value
 454    * @throws \Exception
 455    */
 456   private function validateNestedInput($fieldName, &$value) {
 457     $stack = explode('.', $fieldName);
 458     $spec = $this->apiFieldSpec;
 459     $fieldName = array_pop($stack);
 460     foreach ($stack as $depth => $name) {
 461       $entity = $spec[$name]['FKApiName'];
 462       $spec = $spec[$name]['FKApiSpec'];
 463     }
 464     $params = array($fieldName => $value);
 465     \_civicrm_api3_validate_fields($entity, 'get', $params, $spec);
 466     $value = $params[$fieldName];
 467   }
 468
 469   /**
 470    * Check permission to join onto another api entity
 471    *
 472    * @param string $entity
 473    * @param array $fieldStack
 474    *   The stack of fields leading up to this join
 475    * @return bool
 476    */
 477   private function checkPermissionToJoin($entity, $fieldStack) {
 478     if (!$this->checkPermissions) {
 479       return TRUE;
 480     }
 481     // Build an array of params that relate to the joined entity
 482     $params = array(
 483       'version' => 3,
 484       'return' => array(),
 485       'check_permissions' => $this->checkPermissions,
 486     );
 487     $prefix = implode('.', $fieldStack) . '.';
 488     $len = strlen($prefix);
 489     foreach ($this->options['return'] as $key => $ret) {
 490       if (strpos($key, $prefix) === 0) {
 491         $params['return'][substr($key, $len)] = $ret;
 492       }
 493     }
 494     foreach ($this->params as $key => $param) {
 495       if (strpos($key, $prefix) === 0) {
 496         $params[substr($key, $len)] = $param;
 497       }
 498     }
 499
 500     return \Civi::service('civi_api_kernel')->runAuthorize($entity, 'get', $params);
 501   }
 502
 503   /**
 504    * Get acl clause for an entity
 505    *
 506    * @param string $tableAlias
 507    * @param string $baoName
 508    * @param array $stack
 509    * @return null|string
 510    */
 511   private function getAclClause($tableAlias, $baoName, $stack = array()) {
 512     if (!$this->checkPermissions) {
 513       return NULL;
 514     }
 515     // Prevent (most) redundant acl sub clauses if they have already been applied to the main entity.
 516     // FIXME: Currently this only works 1 level deep, but tracking through multiple joins would increase complexity
 517     // and just doing it for the first join takes care of most acl clause deduping.
 518     if (count($stack) === 1 && in_array($stack[0], $this->aclFields)) {
 519       return NULL;
 520     }
 521     $clauses = $baoName::getSelectWhereClause($tableAlias);
 522     if (!$stack) {
 523       // Track field clauses added to the main entity
 524       $this->aclFields = array_keys($clauses);
 525     }
 526
 527     $clauses = array_filter($clauses);
 528     return $clauses ? implode(' AND ', $clauses) : NULL;
 529   }
 530
 531   /**
 532    * Orders the query by one or more fields
 533    *
 534    * e.g.
 535    * @code
 536    *   $this->orderBy(array('last_name DESC', 'birth_date'));
 537    * @endcode
 538    *
 539    * @param string|array $sortParams
 540    * @throws \API_Exception
 541    * @throws \Civi\API\Exception\UnauthorizedException
 542    */
 543   public function orderBy($sortParams) {
 544     $orderBy = array();
 545     foreach (is_array($sortParams) ? $sortParams : explode(',', $sortParams) as $item) {
 546       $words = preg_split("/[\s]+/", trim($item));
 547       if ($words) {
 548         // Direction defaults to ASC unless DESC is specified
 549         $direction = strtoupper(\CRM_Utils_Array::value(1, $words, '')) == 'DESC' ? ' DESC' : '';
 550         $field = $this->getField($words[0]);
 551         if ($field) {
 552           $orderBy[] = self::MAIN_TABLE_ALIAS . '.' . $field['name'] . $direction;
 553         }
 554         elseif (strpos($words[0], '.')) {
 555           $join = $this->addFkField($words[0]);
 556           if ($join) {
 557             $orderBy[] = "`{$join[0]}`.`{$join[1]}`$direction";
 558           }
 559         }
 560         else {
 561           throw new \API_Exception("Unknown field specified for sort. Cannot order by '$item'");
 562         }
 563       }
 564     }
 565     $this->query->orderBy($orderBy);
 566   }
 567
 568 }