3 +--------------------------------------------------------------------+
4 | Copyright CiviCRM LLC. All rights reserved. |
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 +--------------------------------------------------------------------+
12 namespace Civi\Api4\Query
;
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
;
20 * A query `node` may be in one of three formats:
22 * * leaf: [$fieldName, $operator, $criteria]
23 * * negated: ['NOT', $node]
24 * * branch: ['OR|NOT', [$node, $node, ...]]
26 * Leaf operators are one of:
28 * * '=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=",
29 * * 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN',
30 * * 'IS NOT NULL', or 'IS NULL', 'CONTAINS'.
32 class Api4SelectQuery
{
35 MAIN_TABLE_ALIAS
= 'a',
36 UNLIMITED
= '18446744073709551615';
39 * @var \CRM_Utils_SQL_Select
46 protected $joins = [];
51 protected $apiFieldSpec;
56 protected $entityFieldNames = [];
61 protected $aclFields = [];
64 * @var \Civi\Api4\Generic\DAOGetAction
72 protected $selectAliases = [];
77 public $forceSelectId = TRUE;
82 private $explicitJoins = [];
85 * @param \Civi\Api4\Generic\DAOGetAction $apiGet
87 public function __construct($apiGet) {
90 // Always select ID of main table unless grouping by something else
91 $this->forceSelectId
= !$this->getGroupBy() ||
$this->getGroupBy() === ['id'];
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);
100 $tableName = CoreUtil
::getTableName($this->getEntity());
101 $this->query
= \CRM_Utils_SQL_Select
::from($tableName . ' ' . self
::MAIN_TABLE_ALIAS
);
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));
107 // Add explicit joins. Other joins implied by dot notation may be added later
108 $this->addExplicitJoins();
112 * Builds main final sql statement after initialization.
115 * @throws \API_Exception
116 * @throws \CRM_Core_Exception
118 public function getSql() {
119 $this->buildSelectClause();
120 $this->buildWhereClause();
121 $this->buildOrderBy();
123 $this->buildGroupBy();
124 $this->buildHavingClause();
125 return $this->query
->toSQL();
129 * Why walk when you can
133 public function run() {
135 $sql = $this->getSql();
136 $this->debug('sql', $sql);
137 $query = \CRM_Core_DAO
::executeQuery($sql);
138 while ($query->fetch()) {
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;
145 $results[] = $result;
147 FormattingUtil
::formatOutputValues($results, $this->apiFieldSpec
, $this->getEntity(), 'get', $this->selectAliases
);
153 * @throws \API_Exception
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();
162 // Use a subquery to count groups from GROUP BY or results filtered by HAVING
164 // With no HAVING, just select the last field grouped by
165 if (!$this->getHaving()) {
166 $select = array_slice($this->getGroupBy(), -1);
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`";
174 $this->debug('sql', $sql);
175 return (int) \CRM_Core_DAO
::singleValueQuery($sql);
179 * @param array $select
180 * Array of select expressions; defaults to $this->getSelect
181 * @throws \API_Exception
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
;
191 if ($this->forceSelectId
) {
192 $select = array_merge(['id'], $select);
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']);
203 foreach ($customGroups as $groupName) {
204 $customSelect[] = "$groupName.*";
206 array_splice($select, $customStar, 1, $customSelect);
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;
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);
222 $select = array_unique($select);
224 foreach ($select as $item) {
225 $expr = SqlExpression
::convert($item, TRUE);
227 foreach ($expr->getFields() as $fieldName) {
228 $field = $this->getField($fieldName);
229 // Remove expressions with unknown fields without raising an error
231 $select = array_diff($select, [$item]);
232 $this->debug('undefined_fields', $fieldName);
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');
241 $this->selectAliases
[$alias] = $expr->getExpr();
242 $this->query
->select($expr->render($this->apiFieldSpec
) . " AS `$alias`");
248 * Add WHERE clause to query
250 protected function buildWhereClause() {
251 foreach ($this->getWhere() as $clause) {
252 $sql = $this->treeWalkClauses($clause, 'WHERE');
254 $this->query
->where($sql);
260 * Add HAVING clause to query
262 * Every expression referenced must also be in the SELECT clause.
264 protected function buildHavingClause() {
265 foreach ($this->getHaving() as $clause) {
266 $this->query
->having($this->treeWalkClauses($clause, 'HAVING'));
271 * Add ORDER BY to query
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");
280 $expr = $this->getExpression($item);
281 $column = $expr->render($this->apiFieldSpec
);
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));
290 $column = "FIELD($column,'" . implode("','", array_keys($options)) . "')";
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 . '`';
300 throw new \
API_Exception("Invalid field '{$item}'");
304 $this->query
->orderBy("$column $dir");
311 * @throws \CRM_Core_Exception
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());
321 * Add GROUP BY clause to query
323 protected function buildGroupBy() {
324 foreach ($this->getGroupBy() as $item) {
325 $this->query
->groupBy($this->getExpression($item)->render($this->apiFieldSpec
));
330 * Recursively validate and transform a branch or leaf clause array to SQL.
332 * @param array $clause
333 * @param string $type
335 * @return string SQL where clause
337 * @throws \API_Exception
338 * @uses composeClause() to generate the SQL etc.
340 protected function treeWalkClauses($clause, $type) {
342 if (in_array($clause[0], ['AND', 'OR', 'NOT']) && empty($clause[1])) {
345 switch ($clause[0]) {
349 if (count($clause[1]) === 1) {
350 // a single set so AND|OR is immaterial
351 return $this->treeWalkClauses($clause[1][0], $type);
354 $sql_subclauses = [];
355 foreach ($clause[1] as $subclause) {
356 $sql_subclauses[] = $this->treeWalkClauses($subclause, $type);
358 return '(' . implode("\n" . $clause[0], $sql_subclauses) . ')';
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]];
366 return 'NOT (' . $this->treeWalkClauses($clause[1], $type) . ')';
369 return $this->composeClause($clause, $type);
374 * Validate and transform a leaf clause array to SQL.
375 * @param array $clause [$fieldName, $operator, $criteria]
376 * @param string $type
379 * @throws \API_Exception
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');
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'];
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])) {
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);
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
);
410 // If either the having or select field contains a pseudoconstant suffix, match and perform substitution
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;
423 if (!isset($fieldAlias)) {
424 throw new \
API_Exception("Invalid expression in HAVING clause: '$expr'. Must use a value from SELECT clause.");
426 $fieldAlias = '`' . $fieldAlias . '`';
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]);
440 $value = $valExpr->render($this->apiFieldSpec
);
441 return sprintf('%s %s %s', $fieldAlias, $operator, $value);
444 elseif ($fieldName) {
445 $field = $this->getField($fieldName);
446 FormattingUtil
::formatInputValue($value, $fieldName, $field, $operator);
450 if ($operator === 'CONTAINS') {
451 switch ($field['serialize'] ??
NULL) {
452 case \CRM_Core_DAO
::SERIALIZE_JSON
:
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));
459 case \CRM_Core_DAO
::SERIALIZE_SEPARATOR_BOOKEND
:
461 $value = '%' . \CRM_Core_DAO
::VALUE_SEPARATOR
. $value . \CRM_Core_DAO
::VALUE_SEPARATOR
. '%';
466 $value = '%' . $value . '%';
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'");
479 * @param string $expr
480 * @return SqlExpression
481 * @throws \API_Exception
483 protected function getExpression(string $expr) {
484 $sqlExpr = SqlExpression
::convert($expr);
485 foreach ($sqlExpr->getFields() as $fieldName) {
486 $this->getField($fieldName, TRUE);
492 * Get acl clause for an entity
494 * @param string $tableAlias
495 * @param \CRM_Core_DAO|string $baoName
496 * @param array $stack
499 public function getAclClause($tableAlias, $baoName, $stack = []) {
500 if (!$this->getCheckPermissions()) {
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)) {
509 $clauses = $baoName::getSelectWhereClause($tableAlias);
511 // Track field clauses added to the main entity
512 $this->aclFields
= array_keys($clauses);
514 return array_filter($clauses);
518 * Fetch a field from the getFields list
520 * @param string $expr
521 * @param bool $strict
522 * In strict mode, this will throw an exception if the field doesn't exist
525 * @throws \API_Exception
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);
536 $field = $this->apiFieldSpec
[$fieldName] ??
NULL;
537 if ($strict && !$field) {
538 throw new \
API_Exception("Invalid field '$fieldName'");
541 $this->apiFieldSpec
[$expr] = $field;
547 * Join onto other entities as specified by the api call.
549 * @throws \API_Exception
550 * @throws \Civi\API\Exception\NotImplementedException
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);
570 $tableName = CoreUtil
::getTableName($entity);
571 // Save join info to be retrieved by $this->getExplicitJoin()
572 $this->explicitJoins
[$alias] = [
574 'table' => $tableName,
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);
583 $conditions = $this->getJoinConditions($join, $entity, $alias, $joinEntityFields);
585 foreach (array_filter($join) as $clause) {
586 $conditions[] = $this->treeWalkClauses($clause, 'ON');
588 $this->join($side, $tableName, $alias, $conditions);
593 * Supply conditions for an explicit join.
595 * @param array $joinTree
596 * @param string $joinEntity
597 * @param string $alias
598 * @param array $joinEntityFields
601 private function getJoinConditions($joinTree, $joinEntity, $alias, $joinEntityFields) {
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) {
613 foreach ([$sideA, $sideB] as $expr) {
614 if ($expr === "$alias.id" ||
!empty($joinEntityFields["$alias.$expr"]['fk_entity'])) {
620 // If we're not explicitly referencing the ID (or some other FK field) of the joinEntity, search for a default
622 foreach ($this->apiFieldSpec
as $name => $field) {
623 if ($field['entity'] !== $joinEntity && $field['fk_entity'] === $joinEntity) {
624 $conditions[] = $this->treeWalkClauses([$name, '=', "$alias.id"], 'ON');
626 elseif (strpos($name, "$alias.") === 0 && substr_count($name, '.') === 1 && $field['fk_entity'] === $this->getEntity()) {
627 $conditions[] = $this->treeWalkClauses([$name, '=', 'id'], 'ON');
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];
637 $baoName = CoreUtil
::getBAOFromApiName($joinEntity);
638 $acls = array_values($this->getAclClause($alias, $baoName, $stack));
639 return array_merge($acls, $conditions);
643 * Join via a Bridge table
645 * This creates a double-join in sql that appears to the API user like a single join.
647 * @param array $joinTree
648 * @param string $joinEntity
649 * @param string $alias
651 * @throws \API_Exception
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));
664 /* @var \CRM_Core_DAO $bridgeDAO */
665 $bridgeDAO = $bridgeInfo['dao'];
666 $bridgeTable = $bridgeDAO::getTableName();
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())) {
682 if (!$joinRef ||
!$baseRef) {
683 throw new \
API_Exception("Unable to join $bridgeEntity to $joinEntity");
685 // Create link between bridge entity and join entity
687 "`$bridgeAlias`.`{$joinRef->getReferenceKey()}` = `$alias`.`{$joinRef->getTargetKey()}`",
689 // For dynamic references, also add the type column (e.g. `entity_table`)
690 if ($joinRef->getTypeColumn()) {
691 $joinConditions[] = "`$bridgeAlias`.`{$joinRef->getTypeColumn()}` = '$joinTable'";
693 // Register fields (other than bridge FK fields) from the bridge entity as if they belong to the join entity
695 foreach ($bridgeEntityGet->entityFields() as $name => $field) {
696 if ($name === 'id' ||
$name === $joinRef->getReferenceKey() ||
$name === $joinRef->getTypeColumn() ||
$name === $baseRef->getReferenceKey() ||
$name === $baseRef->getTypeColumn()) {
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'];
704 // Move conditions for the bridge join out of the joinTree
705 $bridgeConditions = [];
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
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
);
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
);
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');
736 // If no bridge conditions were specified, link it to the base entity
738 if (!in_array($this->getEntity(), $baseRef->getTargetEntities())) {
739 throw new \
API_Exception("Unable to join $bridgeEntity to " . $this->getEntity());
741 $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getReferenceKey()}` = a.`{$baseRef->getTargetKey()}`";
742 if ($baseRef->getTypeColumn()) {
743 $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getTypeColumn()}` = '" . $this->getFrom() . "'";
747 $this->join('LEFT', $bridgeTable, $bridgeAlias, $bridgeConditions);
749 $baoName = CoreUtil
::getBAOFromApiName($joinEntity);
750 $acls = array_values($this->getAclClause($alias, $baoName, [NULL, NULL]));
751 return array_merge($acls, $joinConditions);
755 * Joins a path and adds all fields in the joined entity to apiFieldSpec
758 * @throws \API_Exception
761 protected function autoJoinFK($key) {
762 if (isset($this->apiFieldSpec
[$key])) {
766 $pathArray = explode('.', $key);
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);
774 $joinPath = $joiner->autoJoin($this, $pathArray);
776 catch (\Exception
$e) {
779 $lastLink = array_pop($joinPath);
781 // Custom field names are already prefixed
782 $isCustom = $lastLink instanceof CustomGroupJoinable
;
784 array_pop($pathArray);
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);
796 * @param string $side
797 * @param string $tableName
798 * @param string $tableAlias
799 * @param array $conditions
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));
810 * @return FALSE|string
812 public function getFrom() {
813 return CoreUtil
::getTableName($this->getEntity());
819 public function getEntity() {
820 return $this->api
->getEntityName();
826 public function getSelect() {
827 return $this->api
->getSelect();
833 public function getWhere() {
834 return $this->api
->getWhere();
840 public function getHaving() {
841 return $this->api
->getHaving();
847 public function getJoin() {
848 return $this->api
->getJoin();
854 public function getGroupBy() {
855 return $this->api
->getGroupBy();
861 public function getOrderBy() {
862 return $this->api
->getOrderBy();
868 public function getLimit() {
869 return $this->api
->getLimit();
875 public function getOffset() {
876 return $this->api
->getOffset();
880 * @return \CRM_Utils_SQL_Select
882 public function getQuery() {
887 * @return bool|string
889 public function getCheckPermissions() {
890 return $this->api
->getCheckPermissions();
894 * @param string $alias
897 public function getExplicitJoin($alias) {
898 return $this->explicitJoins
[$alias] ??
NULL;
902 * @param string $path
903 * @param array $field
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;
911 $this->apiFieldSpec
[$path] = $field;
915 * Add something to the api's debug output if debugging is enabled
920 public function debug($key, $item) {
921 if ($this->api
->getDebug()) {
922 $this->api
->_debugOutput
[$key][] = $item;