Civi/Api4/Query/Api4SelectQuery.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 namespace Civi\Api4\Query;
  13
  14 use Civi\API\SelectQuery;
  15 use Civi\Api4\Service\Schema\Joinable\CustomGroupJoinable;
  16 use Civi\Api4\Service\Schema\Joinable\Joinable;
  17 use Civi\Api4\Utils\FormattingUtil;
  18 use Civi\Api4\Utils\CoreUtil;
  19 use Civi\Api4\Utils\SelectUtil;
  20 use CRM_Core_DAO_AllCoreTables as AllCoreTables;
  21
  22 /**
  23  * A query `node` may be in one of three formats:
  24  *
  25  * * leaf: [$fieldName, $operator, $criteria]
  26  * * negated: ['NOT', $node]
  27  * * branch: ['OR|NOT', [$node, $node, ...]]
  28  *
  29  * Leaf operators are one of:
  30  *
  31  * * '=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=",
  32  * * "NOT LIKE", 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN',
  33  * * 'IS NOT NULL', or 'IS NULL'.
  34  */
  35 class Api4SelectQuery extends SelectQuery {
  36
  37   /**
  38    * @var int
  39    */
  40   protected $apiVersion = 4;
  41
  42   /**
  43    * @var \Civi\Api4\Service\Schema\Joinable\Joinable[]
  44    *   The joinable tables that have been joined so far
  45    */
  46   protected $joinedTables = [];
  47
  48   /**
  49    * @var array
  50    * [alias => expr][]
  51    */
  52   protected $selectAliases = [];
  53
  54   /**
  55    * If set to an array, this will start collecting debug info.
  56    *
  57    * @var null|array
  58    */
  59   public $debugOutput = NULL;
  60
  61   /**
  62    * @var array
  63    */
  64   public $groupBy = [];
  65
  66   public $forceSelectId = TRUE;
  67
  68   /**
  69    * @var array
  70    */
  71   public $having = [];
  72
  73   /**
  74    * @param \Civi\Api4\Generic\DAOGetAction $apiGet
  75    */
  76   public function __construct($apiGet) {
  77     $this->entity = $apiGet->getEntityName();
  78     $this->checkPermissions = $apiGet->getCheckPermissions();
  79     $this->select = $apiGet->getSelect();
  80     $this->where = $apiGet->getWhere();
  81     $this->groupBy = $apiGet->getGroupBy();
  82     $this->orderBy = $apiGet->getOrderBy();
  83     $this->limit = $apiGet->getLimit();
  84     $this->offset = $apiGet->getOffset();
  85     $this->having = $apiGet->getHaving();
  86     // Always select ID of main table unless grouping is used
  87     $this->forceSelectId = !$this->groupBy;
  88     if ($apiGet->getDebug()) {
  89       $this->debugOutput =& $apiGet->_debugOutput;
  90     }
  91     $baoName = CoreUtil::getBAOFromApiName($this->entity);
  92     $this->entityFieldNames = array_column($baoName::fields(), 'name');
  93     foreach ($apiGet->entityFields() as $path => $field) {
  94       $field['sql_name'] = '`' . self::MAIN_TABLE_ALIAS . '`.`' . $field['column_name'] . '`';
  95       $this->addSpecField($path, $field);
  96     }
  97
  98     $this->constructQueryObject($baoName);
  99
 100     // Add ACLs first to avoid redundant subclauses
 101     $this->query->where($this->getAclClause(self::MAIN_TABLE_ALIAS, $baoName));
 102   }
 103
 104   /**
 105    * Builds final sql statement after all params are set.
 106    *
 107    * @return string
 108    * @throws \API_Exception
 109    * @throws \CRM_Core_Exception
 110    * @throws \Civi\API\Exception\UnauthorizedException
 111    */
 112   public function getSql() {
 113     $this->buildSelectClause();
 114     $this->buildWhereClause();
 115     $this->buildOrderBy();
 116     $this->buildLimit();
 117     $this->buildGroupBy();
 118     $this->buildHavingClause();
 119     return $this->query->toSQL();
 120   }
 121
 122   /**
 123    * Why walk when you can
 124    *
 125    * @return array|int
 126    */
 127   public function run() {
 128     $results = [];
 129     $sql = $this->getSql();
 130     if (is_array($this->debugOutput)) {
 131       $this->debugOutput['sql'][] = $sql;
 132     }
 133     $query = \CRM_Core_DAO::executeQuery($sql);
 134     while ($query->fetch()) {
 135       if (in_array('row_count', $this->select)) {
 136         $results[]['row_count'] = (int) $query->c;
 137         break;
 138       }
 139       $result = [];
 140       foreach ($this->selectAliases as $alias => $expr) {
 141         $returnName = $alias;
 142         $alias = str_replace('.', '_', $alias);
 143         $result[$returnName] = property_exists($query, $alias) ? $query->$alias : NULL;
 144       }
 145       $results[] = $result;
 146     }
 147     FormattingUtil::formatOutputValues($results, $this->getApiFieldSpec(), $this->getEntity());
 148     return $results;
 149   }
 150
 151   protected function buildSelectClause() {
 152     // An empty select is the same as *
 153     if (empty($this->select)) {
 154       $this->select = $this->entityFieldNames;
 155     }
 156     elseif (in_array('row_count', $this->select)) {
 157       $this->query->select("COUNT(*) AS `c`");
 158       return;
 159     }
 160     else {
 161       if ($this->forceSelectId) {
 162         $this->select = array_merge(['id'], $this->select);
 163       }
 164
 165       // Expand wildcards in joins (the api wrapper already expanded non-joined wildcards)
 166       $wildFields = array_filter($this->select, function($item) {
 167         return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE && strpos($item, '(') === FALSE && strpos($item, ' ') === FALSE;
 168       });
 169       foreach ($wildFields as $item) {
 170         $pos = array_search($item, array_values($this->select));
 171         $this->autoJoinFK($item);
 172         $matches = SelectUtil::getMatchingFields($item, array_keys($this->apiFieldSpec));
 173         array_splice($this->select, $pos, 1, $matches);
 174       }
 175       $this->select = array_unique($this->select);
 176     }
 177     foreach ($this->select as $item) {
 178       $expr = SqlExpression::convert($item, TRUE);
 179       $valid = TRUE;
 180       foreach ($expr->getFields() as $fieldName) {
 181         $field = $this->getField($fieldName);
 182         // Remove expressions with unknown fields without raising an error
 183         if (!$field) {
 184           $this->select = array_diff($this->select, [$item]);
 185           if (is_array($this->debugOutput)) {
 186             $this->debugOutput['undefined_fields'][] = $fieldName;
 187           }
 188           $valid = FALSE;
 189         }
 190       }
 191       if ($valid) {
 192         $alias = $expr->getAlias();
 193         if ($alias != $expr->getExpr() && isset($this->apiFieldSpec[$alias])) {
 194           throw new \API_Exception('Cannot use existing field name as alias');
 195         }
 196         $this->selectAliases[$alias] = $expr->getExpr();
 197         $this->query->select($expr->render($this->apiFieldSpec) . " AS `$alias`");
 198       }
 199     }
 200   }
 201
 202   /**
 203    * @inheritDoc
 204    */
 205   protected function buildWhereClause() {
 206     foreach ($this->where as $clause) {
 207       $this->query->where($this->treeWalkClauses($clause, 'WHERE'));
 208     }
 209   }
 210
 211   /**
 212    * Build HAVING clause.
 213    *
 214    * Every expression referenced must also be in the SELECT clause.
 215    */
 216   protected function buildHavingClause() {
 217     foreach ($this->having as $clause) {
 218       $this->query->having($this->treeWalkClauses($clause, 'HAVING'));
 219     }
 220   }
 221
 222   /**
 223    * @inheritDoc
 224    */
 225   protected function buildOrderBy() {
 226     foreach ($this->orderBy as $item => $dir) {
 227       if ($dir !== 'ASC' && $dir !== 'DESC') {
 228         throw new \API_Exception("Invalid sort direction. Cannot order by $item $dir");
 229       }
 230       $expr = SqlExpression::convert($item);
 231       foreach ($expr->getFields() as $fieldName) {
 232         $this->getField($fieldName, TRUE);
 233       }
 234       $this->query->orderBy($expr->render($this->apiFieldSpec) . " $dir");
 235     }
 236   }
 237
 238   /**
 239    * @throws \CRM_Core_Exception
 240    */
 241   protected function buildLimit() {
 242     if (!empty($this->limit) || !empty($this->offset)) {
 243       // If limit is 0, mysql will actually return 0 results. Instead set to maximum possible.
 244       $this->query->limit($this->limit ?: '18446744073709551615', $this->offset);
 245     }
 246   }
 247
 248   /**
 249    * Adds GROUP BY clause to query
 250    */
 251   protected function buildGroupBy() {
 252     foreach ($this->groupBy as $item) {
 253       $expr = SqlExpression::convert($item);
 254       foreach ($expr->getFields() as $fieldName) {
 255         $this->getField($fieldName, TRUE);
 256       }
 257       $this->query->groupBy($expr->render($this->apiFieldSpec));
 258     }
 259   }
 260
 261   /**
 262    * Recursively validate and transform a branch or leaf clause array to SQL.
 263    *
 264    * @param array $clause
 265    * @param string $type
 266    *   WHERE|HAVING
 267    * @return string SQL where clause
 268    *
 269    * @throws \API_Exception
 270    * @uses composeClause() to generate the SQL etc.
 271    */
 272   protected function treeWalkClauses($clause, $type) {
 273     switch ($clause[0]) {
 274       case 'OR':
 275       case 'AND':
 276         // handle branches
 277         if (count($clause[1]) === 1) {
 278           // a single set so AND|OR is immaterial
 279           return $this->treeWalkClauses($clause[1][0], $type);
 280         }
 281         else {
 282           $sql_subclauses = [];
 283           foreach ($clause[1] as $subclause) {
 284             $sql_subclauses[] = $this->treeWalkClauses($subclause, $type);
 285           }
 286           return '(' . implode("\n" . $clause[0], $sql_subclauses) . ')';
 287         }
 288
 289       case 'NOT':
 290         // If we get a group of clauses with no operator, assume AND
 291         if (!is_string($clause[1][0])) {
 292           $clause[1] = ['AND', $clause[1]];
 293         }
 294         return 'NOT (' . $this->treeWalkClauses($clause[1], $type) . ')';
 295
 296       default:
 297         return $this->composeClause($clause, $type);
 298     }
 299   }
 300
 301   /**
 302    * Validate and transform a leaf clause array to SQL.
 303    * @param array $clause [$fieldName, $operator, $criteria]
 304    * @param string $type
 305    *   WHERE|HAVING
 306    * @return string SQL
 307    * @throws \API_Exception
 308    * @throws \Exception
 309    */
 310   protected function composeClause(array $clause, string $type) {
 311     // Pad array for unary operators
 312     list($expr, $operator, $value) = array_pad($clause, 3, NULL);
 313
 314     // For WHERE clause, expr must be the name of a field.
 315     if ($type === 'WHERE') {
 316       $field = $this->getField($expr, TRUE);
 317       FormattingUtil::formatInputValue($value, $expr, $field);
 318       $fieldAlias = $field['sql_name'];
 319     }
 320     // For HAVING, expr must be an item in the SELECT clause
 321     else {
 322       // Expr references a fieldName or alias
 323       if (isset($this->selectAliases[$expr])) {
 324         $fieldAlias = $expr;
 325         // Attempt to format if this is a real field
 326         if (isset($this->apiFieldSpec[$expr])) {
 327           FormattingUtil::formatInputValue($value, $expr, $this->apiFieldSpec[$expr]);
 328         }
 329       }
 330       // Expr references a non-field expression like a function; convert to alias
 331       elseif (in_array($expr, $this->selectAliases)) {
 332         $fieldAlias = array_search($expr, $this->selectAliases);
 333       }
 334       // If either the having or select field contains a pseudoconstant suffix, match and perform substitution
 335       else {
 336         list($fieldName) = explode(':', $expr);
 337         foreach ($this->selectAliases as $selectAlias => $selectExpr) {
 338           list($selectField) = explode(':', $selectAlias);
 339           if ($selectAlias === $selectExpr && $fieldName === $selectField && isset($this->apiFieldSpec[$fieldName])) {
 340             FormattingUtil::formatInputValue($value, $expr, $this->apiFieldSpec[$fieldName]);
 341             $fieldAlias = $selectAlias;
 342             break;
 343           }
 344         }
 345       }
 346       if (!isset($fieldAlias)) {
 347         throw new \API_Exception("Invalid expression in HAVING clause: '$expr'. Must use a value from SELECT clause.");
 348       }
 349       $fieldAlias = '`' . $fieldAlias . '`';
 350     }
 351
 352     $sql_clause = \CRM_Core_DAO::createSQLFilter($fieldAlias, [$operator => $value]);
 353     if ($sql_clause === NULL) {
 354       throw new \API_Exception("Invalid value in $type clause for '$expr'");
 355     }
 356     return $sql_clause;
 357   }
 358
 359   /**
 360    * @inheritDoc
 361    */
 362   protected function getFields() {
 363     return $this->apiFieldSpec;
 364   }
 365
 366   /**
 367    * Fetch a field from the getFields list
 368    *
 369    * @param string $expr
 370    * @param bool $strict
 371    *   In strict mode, this will throw an exception if the field doesn't exist
 372    *
 373    * @return string|null
 374    * @throws \API_Exception
 375    */
 376   public function getField($expr, $strict = FALSE) {
 377     // If the expression contains a pseudoconstant filter like activity_type_id:label,
 378     // strip it to look up the base field name, then add the field:filter key to apiFieldSpec
 379     $col = strpos($expr, ':');
 380     $fieldName = $col ? substr($expr, 0, $col) : $expr;
 381     // Perform join if field not yet available - this will add it to apiFieldSpec
 382     if (!isset($this->apiFieldSpec[$fieldName]) && strpos($fieldName, '.')) {
 383       $this->autoJoinFK($fieldName);
 384     }
 385     $field = $this->apiFieldSpec[$fieldName] ?? NULL;
 386     if ($strict && !$field) {
 387       throw new \API_Exception("Invalid field '$fieldName'");
 388     }
 389     $this->apiFieldSpec[$expr] = $field;
 390     return $field;
 391   }
 392
 393   /**
 394    * Joins a path and adds all fields in the joined entity to apiFieldSpec
 395    *
 396    * @param $key
 397    * @throws \API_Exception
 398    * @throws \Exception
 399    */
 400   protected function autoJoinFK($key) {
 401     if (isset($this->apiFieldSpec[$key])) {
 402       return;
 403     }
 404
 405     $pathArray = explode('.', $key);
 406
 407     /** @var \Civi\Api4\Service\Schema\Joiner $joiner */
 408     $joiner = \Civi::container()->get('joiner');
 409     // The last item in the path is the field name. We don't care about that; we'll add all fields from the joined entity.
 410     array_pop($pathArray);
 411     $pathString = implode('.', $pathArray);
 412
 413     if (!$joiner->canAutoJoin($this->getFrom(), $pathString)) {
 414       return;
 415     }
 416
 417     $joinPath = $joiner->join($this, $pathString);
 418
 419     $lastLink = array_pop($joinPath);
 420
 421     // Custom field names are already prefixed
 422     $isCustom = $lastLink instanceof CustomGroupJoinable;
 423     if ($isCustom) {
 424       array_pop($pathArray);
 425     }
 426     $prefix = $pathArray ? implode('.', $pathArray) . '.' : '';
 427     // Cache field info for retrieval by $this->getField()
 428     $joinEntity = $lastLink->getEntity();
 429     foreach ($lastLink->getEntityFields() as $fieldObject) {
 430       $fieldArray = ['entity' => $joinEntity] + $fieldObject->toArray();
 431       $fieldArray['sql_name'] = '`' . $lastLink->getAlias() . '`.`' . $fieldArray['column_name'] . '`';
 432       $fieldArray['is_custom'] = $isCustom;
 433       $fieldArray['is_join'] = TRUE;
 434       $this->addSpecField($prefix . $fieldArray['name'], $fieldArray);
 435     }
 436   }
 437
 438   /**
 439    * @param \Civi\Api4\Service\Schema\Joinable\Joinable $joinable
 440    *
 441    * @return $this
 442    */
 443   public function addJoinedTable(Joinable $joinable) {
 444     $this->joinedTables[] = $joinable;
 445
 446     return $this;
 447   }
 448
 449   /**
 450    * @return FALSE|string
 451    */
 452   public function getFrom() {
 453     return AllCoreTables::getTableForClass(AllCoreTables::getFullName($this->entity));
 454   }
 455
 456   /**
 457    * @return string
 458    */
 459   public function getEntity() {
 460     return $this->entity;
 461   }
 462
 463   /**
 464    * @return array
 465    */
 466   public function getSelect() {
 467     return $this->select;
 468   }
 469
 470   /**
 471    * @return array
 472    */
 473   public function getWhere() {
 474     return $this->where;
 475   }
 476
 477   /**
 478    * @return array
 479    */
 480   public function getOrderBy() {
 481     return $this->orderBy;
 482   }
 483
 484   /**
 485    * @return mixed
 486    */
 487   public function getLimit() {
 488     return $this->limit;
 489   }
 490
 491   /**
 492    * @return mixed
 493    */
 494   public function getOffset() {
 495     return $this->offset;
 496   }
 497
 498   /**
 499    * @return array
 500    */
 501   public function getSelectFields() {
 502     return $this->selectFields;
 503   }
 504
 505   /**
 506    * @return bool
 507    */
 508   public function isFillUniqueFields() {
 509     return $this->isFillUniqueFields;
 510   }
 511
 512   /**
 513    * @return \CRM_Utils_SQL_Select
 514    */
 515   public function getQuery() {
 516     return $this->query;
 517   }
 518
 519   /**
 520    * @return array
 521    */
 522   public function getJoins() {
 523     return $this->joins;
 524   }
 525
 526   /**
 527    * @return array
 528    */
 529   public function getApiFieldSpec() {
 530     return $this->apiFieldSpec;
 531   }
 532
 533   /**
 534    * @return array
 535    */
 536   public function getEntityFieldNames() {
 537     return $this->entityFieldNames;
 538   }
 539
 540   /**
 541    * @return array
 542    */
 543   public function getAclFields() {
 544     return $this->aclFields;
 545   }
 546
 547   /**
 548    * @return bool|string
 549    */
 550   public function getCheckPermissions() {
 551     return $this->checkPermissions;
 552   }
 553
 554   /**
 555    * @return int
 556    */
 557   public function getApiVersion() {
 558     return $this->apiVersion;
 559   }
 560
 561   /**
 562    * @return \Civi\Api4\Service\Schema\Joinable\Joinable[]
 563    */
 564   public function getJoinedTables() {
 565     return $this->joinedTables;
 566   }
 567
 568   /**
 569    * @return \Civi\Api4\Service\Schema\Joinable\Joinable
 570    */
 571   public function getJoinedTable($alias) {
 572     foreach ($this->joinedTables as $join) {
 573       if ($join->getAlias() == $alias) {
 574         return $join;
 575       }
 576     }
 577   }
 578
 579   /**
 580    * Get table name on basis of entity
 581    *
 582    * @param string $baoName
 583    *
 584    * @return void
 585    */
 586   public function constructQueryObject($baoName) {
 587     if (strstr($this->entity, 'Custom_')) {
 588       $this->query = \CRM_Utils_SQL_Select::from(CoreUtil::getCustomTableByName(str_replace('Custom_', '', $this->entity)) . ' ' . self::MAIN_TABLE_ALIAS);
 589       $this->entityFieldNames = array_keys($this->apiFieldSpec);
 590     }
 591     else {
 592       $bao = new $baoName();
 593       $this->query = \CRM_Utils_SQL_Select::from($bao->tableName() . ' ' . self::MAIN_TABLE_ALIAS);
 594     }
 595   }
 596
 597   /**
 598    * @param $path
 599    * @param $field
 600    */
 601   private function addSpecField($path, $field) {
 602     // Only add field to spec if we have permission
 603     if ($this->checkPermissions && !empty($field['permission']) && !\CRM_Core_Permission::check($field['permission'])) {
 604       $this->apiFieldSpec[$path] = FALSE;
 605       return;
 606     }
 607     $defaults = [];
 608     $defaults['is_custom'] = $defaults['is_join'] = FALSE;
 609     $field += $defaults;
 610     $this->apiFieldSpec[$path] = $field;
 611   }
 612
 613 }