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\API\SelectQuery
;
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
;
21 * A query `node` may be in one of three formats:
23 * * leaf: [$fieldName, $operator, $criteria]
24 * * negated: ['NOT', $node]
25 * * branch: ['OR|NOT', [$node, $node, ...]]
27 * Leaf operators are one of:
29 * * '=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=",
30 * * "NOT LIKE", 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN',
31 * * 'IS NOT NULL', or 'IS NULL'.
33 class Api4SelectQuery
extends SelectQuery
{
38 protected $apiVersion = 4;
44 protected $selectAliases = [];
47 * If set to an array, this will start collecting debug info.
51 public $debugOutput = NULL;
58 public $forceSelectId = TRUE;
66 * @param \Civi\Api4\Generic\DAOGetAction $apiGet
68 public function __construct($apiGet) {
69 $this->entity
= $apiGet->getEntityName();
70 $this->checkPermissions
= $apiGet->getCheckPermissions();
71 $this->select
= $apiGet->getSelect();
72 $this->where
= $apiGet->getWhere();
73 $this->groupBy
= $apiGet->getGroupBy();
74 $this->orderBy
= $apiGet->getOrderBy();
75 $this->limit
= $apiGet->getLimit();
76 $this->offset
= $apiGet->getOffset();
77 $this->having
= $apiGet->getHaving();
78 // Always select ID of main table unless grouping is used
79 $this->forceSelectId
= !$this->groupBy
;
80 if ($apiGet->getDebug()) {
81 $this->debugOutput
=& $apiGet->_debugOutput
;
83 foreach ($apiGet->entityFields() as $field) {
84 $this->entityFieldNames
[] = $field['name'];
85 $field['sql_name'] = '`' . self
::MAIN_TABLE_ALIAS
. '`.`' . $field['column_name'] . '`';
86 $this->addSpecField($field['name'], $field);
89 $baoName = CoreUtil
::getBAOFromApiName($this->entity
);
90 $this->constructQueryObject();
92 // Add ACLs first to avoid redundant subclauses
93 $this->query
->where($this->getAclClause(self
::MAIN_TABLE_ALIAS
, $baoName));
95 // Add explicit joins. Other joins implied by dot notation may be added later
96 $this->addExplicitJoins($apiGet->getJoin());
100 * Builds final sql statement after all params are set.
103 * @throws \API_Exception
104 * @throws \CRM_Core_Exception
105 * @throws \Civi\API\Exception\UnauthorizedException
107 public function getSql() {
108 $this->buildSelectClause();
109 $this->buildWhereClause();
110 $this->buildOrderBy();
112 $this->buildGroupBy();
113 $this->buildHavingClause();
114 return $this->query
->toSQL();
118 * Why walk when you can
122 public function run() {
124 $sql = $this->getSql();
125 if (is_array($this->debugOutput
)) {
126 $this->debugOutput
['sql'][] = $sql;
128 $query = \CRM_Core_DAO
::executeQuery($sql);
129 while ($query->fetch()) {
130 if (in_array('row_count', $this->select
)) {
131 $results[]['row_count'] = (int) $query->c
;
135 foreach ($this->selectAliases
as $alias => $expr) {
136 $returnName = $alias;
137 $alias = str_replace('.', '_', $alias);
138 $result[$returnName] = property_exists($query, $alias) ?
$query->$alias : NULL;
140 $results[] = $result;
142 FormattingUtil
::formatOutputValues($results, $this->getApiFieldSpec(), $this->getEntity());
146 protected function buildSelectClause() {
147 // An empty select is the same as *
148 if (empty($this->select
)) {
149 $this->select
= $this->entityFieldNames
;
151 elseif (in_array('row_count', $this->select
)) {
152 $this->query
->select("COUNT(*) AS `c`");
156 if ($this->forceSelectId
) {
157 $this->select
= array_merge(['id'], $this->select
);
160 // Expand wildcards in joins (the api wrapper already expanded non-joined wildcards)
161 $wildFields = array_filter($this->select
, function($item) {
162 return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE && strpos($item, '(') === FALSE && strpos($item, ' ') === FALSE;
164 foreach ($wildFields as $item) {
165 $pos = array_search($item, array_values($this->select
));
166 $this->autoJoinFK($item);
167 $matches = SelectUtil
::getMatchingFields($item, array_keys($this->apiFieldSpec
));
168 array_splice($this->select
, $pos, 1, $matches);
170 $this->select
= array_unique($this->select
);
172 foreach ($this->select
as $item) {
173 $expr = SqlExpression
::convert($item, TRUE);
175 foreach ($expr->getFields() as $fieldName) {
176 $field = $this->getField($fieldName);
177 // Remove expressions with unknown fields without raising an error
179 $this->select
= array_diff($this->select
, [$item]);
180 if (is_array($this->debugOutput
)) {
181 $this->debugOutput
['undefined_fields'][] = $fieldName;
187 $alias = $expr->getAlias();
188 if ($alias != $expr->getExpr() && isset($this->apiFieldSpec
[$alias])) {
189 throw new \
API_Exception('Cannot use existing field name as alias');
191 $this->selectAliases
[$alias] = $expr->getExpr();
192 $this->query
->select($expr->render($this->apiFieldSpec
) . " AS `$alias`");
200 protected function buildWhereClause() {
201 foreach ($this->where
as $clause) {
202 $sql = $this->treeWalkClauses($clause, 'WHERE');
204 $this->query
->where($sql);
210 * Build HAVING clause.
212 * Every expression referenced must also be in the SELECT clause.
214 protected function buildHavingClause() {
215 foreach ($this->having
as $clause) {
216 $this->query
->having($this->treeWalkClauses($clause, 'HAVING'));
223 protected function buildOrderBy() {
224 foreach ($this->orderBy
as $item => $dir) {
225 if ($dir !== 'ASC' && $dir !== 'DESC') {
226 throw new \
API_Exception("Invalid sort direction. Cannot order by $item $dir");
228 $expr = $this->getExpression($item);
229 $column = $expr->render($this->apiFieldSpec
);
231 // Use FIELD() function to sort on pseudoconstant values
232 $suffix = strstr($item, ':');
233 if ($suffix && $expr->getType() === 'SqlField') {
234 $field = $this->getField($item);
235 $options = FormattingUtil
::getPseudoconstantList($field['entity'], $field['name'], substr($suffix, 1));
238 $column = "FIELD($column,'" . implode("','", array_keys($options)) . "')";
241 $this->query
->orderBy("$column $dir");
246 * @throws \CRM_Core_Exception
248 protected function buildLimit() {
249 if (!empty($this->limit
) ||
!empty($this->offset
)) {
250 // If limit is 0, mysql will actually return 0 results. Instead set to maximum possible.
251 $this->query
->limit($this->limit ?
: '18446744073709551615', $this->offset
);
256 * Adds GROUP BY clause to query
258 protected function buildGroupBy() {
259 foreach ($this->groupBy
as $item) {
260 $this->query
->groupBy($this->getExpression($item)->render($this->apiFieldSpec
));
265 * Recursively validate and transform a branch or leaf clause array to SQL.
267 * @param array $clause
268 * @param string $type
270 * @return string SQL where clause
272 * @throws \API_Exception
273 * @uses composeClause() to generate the SQL etc.
275 protected function treeWalkClauses($clause, $type) {
277 if (in_array($clause[0], ['AND', 'OR', 'NOT']) && empty($clause[1])) {
280 switch ($clause[0]) {
284 if (count($clause[1]) === 1) {
285 // a single set so AND|OR is immaterial
286 return $this->treeWalkClauses($clause[1][0], $type);
289 $sql_subclauses = [];
290 foreach ($clause[1] as $subclause) {
291 $sql_subclauses[] = $this->treeWalkClauses($subclause, $type);
293 return '(' . implode("\n" . $clause[0], $sql_subclauses) . ')';
297 // If we get a group of clauses with no operator, assume AND
298 if (!is_string($clause[1][0])) {
299 $clause[1] = ['AND', $clause[1]];
301 return 'NOT (' . $this->treeWalkClauses($clause[1], $type) . ')';
304 return $this->composeClause($clause, $type);
309 * Validate and transform a leaf clause array to SQL.
310 * @param array $clause [$fieldName, $operator, $criteria]
311 * @param string $type
314 * @throws \API_Exception
317 protected function composeClause(array $clause, string $type) {
318 // Pad array for unary operators
319 list($expr, $operator, $value) = array_pad($clause, 3, NULL);
320 if (!in_array($operator, \CRM_Core_DAO
::acceptedSQLOperators(), TRUE)) {
321 throw new \
API_Exception('Illegal operator');
324 // For WHERE clause, expr must be the name of a field.
325 if ($type === 'WHERE') {
326 $field = $this->getField($expr, TRUE);
327 FormattingUtil
::formatInputValue($value, $expr, $field);
328 $fieldAlias = $field['sql_name'];
330 // For HAVING, expr must be an item in the SELECT clause
331 elseif ($type === 'HAVING') {
332 // Expr references a fieldName or alias
333 if (isset($this->selectAliases
[$expr])) {
335 // Attempt to format if this is a real field
336 if (isset($this->apiFieldSpec
[$expr])) {
337 FormattingUtil
::formatInputValue($value, $expr, $this->apiFieldSpec
[$expr]);
340 // Expr references a non-field expression like a function; convert to alias
341 elseif (in_array($expr, $this->selectAliases
)) {
342 $fieldAlias = array_search($expr, $this->selectAliases
);
344 // If either the having or select field contains a pseudoconstant suffix, match and perform substitution
346 list($fieldName) = explode(':', $expr);
347 foreach ($this->selectAliases
as $selectAlias => $selectExpr) {
348 list($selectField) = explode(':', $selectAlias);
349 if ($selectAlias === $selectExpr && $fieldName === $selectField && isset($this->apiFieldSpec
[$fieldName])) {
350 FormattingUtil
::formatInputValue($value, $expr, $this->apiFieldSpec
[$fieldName]);
351 $fieldAlias = $selectAlias;
356 if (!isset($fieldAlias)) {
357 throw new \
API_Exception("Invalid expression in HAVING clause: '$expr'. Must use a value from SELECT clause.");
359 $fieldAlias = '`' . $fieldAlias . '`';
361 elseif ($type === 'ON') {
362 $expr = $this->getExpression($expr);
363 $fieldName = count($expr->getFields()) === 1 ?
$expr->getFields()[0] : NULL;
364 $fieldAlias = $expr->render($this->apiFieldSpec
);
365 if (is_string($value)) {
366 $valExpr = $this->getExpression($value);
367 if ($fieldName && $valExpr->getType() === 'SqlString') {
368 FormattingUtil
::formatInputValue($valExpr->expr
, $fieldName, $this->apiFieldSpec
[$fieldName]);
370 return sprintf('%s %s %s', $fieldAlias, $operator, $valExpr->render($this->apiFieldSpec
));
372 elseif ($fieldName) {
373 FormattingUtil
::formatInputValue($value, $fieldName, $this->apiFieldSpec
[$fieldName]);
377 $sql_clause = \CRM_Core_DAO
::createSQLFilter($fieldAlias, [$operator => $value]);
378 if ($sql_clause === NULL) {
379 throw new \
API_Exception("Invalid value in $type clause for '$expr'");
385 * @param string $expr
386 * @return SqlExpression
387 * @throws \API_Exception
389 protected function getExpression(string $expr) {
390 $sqlExpr = SqlExpression
::convert($expr);
391 foreach ($sqlExpr->getFields() as $fieldName) {
392 $this->getField($fieldName, TRUE);
400 protected function getFields() {
401 return $this->apiFieldSpec
;
405 * Fetch a field from the getFields list
407 * @param string $expr
408 * @param bool $strict
409 * In strict mode, this will throw an exception if the field doesn't exist
411 * @return string|null
412 * @throws \API_Exception
414 public function getField($expr, $strict = FALSE) {
415 // If the expression contains a pseudoconstant filter like activity_type_id:label,
416 // strip it to look up the base field name, then add the field:filter key to apiFieldSpec
417 $col = strpos($expr, ':');
418 $fieldName = $col ?
substr($expr, 0, $col) : $expr;
419 // Perform join if field not yet available - this will add it to apiFieldSpec
420 if (!isset($this->apiFieldSpec
[$fieldName]) && strpos($fieldName, '.')) {
421 $this->autoJoinFK($fieldName);
423 $field = $this->apiFieldSpec
[$fieldName] ??
NULL;
424 if ($strict && !$field) {
425 throw new \
API_Exception("Invalid field '$fieldName'");
427 $this->apiFieldSpec
[$expr] = $field;
432 * Join onto other entities as specified by the api call.
435 * @throws \API_Exception
436 * @throws \Civi\API\Exception\NotImplementedException
438 private function addExplicitJoins($joins) {
439 foreach ($joins as $join) {
440 // First item in the array is the entity name
441 $entity = array_shift($join);
442 // Which might contain an alias. Split on the keyword "AS"
443 list($entity, $alias) = array_pad(explode(' AS ', $entity), 2, NULL);
444 // Ensure alias is a safe string, and supply default if not given
445 $alias = $alias ? \CRM_Utils_String
::munge($alias) : strtolower($entity);
446 // First item in the array is a boolean indicating if the join is required (aka INNER or LEFT).
447 // The rest are join conditions.
448 $side = array_shift($join) ?
'INNER' : 'LEFT';
449 $joinEntityGet = \Civi\API\Request
::create($entity, 'get', ['version' => 4, 'checkPermissions' => $this->checkPermissions
]);
450 foreach ($joinEntityGet->entityFields() as $field) {
451 $field['sql_name'] = '`' . $alias . '`.`' . $field['column_name'] . '`';
452 $field['is_join'] = TRUE;
453 $this->addSpecField($alias . '.' . $field['name'], $field);
455 $conditions = $this->getJoinConditions($entity, $alias);
456 foreach (array_filter($join) as $clause) {
457 $conditions[] = $this->treeWalkClauses($clause, 'ON');
459 $tableName = CoreUtil
::getTableName($entity);
460 $this->join($side, $tableName, $alias, $conditions);
465 * Supply conditions for an explicit join.
471 private function getJoinConditions($entity, $alias) {
473 // getAclClause() expects a stack of 1-to-1 join fields to help it dedupe, but this is more flexible,
474 // so unless this is a direct 1-to-1 join with the main entity, we'll just hack it
475 // with a padded empty stack to bypass its deduping.
476 $stack = [NULL, NULL];
477 foreach ($this->apiFieldSpec
as $name => $field) {
478 if ($field['entity'] !== $entity && $field['fk_entity'] === $entity) {
479 $conditions[] = $this->treeWalkClauses([$name, '=', "$alias.id"], 'ON');
481 elseif (strpos($name, "$alias.") === 0 && substr_count($name, '.') === 1 && $field['fk_entity'] === $this->entity
) {
482 $conditions[] = $this->treeWalkClauses([$name, '=', 'id'], 'ON');
486 // 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
487 if (count($conditions) > 1) {
488 $stack = [NULL, NULL];
491 $baoName = CoreUtil
::getBAOFromApiName($entity);
492 $acls = array_values($this->getAclClause($alias, $baoName, $stack));
493 return array_merge($acls, $conditions);
497 * Joins a path and adds all fields in the joined entity to apiFieldSpec
500 * @throws \API_Exception
503 protected function autoJoinFK($key) {
504 if (isset($this->apiFieldSpec
[$key])) {
508 $pathArray = explode('.', $key);
510 /** @var \Civi\Api4\Service\Schema\Joiner $joiner */
511 $joiner = \Civi
::container()->get('joiner');
512 // 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.
513 array_pop($pathArray);
514 $pathString = implode('.', $pathArray);
516 if (!$joiner->canAutoJoin($this->getFrom(), $pathString)) {
520 $joinPath = $joiner->join($this, $pathString);
522 $lastLink = array_pop($joinPath);
524 // Custom field names are already prefixed
525 $isCustom = $lastLink instanceof CustomGroupJoinable
;
527 array_pop($pathArray);
529 $prefix = $pathArray ?
implode('.', $pathArray) . '.' : '';
530 // Cache field info for retrieval by $this->getField()
531 foreach ($lastLink->getEntityFields() as $fieldObject) {
532 $fieldArray = $fieldObject->toArray();
533 $fieldArray['sql_name'] = '`' . $lastLink->getAlias() . '`.`' . $fieldArray['column_name'] . '`';
534 $fieldArray['is_custom'] = $isCustom;
535 $fieldArray['is_join'] = TRUE;
536 $this->addSpecField($prefix . $fieldArray['name'], $fieldArray);
541 * @return FALSE|string
543 public function getFrom() {
544 return CoreUtil
::getTableName($this->entity
);
550 public function getEntity() {
551 return $this->entity
;
557 public function getSelect() {
558 return $this->select
;
564 public function getWhere() {
571 public function getOrderBy() {
572 return $this->orderBy
;
578 public function getLimit() {
585 public function getOffset() {
586 return $this->offset
;
592 public function getSelectFields() {
593 return $this->selectFields
;
597 * @return \CRM_Utils_SQL_Select
599 public function getQuery() {
606 public function getJoins() {
613 public function getApiFieldSpec() {
614 return $this->apiFieldSpec
;
620 public function getEntityFieldNames() {
621 return $this->entityFieldNames
;
627 public function getAclFields() {
628 return $this->aclFields
;
632 * @return bool|string
634 public function getCheckPermissions() {
635 return $this->checkPermissions
;
641 public function getApiVersion() {
642 return $this->apiVersion
;
646 * Get table name on basis of entity
650 public function constructQueryObject() {
651 $tableName = CoreUtil
::getTableName($this->entity
);
652 $this->query
= \CRM_Utils_SQL_Select
::from($tableName . ' ' . self
::MAIN_TABLE_ALIAS
);
659 private function addSpecField($path, $field) {
660 // Only add field to spec if we have permission
661 if ($this->checkPermissions
&& !empty($field['permission']) && !\CRM_Core_Permission
::check($field['permission'])) {
662 $this->apiFieldSpec
[$path] = FALSE;
666 $defaults['is_custom'] = $defaults['is_join'] = FALSE;
668 $this->apiFieldSpec
[$path] = $field;