| 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\Api4\Service\Schema\Joinable\CustomGroupJoinable; |
| 15 | use Civi\Api4\Utils\FormattingUtil; |
| 16 | use Civi\Api4\Utils\CoreUtil; |
| 17 | use Civi\Api4\Utils\SelectUtil; |
| 18 | |
| 19 | /** |
| 20 | * A query `node` may be in one of three formats: |
| 21 | * |
| 22 | * * leaf: [$fieldName, $operator, $criteria] |
| 23 | * * negated: ['NOT', $node] |
| 24 | * * branch: ['OR|NOT', [$node, $node, ...]] |
| 25 | * |
| 26 | * Leaf operators are one of: |
| 27 | * |
| 28 | * * '=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=", |
| 29 | * * 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', |
| 30 | * * 'IS NOT NULL', or 'IS NULL', 'CONTAINS'. |
| 31 | */ |
| 32 | class Api4SelectQuery { |
| 33 | |
| 34 | const |
| 35 | MAIN_TABLE_ALIAS = 'a', |
| 36 | UNLIMITED = '18446744073709551615'; |
| 37 | |
| 38 | /** |
| 39 | * @var \CRM_Utils_SQL_Select |
| 40 | */ |
| 41 | protected $query; |
| 42 | |
| 43 | /** |
| 44 | * @var array |
| 45 | */ |
| 46 | protected $joins = []; |
| 47 | |
| 48 | /** |
| 49 | * @var array[] |
| 50 | */ |
| 51 | protected $apiFieldSpec; |
| 52 | |
| 53 | /** |
| 54 | * @var array |
| 55 | */ |
| 56 | protected $entityFieldNames = []; |
| 57 | |
| 58 | /** |
| 59 | * @var array |
| 60 | */ |
| 61 | protected $aclFields = []; |
| 62 | |
| 63 | /** |
| 64 | * @var \Civi\Api4\Generic\DAOGetAction |
| 65 | */ |
| 66 | private $api; |
| 67 | |
| 68 | /** |
| 69 | * @var array |
| 70 | * [alias => expr][] |
| 71 | */ |
| 72 | protected $selectAliases = []; |
| 73 | |
| 74 | /** |
| 75 | * @var bool |
| 76 | */ |
| 77 | public $forceSelectId = TRUE; |
| 78 | |
| 79 | /** |
| 80 | * @param \Civi\Api4\Generic\DAOGetAction $apiGet |
| 81 | */ |
| 82 | public function __construct($apiGet) { |
| 83 | $this->api = $apiGet; |
| 84 | |
| 85 | // Always select ID of main table unless grouping by something else |
| 86 | $this->forceSelectId = !$this->getGroupBy() || $this->getGroupBy() === ['id']; |
| 87 | |
| 88 | // Build field lists |
| 89 | foreach ($this->api->entityFields() as $field) { |
| 90 | $this->entityFieldNames[] = $field['name']; |
| 91 | $field['sql_name'] = '`' . self::MAIN_TABLE_ALIAS . '`.`' . $field['column_name'] . '`'; |
| 92 | $this->addSpecField($field['name'], $field); |
| 93 | } |
| 94 | |
| 95 | $tableName = CoreUtil::getTableName($this->getEntity()); |
| 96 | $this->query = \CRM_Utils_SQL_Select::from($tableName . ' ' . self::MAIN_TABLE_ALIAS); |
| 97 | |
| 98 | // Add ACLs first to avoid redundant subclauses |
| 99 | $baoName = CoreUtil::getBAOFromApiName($this->getEntity()); |
| 100 | $this->query->where($this->getAclClause(self::MAIN_TABLE_ALIAS, $baoName)); |
| 101 | } |
| 102 | |
| 103 | /** |
| 104 | * Builds main final sql statement after initialization. |
| 105 | * |
| 106 | * @return string |
| 107 | * @throws \API_Exception |
| 108 | * @throws \CRM_Core_Exception |
| 109 | */ |
| 110 | public function getSql() { |
| 111 | // Add explicit joins. Other joins implied by dot notation may be added later |
| 112 | $this->addExplicitJoins(); |
| 113 | $this->buildSelectClause(); |
| 114 | $this->buildWhereClause(); |
| 115 | $this->buildOrderBy(); |
| 116 | $this->buildLimit(); |
| 117 | $this->buildGroupBy(); |
| 118 | $this->buildHavingClause(); |
| 119 | return $this->query->toSQL(); |
| 120 | } |
| 121 | |
| 122 | /** |
| 123 | * Why walk when you can |
| 124 | * |
| 125 | * @return array |
| 126 | */ |
| 127 | public function run() { |
| 128 | $results = []; |
| 129 | $sql = $this->getSql(); |
| 130 | $this->debug('sql', $sql); |
| 131 | $query = \CRM_Core_DAO::executeQuery($sql); |
| 132 | while ($query->fetch()) { |
| 133 | $result = []; |
| 134 | foreach ($this->selectAliases as $alias => $expr) { |
| 135 | $returnName = $alias; |
| 136 | $alias = str_replace('.', '_', $alias); |
| 137 | $result[$returnName] = property_exists($query, $alias) ? $query->$alias : NULL; |
| 138 | } |
| 139 | $results[] = $result; |
| 140 | } |
| 141 | FormattingUtil::formatOutputValues($results, $this->apiFieldSpec, $this->getEntity(), 'get', $this->selectAliases); |
| 142 | return $results; |
| 143 | } |
| 144 | |
| 145 | /** |
| 146 | * @return int |
| 147 | * @throws \API_Exception |
| 148 | */ |
| 149 | public function getCount() { |
| 150 | $this->addExplicitJoins(); |
| 151 | $this->buildWhereClause(); |
| 152 | // If no having or groupBy, we only need to select count |
| 153 | if (!$this->getHaving() && !$this->getGroupBy()) { |
| 154 | $this->query->select('COUNT(*) AS `c`'); |
| 155 | $sql = $this->query->toSQL(); |
| 156 | } |
| 157 | // Use a subquery to count groups from GROUP BY or results filtered by HAVING |
| 158 | else { |
| 159 | // With no HAVING, just select the last field grouped by |
| 160 | if (!$this->getHaving()) { |
| 161 | $select = array_slice($this->getGroupBy(), -1); |
| 162 | } |
| 163 | $this->buildSelectClause($select ?? NULL); |
| 164 | $this->buildHavingClause(); |
| 165 | $this->buildGroupBy(); |
| 166 | $subquery = $this->query->toSQL(); |
| 167 | $sql = "SELECT count(*) AS `c` FROM ( $subquery ) AS rows"; |
| 168 | } |
| 169 | $this->debug('sql', $sql); |
| 170 | return (int) \CRM_Core_DAO::singleValueQuery($sql); |
| 171 | } |
| 172 | |
| 173 | /** |
| 174 | * @param array $select |
| 175 | * Array of select expressions; defaults to $this->getSelect |
| 176 | * @throws \API_Exception |
| 177 | */ |
| 178 | protected function buildSelectClause($select = NULL) { |
| 179 | // Use default if select not provided, exclude row_count which is handled elsewhere |
| 180 | $select = array_diff($select ?? $this->getSelect(), ['row_count']); |
| 181 | // An empty select is the same as * |
| 182 | if (empty($select)) { |
| 183 | $select = $this->entityFieldNames; |
| 184 | } |
| 185 | else { |
| 186 | if ($this->forceSelectId) { |
| 187 | $select = array_merge(['id'], $select); |
| 188 | } |
| 189 | |
| 190 | // Expand the superstar 'custom.*' to select all fields in all custom groups |
| 191 | $customStar = array_search('custom.*', array_values($select), TRUE); |
| 192 | if ($customStar !== FALSE) { |
| 193 | $customGroups = civicrm_api4($this->getEntity(), 'getFields', [ |
| 194 | 'checkPermissions' => FALSE, |
| 195 | 'where' => [['custom_group', 'IS NOT NULL']], |
| 196 | ], ['custom_group' => 'custom_group']); |
| 197 | $customSelect = []; |
| 198 | foreach ($customGroups as $groupName) { |
| 199 | $customSelect[] = "$groupName.*"; |
| 200 | } |
| 201 | array_splice($select, $customStar, 1, $customSelect); |
| 202 | } |
| 203 | |
| 204 | // Expand wildcards in joins (the api wrapper already expanded non-joined wildcards) |
| 205 | $wildFields = array_filter($select, function($item) { |
| 206 | return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE && strpos($item, '(') === FALSE && strpos($item, ' ') === FALSE; |
| 207 | }); |
| 208 | |
| 209 | foreach ($wildFields as $item) { |
| 210 | $pos = array_search($item, array_values($select)); |
| 211 | $this->autoJoinFK($item); |
| 212 | $matches = SelectUtil::getMatchingFields($item, array_keys($this->apiFieldSpec)); |
| 213 | array_splice($select, $pos, 1, $matches); |
| 214 | } |
| 215 | $select = array_unique($select); |
| 216 | } |
| 217 | foreach ($select as $item) { |
| 218 | $expr = SqlExpression::convert($item, TRUE); |
| 219 | $valid = TRUE; |
| 220 | foreach ($expr->getFields() as $fieldName) { |
| 221 | $field = $this->getField($fieldName); |
| 222 | // Remove expressions with unknown fields without raising an error |
| 223 | if (!$field) { |
| 224 | $select = array_diff($select, [$item]); |
| 225 | $this->debug('undefined_fields', $fieldName); |
| 226 | $valid = FALSE; |
| 227 | } |
| 228 | } |
| 229 | if ($valid) { |
| 230 | $alias = $expr->getAlias(); |
| 231 | if ($alias != $expr->getExpr() && isset($this->apiFieldSpec[$alias])) { |
| 232 | throw new \API_Exception('Cannot use existing field name as alias'); |
| 233 | } |
| 234 | $this->selectAliases[$alias] = $expr->getExpr(); |
| 235 | $this->query->select($expr->render($this->apiFieldSpec) . " AS `$alias`"); |
| 236 | } |
| 237 | } |
| 238 | } |
| 239 | |
| 240 | /** |
| 241 | * Add WHERE clause to query |
| 242 | */ |
| 243 | protected function buildWhereClause() { |
| 244 | foreach ($this->getWhere() as $clause) { |
| 245 | $sql = $this->treeWalkClauses($clause, 'WHERE'); |
| 246 | if ($sql) { |
| 247 | $this->query->where($sql); |
| 248 | } |
| 249 | } |
| 250 | } |
| 251 | |
| 252 | /** |
| 253 | * Add HAVING clause to query |
| 254 | * |
| 255 | * Every expression referenced must also be in the SELECT clause. |
| 256 | */ |
| 257 | protected function buildHavingClause() { |
| 258 | foreach ($this->getHaving() as $clause) { |
| 259 | $this->query->having($this->treeWalkClauses($clause, 'HAVING')); |
| 260 | } |
| 261 | } |
| 262 | |
| 263 | /** |
| 264 | * Add ORDER BY to query |
| 265 | */ |
| 266 | protected function buildOrderBy() { |
| 267 | foreach ($this->getOrderBy() as $item => $dir) { |
| 268 | if ($dir !== 'ASC' && $dir !== 'DESC') { |
| 269 | throw new \API_Exception("Invalid sort direction. Cannot order by $item $dir"); |
| 270 | } |
| 271 | $expr = $this->getExpression($item); |
| 272 | $column = $expr->render($this->apiFieldSpec); |
| 273 | |
| 274 | // Use FIELD() function to sort on pseudoconstant values |
| 275 | $suffix = strstr($item, ':'); |
| 276 | if ($suffix && $expr->getType() === 'SqlField') { |
| 277 | $field = $this->getField($item); |
| 278 | $options = FormattingUtil::getPseudoconstantList($field, substr($suffix, 1)); |
| 279 | if ($options) { |
| 280 | asort($options); |
| 281 | $column = "FIELD($column,'" . implode("','", array_keys($options)) . "')"; |
| 282 | } |
| 283 | } |
| 284 | $this->query->orderBy("$column $dir"); |
| 285 | } |
| 286 | } |
| 287 | |
| 288 | /** |
| 289 | * Add LIMIT to query |
| 290 | * |
| 291 | * @throws \CRM_Core_Exception |
| 292 | */ |
| 293 | protected function buildLimit() { |
| 294 | if ($this->getLimit() || $this->getOffset()) { |
| 295 | // If limit is 0, mysql will actually return 0 results. Instead set to maximum possible. |
| 296 | $this->query->limit($this->getLimit() ?: self::UNLIMITED, $this->getOffset()); |
| 297 | } |
| 298 | } |
| 299 | |
| 300 | /** |
| 301 | * Add GROUP BY clause to query |
| 302 | */ |
| 303 | protected function buildGroupBy() { |
| 304 | foreach ($this->getGroupBy() as $item) { |
| 305 | $this->query->groupBy($this->getExpression($item)->render($this->apiFieldSpec)); |
| 306 | } |
| 307 | } |
| 308 | |
| 309 | /** |
| 310 | * Recursively validate and transform a branch or leaf clause array to SQL. |
| 311 | * |
| 312 | * @param array $clause |
| 313 | * @param string $type |
| 314 | * WHERE|HAVING|ON |
| 315 | * @return string SQL where clause |
| 316 | * |
| 317 | * @throws \API_Exception |
| 318 | * @uses composeClause() to generate the SQL etc. |
| 319 | */ |
| 320 | protected function treeWalkClauses($clause, $type) { |
| 321 | // Skip empty leaf. |
| 322 | if (in_array($clause[0], ['AND', 'OR', 'NOT']) && empty($clause[1])) { |
| 323 | return ''; |
| 324 | } |
| 325 | switch ($clause[0]) { |
| 326 | case 'OR': |
| 327 | case 'AND': |
| 328 | // handle branches |
| 329 | if (count($clause[1]) === 1) { |
| 330 | // a single set so AND|OR is immaterial |
| 331 | return $this->treeWalkClauses($clause[1][0], $type); |
| 332 | } |
| 333 | else { |
| 334 | $sql_subclauses = []; |
| 335 | foreach ($clause[1] as $subclause) { |
| 336 | $sql_subclauses[] = $this->treeWalkClauses($subclause, $type); |
| 337 | } |
| 338 | return '(' . implode("\n" . $clause[0], $sql_subclauses) . ')'; |
| 339 | } |
| 340 | |
| 341 | case 'NOT': |
| 342 | // If we get a group of clauses with no operator, assume AND |
| 343 | if (!is_string($clause[1][0])) { |
| 344 | $clause[1] = ['AND', $clause[1]]; |
| 345 | } |
| 346 | return 'NOT (' . $this->treeWalkClauses($clause[1], $type) . ')'; |
| 347 | |
| 348 | default: |
| 349 | return $this->composeClause($clause, $type); |
| 350 | } |
| 351 | } |
| 352 | |
| 353 | /** |
| 354 | * Validate and transform a leaf clause array to SQL. |
| 355 | * @param array $clause [$fieldName, $operator, $criteria] |
| 356 | * @param string $type |
| 357 | * WHERE|HAVING|ON |
| 358 | * @return string SQL |
| 359 | * @throws \API_Exception |
| 360 | * @throws \Exception |
| 361 | */ |
| 362 | protected function composeClause(array $clause, string $type) { |
| 363 | // Pad array for unary operators |
| 364 | list($expr, $operator, $value) = array_pad($clause, 3, NULL); |
| 365 | if (!in_array($operator, CoreUtil::getOperators(), TRUE)) { |
| 366 | throw new \API_Exception('Illegal operator'); |
| 367 | } |
| 368 | |
| 369 | // For WHERE clause, expr must be the name of a field. |
| 370 | if ($type === 'WHERE') { |
| 371 | $field = $this->getField($expr, TRUE); |
| 372 | FormattingUtil::formatInputValue($value, $expr, $field, $operator); |
| 373 | $fieldAlias = $field['sql_name']; |
| 374 | } |
| 375 | // For HAVING, expr must be an item in the SELECT clause |
| 376 | elseif ($type === 'HAVING') { |
| 377 | // Expr references a fieldName or alias |
| 378 | if (isset($this->selectAliases[$expr])) { |
| 379 | $fieldAlias = $expr; |
| 380 | // Attempt to format if this is a real field |
| 381 | if (isset($this->apiFieldSpec[$expr])) { |
| 382 | $field = $this->getField($expr); |
| 383 | FormattingUtil::formatInputValue($value, $expr, $field, $operator); |
| 384 | } |
| 385 | } |
| 386 | // Expr references a non-field expression like a function; convert to alias |
| 387 | elseif (in_array($expr, $this->selectAliases)) { |
| 388 | $fieldAlias = array_search($expr, $this->selectAliases); |
| 389 | } |
| 390 | // If either the having or select field contains a pseudoconstant suffix, match and perform substitution |
| 391 | else { |
| 392 | list($fieldName) = explode(':', $expr); |
| 393 | foreach ($this->selectAliases as $selectAlias => $selectExpr) { |
| 394 | list($selectField) = explode(':', $selectAlias); |
| 395 | if ($selectAlias === $selectExpr && $fieldName === $selectField && isset($this->apiFieldSpec[$fieldName])) { |
| 396 | $field = $this->getField($fieldName); |
| 397 | FormattingUtil::formatInputValue($value, $expr, $field, $operator); |
| 398 | $fieldAlias = $selectAlias; |
| 399 | break; |
| 400 | } |
| 401 | } |
| 402 | } |
| 403 | if (!isset($fieldAlias)) { |
| 404 | throw new \API_Exception("Invalid expression in HAVING clause: '$expr'. Must use a value from SELECT clause."); |
| 405 | } |
| 406 | $fieldAlias = '`' . $fieldAlias . '`'; |
| 407 | } |
| 408 | elseif ($type === 'ON') { |
| 409 | $expr = $this->getExpression($expr); |
| 410 | $fieldName = count($expr->getFields()) === 1 ? $expr->getFields()[0] : NULL; |
| 411 | $fieldAlias = $expr->render($this->apiFieldSpec); |
| 412 | if (is_string($value)) { |
| 413 | $valExpr = $this->getExpression($value); |
| 414 | if ($fieldName && $valExpr->getType() === 'SqlString') { |
| 415 | FormattingUtil::formatInputValue($valExpr->expr, $fieldName, $this->apiFieldSpec[$fieldName], $operator); |
| 416 | } |
| 417 | return sprintf('%s %s %s', $fieldAlias, $operator, $valExpr->render($this->apiFieldSpec)); |
| 418 | } |
| 419 | elseif ($fieldName) { |
| 420 | $field = $this->getField($fieldName); |
| 421 | FormattingUtil::formatInputValue($value, $fieldName, $field, $operator); |
| 422 | } |
| 423 | } |
| 424 | |
| 425 | if ($operator === 'CONTAINS') { |
| 426 | switch ($field['serialize'] ?? NULL) { |
| 427 | case \CRM_Core_DAO::SERIALIZE_JSON: |
| 428 | $operator = 'LIKE'; |
| 429 | $value = '%"' . $value . '"%'; |
| 430 | // FIXME: Use this instead of the above hack once MIN_INSTALL_MYSQL_VER is bumped to 5.7. |
| 431 | // return sprintf('JSON_SEARCH(%s, "one", "%s") IS NOT NULL', $fieldAlias, \CRM_Core_DAO::escapeString($value)); |
| 432 | break; |
| 433 | |
| 434 | case \CRM_Core_DAO::SERIALIZE_SEPARATOR_BOOKEND: |
| 435 | $operator = 'LIKE'; |
| 436 | $value = '%' . \CRM_Core_DAO::VALUE_SEPARATOR . $value . \CRM_Core_DAO::VALUE_SEPARATOR . '%'; |
| 437 | break; |
| 438 | |
| 439 | default: |
| 440 | $operator = 'LIKE'; |
| 441 | $value = '%' . $value . '%'; |
| 442 | break; |
| 443 | } |
| 444 | } |
| 445 | |
| 446 | $sql_clause = \CRM_Core_DAO::createSQLFilter($fieldAlias, [$operator => $value]); |
| 447 | if ($sql_clause === NULL) { |
| 448 | throw new \API_Exception("Invalid value in $type clause for '$expr'"); |
| 449 | } |
| 450 | return $sql_clause; |
| 451 | } |
| 452 | |
| 453 | /** |
| 454 | * @param string $expr |
| 455 | * @return SqlExpression |
| 456 | * @throws \API_Exception |
| 457 | */ |
| 458 | protected function getExpression(string $expr) { |
| 459 | $sqlExpr = SqlExpression::convert($expr); |
| 460 | foreach ($sqlExpr->getFields() as $fieldName) { |
| 461 | $this->getField($fieldName, TRUE); |
| 462 | } |
| 463 | return $sqlExpr; |
| 464 | } |
| 465 | |
| 466 | /** |
| 467 | * Get acl clause for an entity |
| 468 | * |
| 469 | * @param string $tableAlias |
| 470 | * @param \CRM_Core_DAO|string $baoName |
| 471 | * @param array $stack |
| 472 | * @return array |
| 473 | */ |
| 474 | public function getAclClause($tableAlias, $baoName, $stack = []) { |
| 475 | if (!$this->getCheckPermissions()) { |
| 476 | return []; |
| 477 | } |
| 478 | // Prevent (most) redundant acl sub clauses if they have already been applied to the main entity. |
| 479 | // FIXME: Currently this only works 1 level deep, but tracking through multiple joins would increase complexity |
| 480 | // and just doing it for the first join takes care of most acl clause deduping. |
| 481 | if (count($stack) === 1 && in_array($stack[0], $this->aclFields, TRUE)) { |
| 482 | return []; |
| 483 | } |
| 484 | $clauses = $baoName::getSelectWhereClause($tableAlias); |
| 485 | if (!$stack) { |
| 486 | // Track field clauses added to the main entity |
| 487 | $this->aclFields = array_keys($clauses); |
| 488 | } |
| 489 | return array_filter($clauses); |
| 490 | } |
| 491 | |
| 492 | /** |
| 493 | * Fetch a field from the getFields list |
| 494 | * |
| 495 | * @param string $expr |
| 496 | * @param bool $strict |
| 497 | * In strict mode, this will throw an exception if the field doesn't exist |
| 498 | * |
| 499 | * @return array|null |
| 500 | * @throws \API_Exception |
| 501 | */ |
| 502 | public function getField($expr, $strict = FALSE) { |
| 503 | // If the expression contains a pseudoconstant filter like activity_type_id:label, |
| 504 | // strip it to look up the base field name, then add the field:filter key to apiFieldSpec |
| 505 | $col = strpos($expr, ':'); |
| 506 | $fieldName = $col ? substr($expr, 0, $col) : $expr; |
| 507 | // Perform join if field not yet available - this will add it to apiFieldSpec |
| 508 | if (!isset($this->apiFieldSpec[$fieldName]) && strpos($fieldName, '.')) { |
| 509 | $this->autoJoinFK($fieldName); |
| 510 | } |
| 511 | $field = $this->apiFieldSpec[$fieldName] ?? NULL; |
| 512 | if ($strict && !$field) { |
| 513 | throw new \API_Exception("Invalid field '$fieldName'"); |
| 514 | } |
| 515 | $this->apiFieldSpec[$expr] = $field; |
| 516 | return $field; |
| 517 | } |
| 518 | |
| 519 | /** |
| 520 | * Join onto other entities as specified by the api call. |
| 521 | * |
| 522 | * @throws \API_Exception |
| 523 | * @throws \Civi\API\Exception\NotImplementedException |
| 524 | */ |
| 525 | private function addExplicitJoins() { |
| 526 | foreach ($this->getJoin() as $join) { |
| 527 | // First item in the array is the entity name |
| 528 | $entity = array_shift($join); |
| 529 | // Which might contain an alias. Split on the keyword "AS" |
| 530 | list($entity, $alias) = array_pad(explode(' AS ', $entity), 2, NULL); |
| 531 | // Ensure alias is a safe string, and supply default if not given |
| 532 | $alias = $alias ? \CRM_Utils_String::munge($alias) : strtolower($entity); |
| 533 | // First item in the array is a boolean indicating if the join is required (aka INNER or LEFT). |
| 534 | // The rest are join conditions. |
| 535 | $side = array_shift($join) ? 'INNER' : 'LEFT'; |
| 536 | // Add all fields from joined entity to spec |
| 537 | $joinEntityGet = \Civi\API\Request::create($entity, 'get', ['version' => 4, 'checkPermissions' => $this->getCheckPermissions()]); |
| 538 | foreach ($joinEntityGet->entityFields() as $field) { |
| 539 | $field['sql_name'] = '`' . $alias . '`.`' . $field['column_name'] . '`'; |
| 540 | $this->addSpecField($alias . '.' . $field['name'], $field); |
| 541 | } |
| 542 | if (!empty($join[0]) && is_string($join[0]) && \CRM_Utils_Rule::alphanumeric($join[0])) { |
| 543 | $conditions = $this->getBridgeJoin($join, $entity, $alias); |
| 544 | } |
| 545 | else { |
| 546 | $conditions = $this->getJoinConditions($join, $entity, $alias); |
| 547 | } |
| 548 | foreach (array_filter($join) as $clause) { |
| 549 | $conditions[] = $this->treeWalkClauses($clause, 'ON'); |
| 550 | } |
| 551 | $tableName = CoreUtil::getTableName($entity); |
| 552 | $this->join($side, $tableName, $alias, $conditions); |
| 553 | } |
| 554 | } |
| 555 | |
| 556 | /** |
| 557 | * Supply conditions for an explicit join. |
| 558 | * |
| 559 | * @param array $joinTree |
| 560 | * @param string $joinEntity |
| 561 | * @param string $alias |
| 562 | * @return array |
| 563 | */ |
| 564 | private function getJoinConditions($joinTree, $joinEntity, $alias) { |
| 565 | $conditions = []; |
| 566 | // getAclClause() expects a stack of 1-to-1 join fields to help it dedupe, but this is more flexible, |
| 567 | // so unless this is a direct 1-to-1 join with the main entity, we'll just hack it |
| 568 | // with a padded empty stack to bypass its deduping. |
| 569 | $stack = [NULL, NULL]; |
| 570 | // If we're not explicitly referencing the joinEntity ID in the ON clause, search for a default |
| 571 | $explicitId = array_filter($joinTree, function($clause) use ($alias) { |
| 572 | list($sideA, $op, $sideB) = array_pad((array) $clause, 3, NULL); |
| 573 | return $op === '=' && ($sideA === "$alias.id" || $sideB === "$alias.id"); |
| 574 | }); |
| 575 | if (!$explicitId) { |
| 576 | foreach ($this->apiFieldSpec as $name => $field) { |
| 577 | if ($field['entity'] !== $joinEntity && $field['fk_entity'] === $joinEntity) { |
| 578 | $conditions[] = $this->treeWalkClauses([$name, '=', "$alias.id"], 'ON'); |
| 579 | } |
| 580 | elseif (strpos($name, "$alias.") === 0 && substr_count($name, '.') === 1 && $field['fk_entity'] === $this->getEntity()) { |
| 581 | $conditions[] = $this->treeWalkClauses([$name, '=', 'id'], 'ON'); |
| 582 | $stack = ['id']; |
| 583 | } |
| 584 | } |
| 585 | // Hmm, if we came up with > 1 condition, then it's ambiguous how it should be joined so we won't return anything but the generic ACLs |
| 586 | if (count($conditions) > 1) { |
| 587 | $stack = [NULL, NULL]; |
| 588 | $conditions = []; |
| 589 | } |
| 590 | } |
| 591 | $baoName = CoreUtil::getBAOFromApiName($joinEntity); |
| 592 | $acls = array_values($this->getAclClause($alias, $baoName, $stack)); |
| 593 | return array_merge($acls, $conditions); |
| 594 | } |
| 595 | |
| 596 | /** |
| 597 | * Join via a Bridge table |
| 598 | * |
| 599 | * This creates a double-join in sql that appears to the API user like a single join. |
| 600 | * |
| 601 | * @param array $joinTree |
| 602 | * @param string $joinEntity |
| 603 | * @param string $alias |
| 604 | * @return array |
| 605 | * @throws \API_Exception |
| 606 | */ |
| 607 | protected function getBridgeJoin(&$joinTree, $joinEntity, $alias) { |
| 608 | $bridgeEntity = array_shift($joinTree); |
| 609 | /* @var \Civi\Api4\Generic\DAOEntity $bridgeEntityClass */ |
| 610 | $bridgeEntityClass = '\Civi\Api4\\' . $bridgeEntity; |
| 611 | $bridgeAlias = $alias . '_via_' . strtolower($bridgeEntity); |
| 612 | $bridgeInfo = $bridgeEntityClass::getInfo(); |
| 613 | $bridgeFields = $bridgeInfo['bridge'] ?? []; |
| 614 | // Sanity check - bridge entity should declare exactly 2 FK fields |
| 615 | if (count($bridgeFields) !== 2) { |
| 616 | throw new \API_Exception("Illegal bridge entity specified: $bridgeEntity. Expected 2 bridge fields, found " . count($bridgeFields)); |
| 617 | } |
| 618 | /* @var \CRM_Core_DAO $bridgeDAO */ |
| 619 | $bridgeDAO = $bridgeInfo['dao']; |
| 620 | $bridgeTable = $bridgeDAO::getTableName(); |
| 621 | |
| 622 | $joinTable = CoreUtil::getTableName($joinEntity); |
| 623 | $bridgeEntityGet = $bridgeEntityClass::get($this->getCheckPermissions()); |
| 624 | // Get the 2 bridge reference columns as CRM_Core_Reference_* objects |
| 625 | $joinRef = $baseRef = NULL; |
| 626 | foreach ($bridgeDAO::getReferenceColumns() as $ref) { |
| 627 | if (in_array($ref->getReferenceKey(), $bridgeFields)) { |
| 628 | if (!$joinRef && in_array($joinEntity, $ref->getTargetEntities())) { |
| 629 | $joinRef = $ref; |
| 630 | } |
| 631 | else { |
| 632 | $baseRef = $ref; |
| 633 | } |
| 634 | } |
| 635 | } |
| 636 | if (!$joinRef || !$baseRef) { |
| 637 | throw new \API_Exception("Unable to join $bridgeEntity to $joinEntity"); |
| 638 | } |
| 639 | // Create link between bridge entity and join entity |
| 640 | $joinConditions = [ |
| 641 | "`$bridgeAlias`.`{$joinRef->getReferenceKey()}` = `$alias`.`{$joinRef->getTargetKey()}`", |
| 642 | ]; |
| 643 | // For dynamic references, also add the type column (e.g. `entity_table`) |
| 644 | if ($joinRef->getTypeColumn()) { |
| 645 | $joinConditions[] = "`$bridgeAlias`.`{$joinRef->getTypeColumn()}` = '$joinTable'"; |
| 646 | } |
| 647 | // Register fields (other than bridge FK fields) from the bridge entity as if they belong to the join entity |
| 648 | $fakeFields = []; |
| 649 | foreach ($bridgeEntityGet->entityFields() as $name => $field) { |
| 650 | if ($name === 'id' || $name === $joinRef->getReferenceKey() || $name === $joinRef->getTypeColumn() || $name === $baseRef->getReferenceKey() || $name === $baseRef->getTypeColumn()) { |
| 651 | continue; |
| 652 | } |
| 653 | // Note these fields get a sql alias pointing to the bridge entity, but an api alias pretending they belong to the join entity |
| 654 | $field['sql_name'] = '`' . $bridgeAlias . '`.`' . $field['column_name'] . '`'; |
| 655 | $this->addSpecField($alias . '.' . $field['name'], $field); |
| 656 | $fakeFields[] = $alias . '.' . $field['name']; |
| 657 | } |
| 658 | // Move conditions for the bridge join out of the joinTree |
| 659 | $bridgeConditions = []; |
| 660 | $isExplicit = FALSE; |
| 661 | $joinTree = array_filter($joinTree, function($clause) use ($baseRef, $alias, $bridgeAlias, $fakeFields, &$bridgeConditions, &$isExplicit) { |
| 662 | list($sideA, $op, $sideB) = array_pad((array) $clause, 3, NULL); |
| 663 | // Skip AND/OR/NOT branches |
| 664 | if (!$sideB) { |
| 665 | return TRUE; |
| 666 | } |
| 667 | // If this condition makes an explicit link between the bridge and another entity |
| 668 | if ($op === '=' && $sideB && ($sideA === "$alias.{$baseRef->getReferenceKey()}" || $sideB === "$alias.{$baseRef->getReferenceKey()}")) { |
| 669 | $expr = $sideA === "$alias.{$baseRef->getReferenceKey()}" ? $sideB : $sideA; |
| 670 | $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getReferenceKey()}` = " . $this->getExpression($expr)->render($this->apiFieldSpec); |
| 671 | $isExplicit = TRUE; |
| 672 | return FALSE; |
| 673 | } |
| 674 | // Explicit link with dynamic "entity_table" column |
| 675 | elseif ($op === '=' && $baseRef->getTypeColumn() && ($sideA === "$alias.{$baseRef->getTypeColumn()}" || $sideB === "$alias.{$baseRef->getTypeColumn()}")) { |
| 676 | $expr = $sideA === "$alias.{$baseRef->getTypeColumn()}" ? $sideB : $sideA; |
| 677 | $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getTypeColumn()}` = " . $this->getExpression($expr)->render($this->apiFieldSpec); |
| 678 | $isExplicit = TRUE; |
| 679 | return FALSE; |
| 680 | } |
| 681 | // Other conditions that apply only to the bridge table should be |
| 682 | foreach ([$sideA, $sideB] as $expr) { |
| 683 | if (is_string($expr) && in_array(explode(':', $expr)[0], $fakeFields)) { |
| 684 | $bridgeConditions[] = $this->composeClause($clause, 'ON'); |
| 685 | return FALSE; |
| 686 | } |
| 687 | } |
| 688 | return TRUE; |
| 689 | }); |
| 690 | // If no bridge conditions were specified, link it to the base entity |
| 691 | if (!$isExplicit) { |
| 692 | if (!in_array($this->getEntity(), $baseRef->getTargetEntities())) { |
| 693 | throw new \API_Exception("Unable to join $bridgeEntity to " . $this->getEntity()); |
| 694 | } |
| 695 | $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getReferenceKey()}` = a.`{$baseRef->getTargetKey()}`"; |
| 696 | if ($baseRef->getTypeColumn()) { |
| 697 | $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getTypeColumn()}` = '" . $this->getFrom() . "'"; |
| 698 | } |
| 699 | } |
| 700 | |
| 701 | $this->join('LEFT', $bridgeTable, $bridgeAlias, $bridgeConditions); |
| 702 | |
| 703 | $baoName = CoreUtil::getBAOFromApiName($joinEntity); |
| 704 | $acls = array_values($this->getAclClause($alias, $baoName, [NULL, NULL])); |
| 705 | return array_merge($acls, $joinConditions); |
| 706 | } |
| 707 | |
| 708 | /** |
| 709 | * Joins a path and adds all fields in the joined entity to apiFieldSpec |
| 710 | * |
| 711 | * @param $key |
| 712 | * @throws \API_Exception |
| 713 | * @throws \Exception |
| 714 | */ |
| 715 | protected function autoJoinFK($key) { |
| 716 | if (isset($this->apiFieldSpec[$key])) { |
| 717 | return; |
| 718 | } |
| 719 | |
| 720 | $pathArray = explode('.', $key); |
| 721 | |
| 722 | /** @var \Civi\Api4\Service\Schema\Joiner $joiner */ |
| 723 | $joiner = \Civi::container()->get('joiner'); |
| 724 | // 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. |
| 725 | array_pop($pathArray); |
| 726 | $pathString = implode('.', $pathArray); |
| 727 | |
| 728 | if (!$joiner->canAutoJoin($this->getFrom(), $pathString)) { |
| 729 | return; |
| 730 | } |
| 731 | |
| 732 | $joinPath = $joiner->join($this, $pathString); |
| 733 | |
| 734 | $lastLink = array_pop($joinPath); |
| 735 | |
| 736 | // Custom field names are already prefixed |
| 737 | $isCustom = $lastLink instanceof CustomGroupJoinable; |
| 738 | if ($isCustom) { |
| 739 | array_pop($pathArray); |
| 740 | } |
| 741 | $prefix = $pathArray ? implode('.', $pathArray) . '.' : ''; |
| 742 | // Cache field info for retrieval by $this->getField() |
| 743 | foreach ($lastLink->getEntityFields() as $fieldObject) { |
| 744 | $fieldArray = $fieldObject->toArray(); |
| 745 | $fieldArray['sql_name'] = '`' . $lastLink->getAlias() . '`.`' . $fieldArray['column_name'] . '`'; |
| 746 | $this->addSpecField($prefix . $fieldArray['name'], $fieldArray); |
| 747 | } |
| 748 | } |
| 749 | |
| 750 | /** |
| 751 | * @param string $side |
| 752 | * @param string $tableName |
| 753 | * @param string $tableAlias |
| 754 | * @param array $conditions |
| 755 | */ |
| 756 | public function join($side, $tableName, $tableAlias, $conditions) { |
| 757 | // INNER JOINs take precedence over LEFT JOINs |
| 758 | if ($side != 'LEFT' || !isset($this->joins[$tableAlias])) { |
| 759 | $this->joins[$tableAlias] = $side; |
| 760 | $this->query->join($tableAlias, "$side JOIN `$tableName` `$tableAlias` ON " . implode(' AND ', $conditions)); |
| 761 | } |
| 762 | } |
| 763 | |
| 764 | /** |
| 765 | * @return FALSE|string |
| 766 | */ |
| 767 | public function getFrom() { |
| 768 | return CoreUtil::getTableName($this->getEntity()); |
| 769 | } |
| 770 | |
| 771 | /** |
| 772 | * @return string |
| 773 | */ |
| 774 | public function getEntity() { |
| 775 | return $this->api->getEntityName(); |
| 776 | } |
| 777 | |
| 778 | /** |
| 779 | * @return array |
| 780 | */ |
| 781 | public function getSelect() { |
| 782 | return $this->api->getSelect(); |
| 783 | } |
| 784 | |
| 785 | /** |
| 786 | * @return array |
| 787 | */ |
| 788 | public function getWhere() { |
| 789 | return $this->api->getWhere(); |
| 790 | } |
| 791 | |
| 792 | /** |
| 793 | * @return array |
| 794 | */ |
| 795 | public function getHaving() { |
| 796 | return $this->api->getHaving(); |
| 797 | } |
| 798 | |
| 799 | /** |
| 800 | * @return array |
| 801 | */ |
| 802 | public function getJoin() { |
| 803 | return $this->api->getJoin(); |
| 804 | } |
| 805 | |
| 806 | /** |
| 807 | * @return array |
| 808 | */ |
| 809 | public function getGroupBy() { |
| 810 | return $this->api->getGroupBy(); |
| 811 | } |
| 812 | |
| 813 | /** |
| 814 | * @return array |
| 815 | */ |
| 816 | public function getOrderBy() { |
| 817 | return $this->api->getOrderBy(); |
| 818 | } |
| 819 | |
| 820 | /** |
| 821 | * @return mixed |
| 822 | */ |
| 823 | public function getLimit() { |
| 824 | return $this->api->getLimit(); |
| 825 | } |
| 826 | |
| 827 | /** |
| 828 | * @return mixed |
| 829 | */ |
| 830 | public function getOffset() { |
| 831 | return $this->api->getOffset(); |
| 832 | } |
| 833 | |
| 834 | /** |
| 835 | * @return \CRM_Utils_SQL_Select |
| 836 | */ |
| 837 | public function getQuery() { |
| 838 | return $this->query; |
| 839 | } |
| 840 | |
| 841 | /** |
| 842 | * @return bool|string |
| 843 | */ |
| 844 | public function getCheckPermissions() { |
| 845 | return $this->api->getCheckPermissions(); |
| 846 | } |
| 847 | |
| 848 | /** |
| 849 | * @param string $path |
| 850 | * @param array $field |
| 851 | */ |
| 852 | private function addSpecField($path, $field) { |
| 853 | // Only add field to spec if we have permission |
| 854 | if ($this->getCheckPermissions() && !empty($field['permission']) && !\CRM_Core_Permission::check($field['permission'])) { |
| 855 | $this->apiFieldSpec[$path] = FALSE; |
| 856 | return; |
| 857 | } |
| 858 | $this->apiFieldSpec[$path] = $field; |
| 859 | } |
| 860 | |
| 861 | /** |
| 862 | * Add something to the api's debug output if debugging is enabled |
| 863 | * |
| 864 | * @param $key |
| 865 | * @param $item |
| 866 | */ |
| 867 | public function debug($key, $item) { |
| 868 | if ($this->api->getDebug()) { |
| 869 | $this->api->_debugOutput[$key][] = $item; |
| 870 | } |
| 871 | } |
| 872 | |
| 873 | } |