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\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       $joinEntityFields = $joinEntityGet->entityFields();
 539       foreach ($joinEntityFields as $field) {
 540         $field['sql_name'] = '`' . $alias . '`.`' . $field['column_name'] . '`';
 541         $this->addSpecField($alias . '.' . $field['name'], $field);
 542       }
 543       if (!empty($join[0]) && is_string($join[0]) && \CRM_Utils_Rule::alphanumeric($join[0])) {
 544         $conditions = $this->getBridgeJoin($join, $entity, $alias);
 545       }
 546       else {
 547         $conditions = $this->getJoinConditions($join, $entity, $alias, $joinEntityFields);
 548       }
 549       foreach (array_filter($join) as $clause) {
 550         $conditions[] = $this->treeWalkClauses($clause, 'ON');
 551       }
 552       $tableName = CoreUtil::getTableName($entity);
 553       $this->join($side, $tableName, $alias, $conditions);
 554     }
 555   }
 556
 557   /**
 558    * Supply conditions for an explicit join.
 559    *
 560    * @param array $joinTree
 561    * @param string $joinEntity
 562    * @param string $alias
 563    * @param array $joinEntityFields
 564    * @return array
 565    */
 566   private function getJoinConditions($joinTree, $joinEntity, $alias, $joinEntityFields) {
 567     $conditions = [];
 568     // getAclClause() expects a stack of 1-to-1 join fields to help it dedupe, but this is more flexible,
 569     // so unless this is a direct 1-to-1 join with the main entity, we'll just hack it
 570     // with a padded empty stack to bypass its deduping.
 571     $stack = [NULL, NULL];
 572     // See if the ON clause already contains an FK reference to joinEntity
 573     $explicitFK = array_filter($joinTree, function($clause) use ($alias, $joinEntityFields) {
 574       list($sideA, $op, $sideB) = array_pad((array) $clause, 3, NULL);
 575       if ($op !== '=' || !$sideB) {
 576         return FALSE;
 577       }
 578       foreach ([$sideA, $sideB] as $expr) {
 579         if ($expr === "$alias.id" || !empty($joinEntityFields["$alias.$expr"]['fk_entity'])) {
 580           return TRUE;
 581         }
 582       }
 583       return FALSE;
 584     });
 585     // If we're not explicitly referencing the ID (or some other FK field) of the joinEntity, search for a default
 586     if (!$explicitFK) {
 587       foreach ($this->apiFieldSpec as $name => $field) {
 588         if ($field['entity'] !== $joinEntity && $field['fk_entity'] === $joinEntity) {
 589           $conditions[] = $this->treeWalkClauses([$name, '=', "$alias.id"], 'ON');
 590         }
 591         elseif (strpos($name, "$alias.") === 0 && substr_count($name, '.') === 1 && $field['fk_entity'] === $this->getEntity()) {
 592           $conditions[] = $this->treeWalkClauses([$name, '=', 'id'], 'ON');
 593           $stack = ['id'];
 594         }
 595       }
 596       // 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
 597       if (count($conditions) > 1) {
 598         $stack = [NULL, NULL];
 599         $conditions = [];
 600       }
 601     }
 602     $baoName = CoreUtil::getBAOFromApiName($joinEntity);
 603     $acls = array_values($this->getAclClause($alias, $baoName, $stack));
 604     return array_merge($acls, $conditions);
 605   }
 606
 607   /**
 608    * Join via a Bridge table
 609    *
 610    * This creates a double-join in sql that appears to the API user like a single join.
 611    *
 612    * @param array $joinTree
 613    * @param string $joinEntity
 614    * @param string $alias
 615    * @return array
 616    * @throws \API_Exception
 617    */
 618   protected function getBridgeJoin(&$joinTree, $joinEntity, $alias) {
 619     $bridgeEntity = array_shift($joinTree);
 620     /* @var \Civi\Api4\Generic\DAOEntity $bridgeEntityClass */
 621     $bridgeEntityClass = '\Civi\Api4\\' . $bridgeEntity;
 622     $bridgeAlias = $alias . '_via_' . strtolower($bridgeEntity);
 623     $bridgeInfo = $bridgeEntityClass::getInfo();
 624     $bridgeFields = $bridgeInfo['bridge'] ?? [];
 625     // Sanity check - bridge entity should declare exactly 2 FK fields
 626     if (count($bridgeFields) !== 2) {
 627       throw new \API_Exception("Illegal bridge entity specified: $bridgeEntity. Expected 2 bridge fields, found " . count($bridgeFields));
 628     }
 629     /* @var \CRM_Core_DAO $bridgeDAO */
 630     $bridgeDAO = $bridgeInfo['dao'];
 631     $bridgeTable = $bridgeDAO::getTableName();
 632
 633     $joinTable = CoreUtil::getTableName($joinEntity);
 634     $bridgeEntityGet = $bridgeEntityClass::get($this->getCheckPermissions());
 635     // Get the 2 bridge reference columns as CRM_Core_Reference_* objects
 636     $joinRef = $baseRef = NULL;
 637     foreach ($bridgeDAO::getReferenceColumns() as $ref) {
 638       if (in_array($ref->getReferenceKey(), $bridgeFields)) {
 639         if (!$joinRef && in_array($joinEntity, $ref->getTargetEntities())) {
 640           $joinRef = $ref;
 641         }
 642         else {
 643           $baseRef = $ref;
 644         }
 645       }
 646     }
 647     if (!$joinRef || !$baseRef) {
 648       throw new \API_Exception("Unable to join $bridgeEntity to $joinEntity");
 649     }
 650     // Create link between bridge entity and join entity
 651     $joinConditions = [
 652       "`$bridgeAlias`.`{$joinRef->getReferenceKey()}` = `$alias`.`{$joinRef->getTargetKey()}`",
 653     ];
 654     // For dynamic references, also add the type column (e.g. `entity_table`)
 655     if ($joinRef->getTypeColumn()) {
 656       $joinConditions[] = "`$bridgeAlias`.`{$joinRef->getTypeColumn()}` = '$joinTable'";
 657     }
 658     // Register fields (other than bridge FK fields) from the bridge entity as if they belong to the join entity
 659     $fakeFields = [];
 660     foreach ($bridgeEntityGet->entityFields() as $name => $field) {
 661       if ($name === 'id' || $name === $joinRef->getReferenceKey() || $name === $joinRef->getTypeColumn() || $name === $baseRef->getReferenceKey() || $name === $baseRef->getTypeColumn()) {
 662         continue;
 663       }
 664       // Note these fields get a sql alias pointing to the bridge entity, but an api alias pretending they belong to the join entity
 665       $field['sql_name'] = '`' . $bridgeAlias . '`.`' . $field['column_name'] . '`';
 666       $this->addSpecField($alias . '.' . $field['name'], $field);
 667       $fakeFields[] = $alias . '.' . $field['name'];
 668     }
 669     // Move conditions for the bridge join out of the joinTree
 670     $bridgeConditions = [];
 671     $isExplicit = FALSE;
 672     $joinTree = array_filter($joinTree, function($clause) use ($baseRef, $alias, $bridgeAlias, $fakeFields, &$bridgeConditions, &$isExplicit) {
 673       list($sideA, $op, $sideB) = array_pad((array) $clause, 3, NULL);
 674       // Skip AND/OR/NOT branches
 675       if (!$sideB) {
 676         return TRUE;
 677       }
 678       // If this condition makes an explicit link between the bridge and another entity
 679       if ($op === '=' && $sideB && ($sideA === "$alias.{$baseRef->getReferenceKey()}" || $sideB === "$alias.{$baseRef->getReferenceKey()}")) {
 680         $expr = $sideA === "$alias.{$baseRef->getReferenceKey()}" ? $sideB : $sideA;
 681         $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getReferenceKey()}` = " . $this->getExpression($expr)->render($this->apiFieldSpec);
 682         $isExplicit = TRUE;
 683         return FALSE;
 684       }
 685       // Explicit link with dynamic "entity_table" column
 686       elseif ($op === '=' && $baseRef->getTypeColumn() && ($sideA === "$alias.{$baseRef->getTypeColumn()}" || $sideB === "$alias.{$baseRef->getTypeColumn()}")) {
 687         $expr = $sideA === "$alias.{$baseRef->getTypeColumn()}" ? $sideB : $sideA;
 688         $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getTypeColumn()}` = " . $this->getExpression($expr)->render($this->apiFieldSpec);
 689         $isExplicit = TRUE;
 690         return FALSE;
 691       }
 692       // Other conditions that apply only to the bridge table should be
 693       foreach ([$sideA, $sideB] as $expr) {
 694         if (is_string($expr) && in_array(explode(':', $expr)[0], $fakeFields)) {
 695           $bridgeConditions[] = $this->composeClause($clause, 'ON');
 696           return FALSE;
 697         }
 698       }
 699       return TRUE;
 700     });
 701     // If no bridge conditions were specified, link it to the base entity
 702     if (!$isExplicit) {
 703       if (!in_array($this->getEntity(), $baseRef->getTargetEntities())) {
 704         throw new \API_Exception("Unable to join $bridgeEntity to " . $this->getEntity());
 705       }
 706       $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getReferenceKey()}` = a.`{$baseRef->getTargetKey()}`";
 707       if ($baseRef->getTypeColumn()) {
 708         $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getTypeColumn()}` = '" . $this->getFrom() . "'";
 709       }
 710     }
 711
 712     $this->join('LEFT', $bridgeTable, $bridgeAlias, $bridgeConditions);
 713
 714     $baoName = CoreUtil::getBAOFromApiName($joinEntity);
 715     $acls = array_values($this->getAclClause($alias, $baoName, [NULL, NULL]));
 716     return array_merge($acls, $joinConditions);
 717   }
 718
 719   /**
 720    * Joins a path and adds all fields in the joined entity to apiFieldSpec
 721    *
 722    * @param $key
 723    * @throws \API_Exception
 724    * @throws \Exception
 725    */
 726   protected function autoJoinFK($key) {
 727     if (isset($this->apiFieldSpec[$key])) {
 728       return;
 729     }
 730
 731     $pathArray = explode('.', $key);
 732
 733     /** @var \Civi\Api4\Service\Schema\Joiner $joiner */
 734     $joiner = \Civi::container()->get('joiner');
 735     // 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.
 736     array_pop($pathArray);
 737     $pathString = implode('.', $pathArray);
 738
 739     if (!$joiner->canAutoJoin($this->getFrom(), $pathString)) {
 740       return;
 741     }
 742
 743     $joinPath = $joiner->join($this, $pathString);
 744
 745     $lastLink = array_pop($joinPath);
 746
 747     // Custom field names are already prefixed
 748     $isCustom = $lastLink instanceof CustomGroupJoinable;
 749     if ($isCustom) {
 750       array_pop($pathArray);
 751     }
 752     $prefix = $pathArray ? implode('.', $pathArray) . '.' : '';
 753     // Cache field info for retrieval by $this->getField()
 754     foreach ($lastLink->getEntityFields() as $fieldObject) {
 755       $fieldArray = $fieldObject->toArray();
 756       $fieldArray['sql_name'] = '`' . $lastLink->getAlias() . '`.`' . $fieldArray['column_name'] . '`';
 757       $this->addSpecField($prefix . $fieldArray['name'], $fieldArray);
 758     }
 759   }
 760
 761   /**
 762    * @param string $side
 763    * @param string $tableName
 764    * @param string $tableAlias
 765    * @param array $conditions
 766    */
 767   public function join($side, $tableName, $tableAlias, $conditions) {
 768     // INNER JOINs take precedence over LEFT JOINs
 769     if ($side != 'LEFT' || !isset($this->joins[$tableAlias])) {
 770       $this->joins[$tableAlias] = $side;
 771       $this->query->join($tableAlias, "$side JOIN `$tableName` `$tableAlias` ON " . implode(' AND ', $conditions));
 772     }
 773   }
 774
 775   /**
 776    * @return FALSE|string
 777    */
 778   public function getFrom() {
 779     return CoreUtil::getTableName($this->getEntity());
 780   }
 781
 782   /**
 783    * @return string
 784    */
 785   public function getEntity() {
 786     return $this->api->getEntityName();
 787   }
 788
 789   /**
 790    * @return array
 791    */
 792   public function getSelect() {
 793     return $this->api->getSelect();
 794   }
 795
 796   /**
 797    * @return array
 798    */
 799   public function getWhere() {
 800     return $this->api->getWhere();
 801   }
 802
 803   /**
 804    * @return array
 805    */
 806   public function getHaving() {
 807     return $this->api->getHaving();
 808   }
 809
 810   /**
 811    * @return array
 812    */
 813   public function getJoin() {
 814     return $this->api->getJoin();
 815   }
 816
 817   /**
 818    * @return array
 819    */
 820   public function getGroupBy() {
 821     return $this->api->getGroupBy();
 822   }
 823
 824   /**
 825    * @return array
 826    */
 827   public function getOrderBy() {
 828     return $this->api->getOrderBy();
 829   }
 830
 831   /**
 832    * @return mixed
 833    */
 834   public function getLimit() {
 835     return $this->api->getLimit();
 836   }
 837
 838   /**
 839    * @return mixed
 840    */
 841   public function getOffset() {
 842     return $this->api->getOffset();
 843   }
 844
 845   /**
 846    * @return \CRM_Utils_SQL_Select
 847    */
 848   public function getQuery() {
 849     return $this->query;
 850   }
 851
 852   /**
 853    * @return bool|string
 854    */
 855   public function getCheckPermissions() {
 856     return $this->api->getCheckPermissions();
 857   }
 858
 859   /**
 860    * @param string $path
 861    * @param array $field
 862    */
 863   private function addSpecField($path, $field) {
 864     // Only add field to spec if we have permission
 865     if ($this->getCheckPermissions() && !empty($field['permission']) && !\CRM_Core_Permission::check($field['permission'])) {
 866       $this->apiFieldSpec[$path] = FALSE;
 867       return;
 868     }
 869     $this->apiFieldSpec[$path] = $field;
 870   }
 871
 872   /**
 873    * Add something to the api's debug output if debugging is enabled
 874    *
 875    * @param $key
 876    * @param $item
 877    */
 878   public function debug($key, $item) {
 879     if ($this->api->getDebug()) {
 880       $this->api->_debugOutput[$key][] = $item;
 881     }
 882   }
 883
 884 }