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