Civi/Api4/Query/Api4SelectQuery.php

   1 <?php
   2 /*
   3  +--------------------------------------------------------------------+
   4  | Copyright CiviCRM LLC. All rights reserved.                        |
   5  |                                                                    |
   6  | This work is published under the GNU AGPLv3 license with some      |
   7  | permitted exceptions and without any warranty. For full license    |
   8  | and copyright information, see https://civicrm.org/licensing       |
   9  +--------------------------------------------------------------------+
  10  */
  11
  12 namespace Civi\Api4\Query;
  13
  14 use Civi\API\Exception\UnauthorizedException;
  15 use Civi\Api4\Service\Schema\Joinable\CustomGroupJoinable;
  16 use Civi\Api4\Utils\FormattingUtil;
  17 use Civi\Api4\Utils\CoreUtil;
  18 use Civi\Api4\Utils\SelectUtil;
  19
  20 /**
  21  * A query `node` may be in one of three formats:
  22  *
  23  * * leaf: [$fieldName, $operator, $criteria]
  24  * * negated: ['NOT', $node]
  25  * * branch: ['OR|NOT', [$node, $node, ...]]
  26  *
  27  * Leaf operators are one of:
  28  *
  29  * * '=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=",
  30  * * 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN',
  31  * * 'IS NOT NULL', 'IS NULL', 'CONTAINS', 'IS EMPTY', 'IS NOT EMPTY',
  32  * * 'REGEXP', 'NOT REGEXP'.
  33  */
  34 class Api4SelectQuery {
  35
  36   const
  37     MAIN_TABLE_ALIAS = 'a',
  38     UNLIMITED = '18446744073709551615';
  39
  40   /**
  41    * @var \CRM_Utils_SQL_Select
  42    */
  43   protected $query;
  44
  45   /**
  46    * @var array
  47    */
  48   protected $joins = [];
  49
  50   /**
  51    * Used to keep track of implicit join table aliases
  52    * @var array
  53    */
  54   protected $joinTree = [];
  55
  56   /**
  57    * Used to create a unique table alias for each implicit join
  58    * @var int
  59    */
  60   protected $autoJoinSuffix = 0;
  61
  62   /**
  63    * @var array[]
  64    */
  65   protected $apiFieldSpec;
  66
  67   /**
  68    * @var array
  69    */
  70   protected $aclFields = [];
  71
  72   /**
  73    * @var \Civi\Api4\Generic\DAOGetAction
  74    */
  75   private $api;
  76
  77   /**
  78    * @var array
  79    * [alias => expr][]
  80    */
  81   protected $selectAliases = [];
  82
  83   /**
  84    * @var bool
  85    */
  86   public $forceSelectId = TRUE;
  87
  88   /**
  89    * @var array
  90    */
  91   private $explicitJoins = [];
  92
  93   /**
  94    * @var array
  95    */
  96   private $entityAccess = [];
  97
  98   /**
  99    * @param \Civi\Api4\Generic\DAOGetAction $apiGet
 100    */
 101   public function __construct($apiGet) {
 102     $this->api = $apiGet;
 103
 104     // Always select ID of main table unless grouping by something else
 105     $keys = CoreUtil::getInfoItem($this->getEntity(), 'primary_key');
 106     $this->forceSelectId = !$this->isAggregateQuery() || array_intersect($this->getGroupBy(), $keys);
 107
 108     // Build field lists
 109     foreach ($this->api->entityFields() as $field) {
 110       $field['sql_name'] = '`' . self::MAIN_TABLE_ALIAS . '`.`' . $field['column_name'] . '`';
 111       $this->addSpecField($field['name'], $field);
 112     }
 113
 114     $tableName = CoreUtil::getTableName($this->getEntity());
 115     $this->query = \CRM_Utils_SQL_Select::from($tableName . ' ' . self::MAIN_TABLE_ALIAS);
 116
 117     $this->entityAccess[$this->getEntity()] = TRUE;
 118
 119     // Add ACLs first to avoid redundant subclauses
 120     $baoName = CoreUtil::getBAOFromApiName($this->getEntity());
 121     $this->query->where($this->getAclClause(self::MAIN_TABLE_ALIAS, $baoName));
 122
 123     // Add explicit joins. Other joins implied by dot notation may be added later
 124     $this->addExplicitJoins();
 125   }
 126
 127   protected function isAggregateQuery() {
 128     if ($this->getGroupBy()) {
 129       return TRUE;
 130     }
 131     foreach ($this->getSelect() as $sql) {
 132       $classname = get_class(SqlExpression::convert($sql, TRUE));
 133       if (method_exists($classname, 'getCategory') && $classname::getCategory() === SqlFunction::CATEGORY_AGGREGATE) {
 134         return TRUE;
 135       }
 136     }
 137     return FALSE;
 138   }
 139
 140   /**
 141    * Builds main final sql statement after initialization.
 142    *
 143    * @return string
 144    * @throws \API_Exception
 145    * @throws \CRM_Core_Exception
 146    */
 147   public function getSql() {
 148     $this->buildSelectClause();
 149     $this->buildWhereClause();
 150     $this->buildOrderBy();
 151     $this->buildLimit();
 152     $this->buildGroupBy();
 153     $this->buildHavingClause();
 154     return $this->query->toSQL();
 155   }
 156
 157   /**
 158    * Why walk when you can
 159    *
 160    * @return array
 161    */
 162   public function run() {
 163     $results = [];
 164     $sql = $this->getSql();
 165     $this->debug('sql', $sql);
 166     $query = \CRM_Core_DAO::executeQuery($sql);
 167     while ($query->fetch()) {
 168       $result = [];
 169       foreach ($this->selectAliases as $alias => $expr) {
 170         $returnName = $alias;
 171         $alias = str_replace('.', '_', $alias);
 172         $result[$returnName] = property_exists($query, $alias) ? $query->$alias : NULL;
 173       }
 174       $results[] = $result;
 175     }
 176     FormattingUtil::formatOutputValues($results, $this->apiFieldSpec, $this->getEntity(), 'get', $this->selectAliases);
 177     return $results;
 178   }
 179
 180   /**
 181    * @return int
 182    * @throws \API_Exception
 183    */
 184   public function getCount() {
 185     $this->buildWhereClause();
 186     // If no having or groupBy, we only need to select count
 187     if (!$this->getHaving() && !$this->getGroupBy()) {
 188       $this->query->select('COUNT(*) AS `c`');
 189       $sql = $this->query->toSQL();
 190     }
 191     // Use a subquery to count groups from GROUP BY or results filtered by HAVING
 192     else {
 193       // With no HAVING, just select the last field grouped by
 194       if (!$this->getHaving()) {
 195         $select = array_slice($this->getGroupBy(), -1);
 196       }
 197       $this->buildSelectClause($select ?? NULL);
 198       $this->buildHavingClause();
 199       $this->buildGroupBy();
 200       $subquery = $this->query->toSQL();
 201       $sql = "SELECT count(*) AS `c` FROM ( $subquery ) AS `rows`";
 202     }
 203     $this->debug('sql', $sql);
 204     return (int) \CRM_Core_DAO::singleValueQuery($sql);
 205   }
 206
 207   /**
 208    * @param array $select
 209    *   Array of select expressions; defaults to $this->getSelect
 210    * @throws \API_Exception
 211    */
 212   protected function buildSelectClause($select = NULL) {
 213     // Use default if select not provided, exclude row_count which is handled elsewhere
 214     $select = array_diff($select ?? $this->getSelect(), ['row_count']);
 215     // An empty select is the same as *
 216     if (empty($select)) {
 217       $select = $this->selectMatchingFields('*');
 218     }
 219     else {
 220       if ($this->forceSelectId) {
 221         $keys = CoreUtil::getInfoItem($this->getEntity(), 'primary_key');
 222         $select = array_merge($keys, $select);
 223       }
 224
 225       // Expand the superstar 'custom.*' to select all fields in all custom groups
 226       $customStar = array_search('custom.*', array_values($select), TRUE);
 227       if ($customStar !== FALSE) {
 228         $customGroups = civicrm_api4($this->getEntity(), 'getFields', [
 229           'checkPermissions' => FALSE,
 230           'where' => [['custom_group', 'IS NOT NULL']],
 231         ], ['custom_group' => 'custom_group']);
 232         $customSelect = [];
 233         foreach ($customGroups as $groupName) {
 234           $customSelect[] = "$groupName.*";
 235         }
 236         array_splice($select, $customStar, 1, $customSelect);
 237       }
 238
 239       // Expand wildcards in joins (the api wrapper already expanded non-joined wildcards)
 240       $wildFields = array_filter($select, function($item) {
 241         return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE && strpos($item, '(') === FALSE && strpos($item, ' ') === FALSE;
 242       });
 243
 244       foreach ($wildFields as $wildField) {
 245         $pos = array_search($wildField, array_values($select));
 246         // If the joined_entity.id isn't in the fieldspec already, autoJoinFK will attempt to add the entity.
 247         $fkField = substr($wildField, 0, strrpos($wildField, '.'));
 248         $fkEntity = $this->getField($fkField)['fk_entity'] ?? NULL;
 249         $id = $fkEntity ? CoreUtil::getIdFieldName($fkEntity) : 'id';
 250         $this->autoJoinFK($fkField . ".$id");
 251         $matches = $this->selectMatchingFields($wildField);
 252         array_splice($select, $pos, 1, $matches);
 253       }
 254       $select = array_unique($select);
 255     }
 256     foreach ($select as $item) {
 257       $expr = SqlExpression::convert($item, TRUE);
 258       $valid = TRUE;
 259       foreach ($expr->getFields() as $fieldName) {
 260         $field = $this->getField($fieldName);
 261         // Remove expressions with unknown fields without raising an error
 262         if (!$field || $field['type'] === 'Filter') {
 263           $select = array_diff($select, [$item]);
 264           $valid = FALSE;
 265         }
 266       }
 267       if ($valid) {
 268         $alias = $expr->getAlias();
 269         if ($alias != $expr->getExpr() && isset($this->apiFieldSpec[$alias])) {
 270           throw new \API_Exception('Cannot use existing field name as alias');
 271         }
 272         $this->selectAliases[$alias] = $expr->getExpr();
 273         $this->query->select($expr->render($this->apiFieldSpec) . " AS `$alias`");
 274       }
 275     }
 276   }
 277
 278   /**
 279    * Get all fields for SELECT clause matching a wildcard pattern
 280    *
 281    * @param $pattern
 282    * @return array
 283    */
 284   private function selectMatchingFields($pattern) {
 285     // Only core & custom fields can be selected
 286     $availableFields = array_filter($this->apiFieldSpec, function($field) {
 287       return is_array($field) && in_array($field['type'], ['Field', 'Custom'], TRUE);
 288     });
 289     return SelectUtil::getMatchingFields($pattern, array_keys($availableFields));
 290   }
 291
 292   /**
 293    * Add WHERE clause to query
 294    */
 295   protected function buildWhereClause() {
 296     foreach ($this->getWhere() as $clause) {
 297       $sql = $this->treeWalkClauses($clause, 'WHERE');
 298       if ($sql) {
 299         $this->query->where($sql);
 300       }
 301     }
 302   }
 303
 304   /**
 305    * Add HAVING clause to query
 306    *
 307    * Every expression referenced must also be in the SELECT clause.
 308    */
 309   protected function buildHavingClause() {
 310     foreach ($this->getHaving() as $clause) {
 311       $sql = $this->treeWalkClauses($clause, 'HAVING');
 312       if ($sql) {
 313         $this->query->having($sql);
 314       }
 315     }
 316   }
 317
 318   /**
 319    * Add ORDER BY to query
 320    */
 321   protected function buildOrderBy() {
 322     foreach ($this->getOrderBy() as $item => $dir) {
 323       if ($dir !== 'ASC' && $dir !== 'DESC') {
 324         throw new \API_Exception("Invalid sort direction. Cannot order by $item $dir");
 325       }
 326
 327       try {
 328         $expr = $this->getExpression($item);
 329         $column = $expr->render($this->apiFieldSpec);
 330
 331         // Use FIELD() function to sort on pseudoconstant values
 332         $suffix = strstr($item, ':');
 333         if ($suffix && $expr->getType() === 'SqlField') {
 334           $field = $this->getField($item);
 335           $options = FormattingUtil::getPseudoconstantList($field, $item);
 336           if ($options) {
 337             asort($options);
 338             $column = "FIELD($column,'" . implode("','", array_keys($options)) . "')";
 339           }
 340         }
 341       }
 342       // If the expression could not be rendered, it might be a field alias
 343       catch (\API_Exception $e) {
 344         // Silently ignore fields the user lacks permission to see
 345         if (is_a($e, 'Civi\API\Exception\UnauthorizedException')) {
 346           $this->debug('unauthorized_fields', $item);
 347           continue;
 348         }
 349         if (!empty($this->selectAliases[$item])) {
 350           $column = '`' . $item . '`';
 351         }
 352         else {
 353           throw new \API_Exception("Invalid field '{$item}'");
 354         }
 355       }
 356
 357       $this->query->orderBy("$column $dir");
 358     }
 359   }
 360
 361   /**
 362    * Add LIMIT to query
 363    *
 364    * @throws \CRM_Core_Exception
 365    */
 366   protected function buildLimit() {
 367     if ($this->getLimit() || $this->getOffset()) {
 368       // If limit is 0, mysql will actually return 0 results. Instead set to maximum possible.
 369       $this->query->limit($this->getLimit() ?: self::UNLIMITED, $this->getOffset());
 370     }
 371   }
 372
 373   /**
 374    * Add GROUP BY clause to query
 375    */
 376   protected function buildGroupBy() {
 377     foreach ($this->getGroupBy() as $item) {
 378       $this->query->groupBy($this->getExpression($item)->render($this->apiFieldSpec));
 379     }
 380   }
 381
 382   /**
 383    * Recursively validate and transform a branch or leaf clause array to SQL.
 384    *
 385    * @param array $clause
 386    * @param string $type
 387    *   WHERE|HAVING|ON
 388    * @param int $depth
 389    * @return string SQL where clause
 390    *
 391    * @throws \API_Exception
 392    * @uses composeClause() to generate the SQL etc.
 393    */
 394   protected function treeWalkClauses($clause, $type, $depth = 0) {
 395     // Skip empty leaf.
 396     if (in_array($clause[0], ['AND', 'OR', 'NOT']) && empty($clause[1])) {
 397       return '';
 398     }
 399     switch ($clause[0]) {
 400       case 'OR':
 401       case 'AND':
 402         // handle branches
 403         if (count($clause[1]) === 1) {
 404           // a single set so AND|OR is immaterial
 405           return $this->treeWalkClauses($clause[1][0], $type, $depth + 1);
 406         }
 407         else {
 408           $sql_subclauses = [];
 409           foreach ($clause[1] as $subclause) {
 410             $sql_subclauses[] = $this->treeWalkClauses($subclause, $type, $depth + 1);
 411           }
 412           return '(' . implode("\n" . $clause[0] . ' ', $sql_subclauses) . ')';
 413         }
 414
 415       case 'NOT':
 416         // If we get a group of clauses with no operator, assume AND
 417         if (!is_string($clause[1][0])) {
 418           $clause[1] = ['AND', $clause[1]];
 419         }
 420         return 'NOT (' . $this->treeWalkClauses($clause[1], $type, $depth + 1) . ')';
 421
 422       default:
 423         try {
 424           return $this->composeClause($clause, $type, $depth);
 425         }
 426         // Silently ignore fields the user lacks permission to see
 427         catch (UnauthorizedException $e) {
 428           return '';
 429         }
 430     }
 431   }
 432
 433   /**
 434    * Validate and transform a leaf clause array to SQL.
 435    * @param array $clause [$fieldName, $operator, $criteria, $isExpression]
 436    * @param string $type
 437    *   WHERE|HAVING|ON
 438    * @param int $depth
 439    * @return string SQL
 440    * @throws \API_Exception
 441    * @throws \Exception
 442    */
 443   protected function composeClause(array $clause, string $type, int $depth) {
 444     $field = NULL;
 445     // Pad array for unary operators
 446     [$expr, $operator, $value] = array_pad($clause, 3, NULL);
 447     $isExpression = $clause[3] ?? FALSE;
 448     if (!in_array($operator, CoreUtil::getOperators(), TRUE)) {
 449       throw new \API_Exception('Illegal operator');
 450     }
 451
 452     // For WHERE clause, expr must be the name of a field.
 453     if ($type === 'WHERE' && !$isExpression) {
 454       $field = $this->getField($expr, TRUE);
 455       FormattingUtil::formatInputValue($value, $expr, $field, $operator);
 456       $fieldAlias = $this->getExpression($expr)->render($this->apiFieldSpec);
 457     }
 458     // For HAVING, expr must be an item in the SELECT clause
 459     elseif ($type === 'HAVING') {
 460       // Expr references a fieldName or alias
 461       if (isset($this->selectAliases[$expr])) {
 462         $fieldAlias = $expr;
 463         // Attempt to format if this is a real field
 464         if (isset($this->apiFieldSpec[$expr])) {
 465           $field = $this->getField($expr);
 466           FormattingUtil::formatInputValue($value, $expr, $field, $operator);
 467         }
 468       }
 469       // Expr references a non-field expression like a function; convert to alias
 470       elseif (in_array($expr, $this->selectAliases)) {
 471         $fieldAlias = array_search($expr, $this->selectAliases);
 472       }
 473       // If either the having or select field contains a pseudoconstant suffix, match and perform substitution
 474       else {
 475         list($fieldName) = explode(':', $expr);
 476         foreach ($this->selectAliases as $selectAlias => $selectExpr) {
 477           list($selectField) = explode(':', $selectAlias);
 478           if ($selectAlias === $selectExpr && $fieldName === $selectField && isset($this->apiFieldSpec[$fieldName])) {
 479             $field = $this->getField($fieldName);
 480             FormattingUtil::formatInputValue($value, $expr, $field, $operator);
 481             $fieldAlias = $selectAlias;
 482             break;
 483           }
 484         }
 485       }
 486       if (!isset($fieldAlias)) {
 487         if (in_array($expr, $this->getSelect())) {
 488           throw new UnauthorizedException("Unauthorized field '$expr'");
 489         }
 490         else {
 491           throw new \API_Exception("Invalid expression in HAVING clause: '$expr'. Must use a value from SELECT clause.");
 492         }
 493       }
 494       $fieldAlias = '`' . $fieldAlias . '`';
 495     }
 496     elseif ($type === 'ON' || ($type === 'WHERE' && $isExpression)) {
 497       $expr = $this->getExpression($expr);
 498       $fieldName = count($expr->getFields()) === 1 ? $expr->getFields()[0] : NULL;
 499       $fieldAlias = $expr->render($this->apiFieldSpec);
 500       if (is_string($value)) {
 501         $valExpr = $this->getExpression($value);
 502         if ($fieldName && $valExpr->getType() === 'SqlString') {
 503           $value = $valExpr->getExpr();
 504           FormattingUtil::formatInputValue($value, $fieldName, $this->apiFieldSpec[$fieldName], $operator);
 505           return $this->createSQLClause($fieldAlias, $operator, $value, $this->apiFieldSpec[$fieldName], $depth);
 506         }
 507         else {
 508           $value = $valExpr->render($this->apiFieldSpec);
 509           return sprintf('%s %s %s', $fieldAlias, $operator, $value);
 510         }
 511       }
 512       elseif ($fieldName) {
 513         $field = $this->getField($fieldName);
 514         FormattingUtil::formatInputValue($value, $fieldName, $field, $operator);
 515       }
 516     }
 517
 518     $sqlClause = $this->createSQLClause($fieldAlias, $operator, $value, $field, $depth);
 519     if ($sqlClause === NULL) {
 520       throw new \API_Exception("Invalid value in $type clause for '$expr'");
 521     }
 522     return $sqlClause;
 523   }
 524
 525   /**
 526    * @param string $fieldAlias
 527    * @param string $operator
 528    * @param mixed $value
 529    * @param array|null $field
 530    * @param int $depth
 531    * @return array|string|NULL
 532    * @throws \Exception
 533    */
 534   protected function createSQLClause($fieldAlias, $operator, $value, $field, int $depth) {
 535     if (!empty($field['operators']) && !in_array($operator, $field['operators'], TRUE)) {
 536       throw new \API_Exception('Illegal operator for ' . $field['name']);
 537     }
 538     // Some fields use a callback to generate their sql
 539     if (!empty($field['sql_filters'])) {
 540       $sql = [];
 541       foreach ($field['sql_filters'] as $filter) {
 542         $clause = is_callable($filter) ? $filter($field, $fieldAlias, $operator, $value, $this, $depth) : NULL;
 543         if ($clause) {
 544           $sql[] = $clause;
 545         }
 546       }
 547       return $sql ? implode(' AND ', $sql) : NULL;
 548     }
 549     if ($operator === 'CONTAINS') {
 550       switch ($field['serialize'] ?? NULL) {
 551         case \CRM_Core_DAO::SERIALIZE_JSON:
 552           $operator = 'LIKE';
 553           $value = '%"' . $value . '"%';
 554           // FIXME: Use this instead of the above hack once MIN_INSTALL_MYSQL_VER is bumped to 5.7.
 555           // return sprintf('JSON_SEARCH(%s, "one", "%s") IS NOT NULL', $fieldAlias, \CRM_Core_DAO::escapeString($value));
 556           break;
 557
 558         case \CRM_Core_DAO::SERIALIZE_SEPARATOR_BOOKEND:
 559           $operator = 'LIKE';
 560           $value = '%' . \CRM_Core_DAO::VALUE_SEPARATOR . $value . \CRM_Core_DAO::VALUE_SEPARATOR . '%';
 561           break;
 562
 563         default:
 564           $operator = 'LIKE';
 565           $value = '%' . $value . '%';
 566           break;
 567       }
 568     }
 569
 570     if ($operator === 'IS EMPTY' || $operator === 'IS NOT EMPTY') {
 571       // If field is not a string or number, this will pass through and use IS NULL/IS NOT NULL
 572       $operator = str_replace('EMPTY', 'NULL', $operator);
 573       // For strings & numbers, create an OR grouping of empty value OR null
 574       if (in_array($field['data_type'] ?? NULL, ['String', 'Integer', 'Float'], TRUE)) {
 575         $emptyVal = $field['data_type'] === 'String' ? '""' : '0';
 576         $isEmptyClause = $operator === 'IS NULL' ? "= $emptyVal OR" : "<> $emptyVal AND";
 577         return "($fieldAlias $isEmptyClause $fieldAlias $operator)";
 578       }
 579     }
 580
 581     if ($operator == 'REGEXP' || $operator == 'NOT REGEXP') {
 582       return sprintf('%s %s "%s"', $fieldAlias, $operator, \CRM_Core_DAO::escapeString($value));
 583     }
 584
 585     if (is_bool($value)) {
 586       $value = (int) $value;
 587     }
 588
 589     return \CRM_Core_DAO::createSQLFilter($fieldAlias, [$operator => $value]);
 590   }
 591
 592   /**
 593    * @param string $expr
 594    * @return SqlExpression
 595    * @throws \API_Exception
 596    */
 597   protected function getExpression(string $expr) {
 598     $sqlExpr = SqlExpression::convert($expr);
 599     foreach ($sqlExpr->getFields() as $fieldName) {
 600       $this->getField($fieldName, TRUE);
 601     }
 602     return $sqlExpr;
 603   }
 604
 605   /**
 606    * Get acl clause for an entity
 607    *
 608    * @param string $tableAlias
 609    * @param \CRM_Core_DAO|string $baoName
 610    * @param array $stack
 611    * @return array
 612    */
 613   public function getAclClause($tableAlias, $baoName, $stack = []) {
 614     if (!$this->getCheckPermissions()) {
 615       return [];
 616     }
 617     // Prevent (most) redundant acl sub clauses if they have already been applied to the main entity.
 618     // FIXME: Currently this only works 1 level deep, but tracking through multiple joins would increase complexity
 619     // and just doing it for the first join takes care of most acl clause deduping.
 620     if (count($stack) === 1 && in_array(reset($stack), $this->aclFields, TRUE)) {
 621       return [];
 622     }
 623     $clauses = $baoName::getSelectWhereClause($tableAlias);
 624     if (!$stack) {
 625       // Track field clauses added to the main entity
 626       $this->aclFields = array_keys($clauses);
 627     }
 628     return array_filter($clauses);
 629   }
 630
 631   /**
 632    * Fetch a field from the getFields list
 633    *
 634    * @param string $expr
 635    * @param bool $strict
 636    *   In strict mode, this will throw an exception if the field doesn't exist
 637    *
 638    * @return array|null
 639    * @throws \API_Exception
 640    */
 641   public function getField($expr, $strict = FALSE) {
 642     // If the expression contains a pseudoconstant filter like activity_type_id:label,
 643     // strip it to look up the base field name, then add the field:filter key to apiFieldSpec
 644     $col = strpos($expr, ':');
 645     $fieldName = $col ? substr($expr, 0, $col) : $expr;
 646     // Perform join if field not yet available - this will add it to apiFieldSpec
 647     if (!isset($this->apiFieldSpec[$fieldName]) && strpos($fieldName, '.')) {
 648       $this->autoJoinFK($fieldName);
 649     }
 650     $field = $this->apiFieldSpec[$fieldName] ?? NULL;
 651     if (!$field) {
 652       $this->debug($field === FALSE ? 'unauthorized_fields' : 'undefined_fields', $fieldName);
 653     }
 654     if ($strict && $field === NULL) {
 655       throw new \API_Exception("Invalid field '$fieldName'");
 656     }
 657     if ($strict && $field === FALSE) {
 658       throw new UnauthorizedException("Unauthorized field '$fieldName'");
 659     }
 660     if ($field) {
 661       $this->apiFieldSpec[$expr] = $field;
 662     }
 663     return $field;
 664   }
 665
 666   /**
 667    * Check the "gatekeeper" permissions for performing "get" on a given entity.
 668    *
 669    * @param $entity
 670    * @return bool
 671    */
 672   public function checkEntityAccess($entity) {
 673     if (!$this->getCheckPermissions()) {
 674       return TRUE;
 675     }
 676     if (!isset($this->entityAccess[$entity])) {
 677       $this->entityAccess[$entity] = (bool) civicrm_api4($entity, 'getActions', [
 678         'where' => [['name', '=', 'get']],
 679         'select' => ['name'],
 680       ])->first();
 681     }
 682     return $this->entityAccess[$entity];
 683   }
 684
 685   /**
 686    * Join onto other entities as specified by the api call.
 687    *
 688    * @throws \API_Exception
 689    * @throws \Civi\API\Exception\NotImplementedException
 690    */
 691   private function addExplicitJoins() {
 692     foreach ($this->getJoin() as $join) {
 693       // First item in the array is the entity name
 694       $entity = array_shift($join);
 695       // Which might contain an alias. Split on the keyword "AS"
 696       list($entity, $alias) = array_pad(explode(' AS ', $entity), 2, NULL);
 697       // Ensure permissions
 698       if (!$this->checkEntityAccess($entity)) {
 699         continue;
 700       }
 701       // Ensure alias is a safe string, and supply default if not given
 702       $alias = $alias ?: strtolower($entity);
 703       if ($alias === self::MAIN_TABLE_ALIAS || !preg_match('/^[-\w]{1,256}$/', $alias)) {
 704         throw new \API_Exception('Illegal join alias: "' . $alias . '"');
 705       }
 706       // First item in the array is a boolean indicating if the join is required (aka INNER or LEFT).
 707       // The rest are join conditions.
 708       $side = array_shift($join);
 709       // If omitted, supply default (LEFT); and legacy support for boolean values
 710       if (!is_string($side)) {
 711         $side = $side ? 'INNER' : 'LEFT';
 712       }
 713       if (!in_array($side, ['INNER', 'LEFT', 'EXCLUDE'])) {
 714         throw new \API_Exception("Illegal value for join side: '$side'.");
 715       }
 716       if ($side === 'EXCLUDE') {
 717         $side = 'LEFT';
 718         $this->api->addWhere("$alias.id", 'IS NULL');
 719       }
 720       // Add all fields from joined entity to spec
 721       $joinEntityGet = \Civi\API\Request::create($entity, 'get', ['version' => 4, 'checkPermissions' => $this->getCheckPermissions()]);
 722       $joinEntityFields = $joinEntityGet->entityFields();
 723       foreach ($joinEntityFields as $field) {
 724         $field['sql_name'] = '`' . $alias . '`.`' . $field['column_name'] . '`';
 725         $this->addSpecField($alias . '.' . $field['name'], $field);
 726       }
 727       $tableName = CoreUtil::getTableName($entity);
 728       // Save join info to be retrieved by $this->getExplicitJoin()
 729       $this->explicitJoins[$alias] = [
 730         'entity' => $entity,
 731         'table' => $tableName,
 732         'bridge' => NULL,
 733       ];
 734       // If the first condition is a string, it's the name of a bridge entity
 735       if (!empty($join[0]) && is_string($join[0]) && \CRM_Utils_Rule::alphanumeric($join[0])) {
 736         $this->addBridgeJoin($join, $entity, $alias, $side);
 737       }
 738       else {
 739         $conditions = $this->getJoinConditions($join, $entity, $alias, $joinEntityFields);
 740         foreach (array_filter($join) as $clause) {
 741           $conditions[] = $this->treeWalkClauses($clause, 'ON');
 742         }
 743         $this->join($side, $tableName, $alias, $conditions);
 744       }
 745     }
 746   }
 747
 748   /**
 749    * Supply conditions for an explicit join.
 750    *
 751    * @param array $joinTree
 752    * @param string $joinEntity
 753    * @param string $alias
 754    * @param array $joinEntityFields
 755    * @return array
 756    */
 757   private function getJoinConditions($joinTree, $joinEntity, $alias, $joinEntityFields) {
 758     $conditions = [];
 759     // getAclClause() expects a stack of 1-to-1 join fields to help it dedupe, but this is more flexible,
 760     // so unless this is a direct 1-to-1 join with the main entity, we'll just hack it
 761     // with a padded empty stack to bypass its deduping.
 762     $stack = [NULL, NULL];
 763     // See if the ON clause already contains an FK reference to joinEntity
 764     $explicitFK = array_filter($joinTree, function($clause) use ($alias, $joinEntityFields) {
 765       list($sideA, $op, $sideB) = array_pad((array) $clause, 3, NULL);
 766       if ($op !== '=' || !$sideB) {
 767         return FALSE;
 768       }
 769       foreach ([$sideA, $sideB] as $expr) {
 770         if ($expr === "$alias.id" || !empty($joinEntityFields[str_replace("$alias.", '', $expr)]['fk_entity'])) {
 771           return TRUE;
 772         }
 773       }
 774       return FALSE;
 775     });
 776     // If we're not explicitly referencing the ID (or some other FK field) of the joinEntity, search for a default
 777     if (!$explicitFK) {
 778       foreach ($this->apiFieldSpec as $name => $field) {
 779         if (is_array($field) && $field['entity'] !== $joinEntity && $field['fk_entity'] === $joinEntity) {
 780           $conditions[] = $this->treeWalkClauses([$name, '=', "$alias.id"], 'ON');
 781         }
 782         elseif (strpos($name, "$alias.") === 0 && substr_count($name, '.') === 1 && $field['fk_entity'] === $this->getEntity()) {
 783           $conditions[] = $this->treeWalkClauses([$name, '=', 'id'], 'ON');
 784           $stack = ['id'];
 785         }
 786       }
 787       // 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
 788       if (count($conditions) > 1) {
 789         $stack = [NULL, NULL];
 790         $conditions = [];
 791       }
 792     }
 793     $baoName = CoreUtil::getBAOFromApiName($joinEntity);
 794     $acls = array_values($this->getAclClause($alias, $baoName, $stack));
 795     return array_merge($acls, $conditions);
 796   }
 797
 798   /**
 799    * Join via a Bridge table
 800    *
 801    * This creates a double-join in sql that appears to the API user like a single join.
 802    *
 803    * LEFT joins use a subquery so that the bridge + joined-entity can be treated like a single table.
 804    *
 805    * @param array $joinTree
 806    * @param string $joinEntity
 807    * @param string $alias
 808    * @param string $side
 809    * @throws \API_Exception
 810    */
 811   protected function addBridgeJoin($joinTree, $joinEntity, $alias, $side) {
 812     $bridgeEntity = array_shift($joinTree);
 813     $this->explicitJoins[$alias]['bridge'] = $bridgeEntity;
 814
 815     // INNER joins require unique aliases, whereas left joins will be inside a subquery and short aliases are more readable
 816     $bridgeAlias = $side === 'INNER' ? $alias . '_via_' . strtolower($bridgeEntity) : 'b';
 817     $joinAlias = $side === 'INNER' ? $alias : 'c';
 818
 819     $joinTable = CoreUtil::getTableName($joinEntity);
 820     [$bridgeTable, $baseRef, $joinRef] = $this->getBridgeRefs($bridgeEntity, $joinEntity);
 821
 822     $bridgeFields = $this->registerBridgeJoinFields($bridgeEntity, $joinRef, $baseRef, $alias, $bridgeAlias, $side);
 823
 824     $linkConditions = $this->getBridgeLinkConditions($bridgeAlias, $joinAlias, $joinTable, $joinRef);
 825
 826     $bridgeConditions = $this->getBridgeJoinConditions($joinTree, $baseRef, $alias, $bridgeAlias, $bridgeEntity, $side);
 827
 828     $acls = array_values($this->getAclClause($joinAlias, CoreUtil::getBAOFromApiName($joinEntity), [NULL, NULL]));
 829
 830     $joinConditions = [];
 831     foreach (array_filter($joinTree) as $clause) {
 832       $joinConditions[] = $this->treeWalkClauses($clause, 'ON');
 833     }
 834
 835     // INNER joins are done with 2 joins
 836     if ($side === 'INNER') {
 837       // Info needed for joining custom fields extending the bridge entity
 838       $this->explicitJoins[$alias]['bridge_table_alias'] = $bridgeAlias;
 839       $this->explicitJoins[$alias]['bridge_id_alias'] = 'id';
 840       $this->join('INNER', $bridgeTable, $bridgeAlias, $bridgeConditions);
 841       $this->join('INNER', $joinTable, $alias, array_merge($linkConditions, $acls, $joinConditions));
 842     }
 843     // For LEFT joins, construct a subquery to link the bridge & join tables as one
 844     else {
 845       $joinEntityClass = CoreUtil::getApiClass($joinEntity);
 846       foreach ($joinEntityClass::get($this->getCheckPermissions())->entityFields() as $name => $field) {
 847         if ($field['type'] === 'Field') {
 848           $bridgeFields[$field['column_name']] = '`' . $joinAlias . '`.`' . $field['column_name'] . '`';
 849         }
 850       }
 851       // Info needed for joining custom fields extending the bridge entity
 852       $this->explicitJoins[$alias]['bridge_table_alias'] = $alias;
 853       $this->explicitJoins[$alias]['bridge_id_alias'] = 'bridge_entity_id_key';
 854       $bridgeFields[] = "`$bridgeAlias`.`id` AS `bridge_entity_id_key`";
 855       $select = implode(',', $bridgeFields);
 856       $joinConditions = array_merge($joinConditions, $bridgeConditions);
 857       $innerConditions = array_merge($linkConditions, $acls);
 858       $subquery = "SELECT $select FROM `$bridgeTable` `$bridgeAlias`, `$joinTable` `$joinAlias` WHERE " . implode(' AND ', $innerConditions);
 859       $this->query->join($alias, "$side JOIN ($subquery) `$alias` ON " . implode(' AND ', $joinConditions));
 860     }
 861   }
 862
 863   /**
 864    * Get the table name and 2 reference columns from a bridge entity
 865    *
 866    * @param string $bridgeEntity
 867    * @param string $joinEntity
 868    * @return array
 869    * @throws \API_Exception
 870    */
 871   private function getBridgeRefs(string $bridgeEntity, string $joinEntity): array {
 872     $bridgeFields = CoreUtil::getInfoItem($bridgeEntity, 'bridge') ?? [];
 873     // Sanity check - bridge entity should declare exactly 2 FK fields
 874     if (count($bridgeFields) !== 2) {
 875       throw new \API_Exception("Illegal bridge entity specified: $bridgeEntity. Expected 2 bridge fields, found " . count($bridgeFields));
 876     }
 877     /* @var \CRM_Core_DAO $bridgeDAO */
 878     $bridgeDAO = CoreUtil::getInfoItem($bridgeEntity, 'dao');
 879     $bridgeTable = $bridgeDAO::getTableName();
 880
 881     // Get the 2 bridge reference columns as CRM_Core_Reference_* objects
 882     $joinRef = $baseRef = NULL;
 883     foreach ($bridgeDAO::getReferenceColumns() as $ref) {
 884       if (array_key_exists($ref->getReferenceKey(), $bridgeFields)) {
 885         if (!$joinRef && in_array($joinEntity, $ref->getTargetEntities())) {
 886           $joinRef = $ref;
 887         }
 888         else {
 889           $baseRef = $ref;
 890         }
 891       }
 892     }
 893     if (!$joinRef || !$baseRef) {
 894       throw new \API_Exception("Unable to join $bridgeEntity to $joinEntity");
 895     }
 896     return [$bridgeTable, $baseRef, $joinRef];
 897   }
 898
 899   /**
 900    * Get the clause to link bridge entity with join entity
 901    *
 902    * @param string $bridgeAlias
 903    * @param string $joinAlias
 904    * @param string $joinTable
 905    * @param $joinRef
 906    * @return array
 907    */
 908   private function getBridgeLinkConditions(string $bridgeAlias, string $joinAlias, string $joinTable, $joinRef): array {
 909     $linkConditions = [
 910       "`$bridgeAlias`.`{$joinRef->getReferenceKey()}` = `$joinAlias`.`{$joinRef->getTargetKey()}`",
 911     ];
 912     // For dynamic references, also add the type column (e.g. `entity_table`)
 913     if ($joinRef->getTypeColumn()) {
 914       $linkConditions[] = "`$bridgeAlias`.`{$joinRef->getTypeColumn()}` = '$joinTable'";
 915     }
 916     return $linkConditions;
 917   }
 918
 919   /**
 920    * Register fields (other than bridge FK fields) from the bridge entity as if they belong to the join entity
 921    *
 922    * @param $bridgeEntity
 923    * @param $joinRef
 924    * @param $baseRef
 925    * @param string $alias
 926    * @param string $bridgeAlias
 927    * @param string $side
 928    * @return array
 929    */
 930   private function registerBridgeJoinFields($bridgeEntity, $joinRef, $baseRef, string $alias, string $bridgeAlias, string $side): array {
 931     $fakeFields = [];
 932     $bridgeFkFields = [$joinRef->getReferenceKey(), $joinRef->getTypeColumn(), $baseRef->getReferenceKey(), $baseRef->getTypeColumn()];
 933     $bridgeEntityClass = CoreUtil::getApiClass($bridgeEntity);
 934     foreach ($bridgeEntityClass::get($this->getCheckPermissions())->entityFields() as $name => $field) {
 935       if ($name === 'id' || ($side === 'INNER' && in_array($name, $bridgeFkFields, TRUE))) {
 936         continue;
 937       }
 938       // For INNER joins, these fields get a sql alias pointing to the bridge entity,
 939       // but an api alias pretending they belong to the join entity.
 940       $field['sql_name'] = '`' . ($side === 'LEFT' ? $alias : $bridgeAlias) . '`.`' . $field['column_name'] . '`';
 941       $this->addSpecField($alias . '.' . $name, $field);
 942       if ($field['type'] === 'Field') {
 943         $fakeFields[$field['column_name']] = '`' . $bridgeAlias . '`.`' . $field['column_name'] . '`';
 944       }
 945     }
 946     return $fakeFields;
 947   }
 948
 949   /**
 950    * Extract bridge join conditions from the joinTree if any, else supply default conditions for join to base entity
 951    *
 952    * @param array $joinTree
 953    * @param $baseRef
 954    * @param string $alias
 955    * @param string $bridgeAlias
 956    * @param string $bridgeEntity
 957    * @param string $side
 958    * @return string[]
 959    * @throws \API_Exception
 960    */
 961   private function getBridgeJoinConditions(array &$joinTree, $baseRef, string $alias, string $bridgeAlias, string $bridgeEntity, string $side): array {
 962     $bridgeConditions = [];
 963     $bridgeAlias = $side === 'INNER' ? $bridgeAlias : $alias;
 964     // Find explicit bridge join conditions and move them out of the joinTree
 965     $joinTree = array_filter($joinTree, function ($clause) use ($baseRef, $alias, $bridgeAlias, &$bridgeConditions) {
 966       list($sideA, $op, $sideB) = array_pad((array) $clause, 3, NULL);
 967       // Skip AND/OR/NOT branches
 968       if (!$sideB) {
 969         return TRUE;
 970       }
 971       // If this condition makes an explicit link between the bridge and another entity
 972       if ($op === '=' && $sideB && ($sideA === "$alias.{$baseRef->getReferenceKey()}" || $sideB === "$alias.{$baseRef->getReferenceKey()}")) {
 973         $expr = $sideA === "$alias.{$baseRef->getReferenceKey()}" ? $sideB : $sideA;
 974         $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getReferenceKey()}` = " . $this->getExpression($expr)->render($this->apiFieldSpec);
 975         return FALSE;
 976       }
 977       // Explicit link with dynamic "entity_table" column
 978       elseif ($op === '=' && $baseRef->getTypeColumn() && ($sideA === "$alias.{$baseRef->getTypeColumn()}" || $sideB === "$alias.{$baseRef->getTypeColumn()}")) {
 979         $expr = $sideA === "$alias.{$baseRef->getTypeColumn()}" ? $sideB : $sideA;
 980         $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getTypeColumn()}` = " . $this->getExpression($expr)->render($this->apiFieldSpec);
 981         return FALSE;
 982       }
 983       return TRUE;
 984     });
 985     // If no bridge conditions were specified, link it to the base entity
 986     if (!$bridgeConditions) {
 987       if (!in_array($this->getEntity(), $baseRef->getTargetEntities())) {
 988         throw new \API_Exception("Unable to join $bridgeEntity to " . $this->getEntity());
 989       }
 990       $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getReferenceKey()}` = a.`{$baseRef->getTargetKey()}`";
 991       if ($baseRef->getTypeColumn()) {
 992         $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getTypeColumn()}` = '" . $this->getFrom() . "'";
 993       }
 994     }
 995     return $bridgeConditions;
 996   }
 997
 998   /**
 999    * Joins a path and adds all fields in the joined entity to apiFieldSpec
1000    *
1001    * @param $key
1002    */
1003   protected function autoJoinFK($key) {
1004     if (isset($this->apiFieldSpec[$key])) {
1005       return;
1006     }
1007     /** @var \Civi\Api4\Service\Schema\Joiner $joiner */
1008     $joiner = \Civi::container()->get('joiner');
1009
1010     $pathArray = explode('.', $key);
1011     // 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.
1012     array_pop($pathArray);
1013
1014     $baseTableAlias = $this::MAIN_TABLE_ALIAS;
1015
1016     // If the first item is the name of an explicit join, use it as the base & shift it off the path
1017     $explicitJoin = $this->getExplicitJoin($pathArray[0]);
1018     if ($explicitJoin) {
1019       $baseTableAlias = array_shift($pathArray);
1020     }
1021
1022     // Ensure joinTree array contains base table
1023     $this->joinTree[$baseTableAlias]['#table_alias'] = $baseTableAlias;
1024     $this->joinTree[$baseTableAlias]['#path'] = $explicitJoin ? $baseTableAlias . '.' : '';
1025     // During iteration this variable will refer to the current position in the tree
1026     $joinTreeNode =& $this->joinTree[$baseTableAlias];
1027
1028     $useBridgeTable = FALSE;
1029     try {
1030       $joinPath = $joiner->getPath($explicitJoin['table'] ?? $this->getFrom(), $pathArray);
1031     }
1032     catch (\API_Exception $e) {
1033       if (!empty($explicitJoin['bridge'])) {
1034         // Try looking up custom field in bridge entity instead
1035         try {
1036           $useBridgeTable = TRUE;
1037           $joinPath = $joiner->getPath(CoreUtil::getTableName($explicitJoin['bridge']), $pathArray);
1038         }
1039         catch (\API_Exception $e) {
1040           return;
1041         }
1042       }
1043       else {
1044         // Because the select clause silently ignores unknown fields, this function shouldn't throw exceptions
1045         return;
1046       }
1047     }
1048
1049     foreach ($joinPath as $joinName => $link) {
1050       if (!isset($joinTreeNode[$joinName])) {
1051         $target = $link->getTargetTable();
1052         $tableAlias = $link->getAlias() . '_' . ++$this->autoJoinSuffix;
1053         $isCustom = $link instanceof CustomGroupJoinable;
1054
1055         $joinTreeNode[$joinName] = [
1056           '#table_alias' => $tableAlias,
1057           '#path' => $joinTreeNode['#path'] . $joinName . '.',
1058         ];
1059         $joinEntity = CoreUtil::getApiNameFromTableName($target);
1060
1061         if ($joinEntity && !$this->checkEntityAccess($joinEntity)) {
1062           return;
1063         }
1064         if ($this->getCheckPermissions() && $isCustom) {
1065           // Check access to custom group
1066           $groupId = \CRM_Core_DAO::getFieldValue('CRM_Core_DAO_CustomGroup', $link->getTargetTable(), 'id', 'table_name');
1067           if (!\CRM_Core_BAO_CustomGroup::checkGroupAccess($groupId, \CRM_Core_Permission::VIEW)) {
1068             return;
1069           }
1070         }
1071         if ($link->isDeprecated()) {
1072           $deprecatedAlias = $link->getAlias();
1073           \CRM_Core_Error::deprecatedWarning("Deprecated join alias '$deprecatedAlias' used in APIv4 get. Should be changed to '{$deprecatedAlias}_id'");
1074         }
1075         $virtualField = $link->getSerialize();
1076         $baseTableAlias = $joinTreeNode['#table_alias'];
1077         if ($useBridgeTable) {
1078           // When joining custom fields that directly extend the bridge entity
1079           $baseTableAlias = $explicitJoin['bridge_table_alias'];
1080           if ($link->getBaseColumn() === 'id') {
1081             $link->setBaseColumn($explicitJoin['bridge_id_alias']);
1082           }
1083         }
1084
1085         // Cache field info for retrieval by $this->getField()
1086         foreach ($link->getEntityFields() as $fieldObject) {
1087           $fieldArray = $fieldObject->toArray();
1088           // Set sql name of field, using column name for real joins
1089           if (!$virtualField) {
1090             $fieldArray['sql_name'] = '`' . $tableAlias . '`.`' . $fieldArray['column_name'] . '`';
1091           }
1092           // For virtual joins on serialized fields, the callback function will need the sql name of the serialized field
1093           // @see self::renderSerializedJoin()
1094           else {
1095             $fieldArray['sql_name'] = '`' . $baseTableAlias . '`.`' . $link->getBaseColumn() . '`';
1096           }
1097           // Custom fields will already have the group name prefixed
1098           $fieldName = $isCustom ? explode('.', $fieldArray['name'])[1] : $fieldArray['name'];
1099           $this->addSpecField($joinTreeNode[$joinName]['#path'] . $fieldName, $fieldArray);
1100         }
1101
1102         // Serialized joins are rendered by this::renderSerializedJoin. Don't add their tables.
1103         if (!$virtualField) {
1104           $bao = $joinEntity ? CoreUtil::getBAOFromApiName($joinEntity) : NULL;
1105           $conditions = $link->getConditionsForJoin($baseTableAlias, $tableAlias);
1106           if ($bao) {
1107             $conditions = array_merge($conditions, $this->getAclClause($tableAlias, $bao, $joinPath));
1108           }
1109           $this->join('LEFT', $target, $tableAlias, $conditions);
1110         }
1111
1112       }
1113       $joinTreeNode =& $joinTreeNode[$joinName];
1114       $useBridgeTable = FALSE;
1115     }
1116   }
1117
1118   /**
1119    * @param string $side
1120    * @param string $tableName
1121    * @param string $tableAlias
1122    * @param array $conditions
1123    */
1124   public function join($side, $tableName, $tableAlias, $conditions) {
1125     // INNER JOINs take precedence over LEFT JOINs
1126     if ($side != 'LEFT' || !isset($this->joins[$tableAlias])) {
1127       $this->joins[$tableAlias] = $side;
1128       $this->query->join($tableAlias, "$side JOIN `$tableName` `$tableAlias` ON " . implode(' AND ', $conditions));
1129     }
1130   }
1131
1132   /**
1133    * Performs a virtual join with a serialized field using FIND_IN_SET
1134    *
1135    * @param array $field
1136    * @return string
1137    */
1138   public static function renderSerializedJoin(array $field): string {
1139     $sep = \CRM_Core_DAO::VALUE_SEPARATOR;
1140     $id = CoreUtil::getIdFieldName($field['entity']);
1141     $searchFn = "FIND_IN_SET(`{$field['table_name']}`.`$id`, REPLACE({$field['sql_name']}, '$sep', ','))";
1142     return "(
1143       SELECT GROUP_CONCAT(
1144         `{$field['column_name']}`
1145         ORDER BY $searchFn
1146         SEPARATOR '$sep'
1147       )
1148       FROM `{$field['table_name']}`
1149       WHERE $searchFn
1150     )";
1151   }
1152
1153   /**
1154    * @return FALSE|string
1155    */
1156   public function getFrom() {
1157     return CoreUtil::getTableName($this->getEntity());
1158   }
1159
1160   /**
1161    * @return string
1162    */
1163   public function getEntity() {
1164     return $this->api->getEntityName();
1165   }
1166
1167   /**
1168    * @return array
1169    */
1170   public function getSelect() {
1171     return $this->api->getSelect();
1172   }
1173
1174   /**
1175    * @return array
1176    */
1177   public function getWhere() {
1178     return $this->api->getWhere();
1179   }
1180
1181   /**
1182    * @return array
1183    */
1184   public function getHaving() {
1185     return $this->api->getHaving();
1186   }
1187
1188   /**
1189    * @return array
1190    */
1191   public function getJoin() {
1192     return $this->api->getJoin();
1193   }
1194
1195   /**
1196    * @return array
1197    */
1198   public function getGroupBy() {
1199     return $this->api->getGroupBy();
1200   }
1201
1202   /**
1203    * @return array
1204    */
1205   public function getOrderBy() {
1206     return $this->api->getOrderBy();
1207   }
1208
1209   /**
1210    * @return mixed
1211    */
1212   public function getLimit() {
1213     return $this->api->getLimit();
1214   }
1215
1216   /**
1217    * @return mixed
1218    */
1219   public function getOffset() {
1220     return $this->api->getOffset();
1221   }
1222
1223   /**
1224    * @return \CRM_Utils_SQL_Select
1225    */
1226   public function getQuery() {
1227     return $this->query;
1228   }
1229
1230   /**
1231    * @return bool|string
1232    */
1233   public function getCheckPermissions() {
1234     return $this->api->getCheckPermissions();
1235   }
1236
1237   /**
1238    * @param string $alias
1239    * @return array|NULL
1240    */
1241   public function getExplicitJoin($alias) {
1242     return $this->explicitJoins[$alias] ?? NULL;
1243   }
1244
1245   /**
1246    * @param string $path
1247    * @param array $field
1248    */
1249   private function addSpecField($path, $field) {
1250     // Only add field to spec if we have permission
1251     if ($this->getCheckPermissions() && !empty($field['permission']) && !\CRM_Core_Permission::check($field['permission'])) {
1252       $this->apiFieldSpec[$path] = FALSE;
1253       return;
1254     }
1255     $this->apiFieldSpec[$path] = $field;
1256   }
1257
1258   /**
1259    * Add something to the api's debug output if debugging is enabled
1260    *
1261    * @param $key
1262    * @param $item
1263    */
1264   public function debug($key, $item) {
1265     if ($this->api->getDebug()) {
1266       $this->api->_debugOutput[$key][] = $item;
1267     }
1268   }
1269
1270 }