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