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