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