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