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