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