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