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