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\Event\Events
;
16 use Civi\Api4\Event\PostSelectQueryEvent
;
17 use Civi\Api4\Service\Schema\Joinable\CustomGroupJoinable
;
18 use Civi\Api4\Service\Schema\Joinable\Joinable
;
19 use Civi\Api4\Service\Schema\Joinable\OptionValueJoinable
;
20 use Civi\Api4\Utils\FormattingUtil
;
21 use Civi\Api4\Utils\CoreUtil
;
22 use Civi\Api4\Utils\SelectUtil
;
23 use CRM_Core_DAO_AllCoreTables
as AllCoreTables
;
26 * A query `node` may be in one of three formats:
28 * * leaf: [$fieldName, $operator, $criteria]
29 * * negated: ['NOT', $node]
30 * * branch: ['OR|NOT', [$node, $node, ...]]
32 * Leaf operators are one of:
34 * * '=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=",
35 * * "NOT LIKE", 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN',
36 * * 'IS NOT NULL', or 'IS NULL'.
38 class Api4SelectQuery
extends SelectQuery
{
43 protected $apiVersion = 4;
46 * @var \Civi\Api4\Service\Schema\Joinable\Joinable[]
47 * The joinable tables that have been joined so far
49 protected $joinedTables = [];
55 protected $selectAliases = [];
58 * If set to an array, this will start collecting debug info.
62 public $debugOutput = NULL;
69 public $forceSelectId = TRUE;
77 * @param \Civi\Api4\Generic\DAOGetAction $apiGet
79 public function __construct($apiGet) {
80 $this->entity
= $apiGet->getEntityName();
81 $this->checkPermissions
= $apiGet->getCheckPermissions();
82 $this->select
= $apiGet->getSelect();
83 $this->where
= $apiGet->getWhere();
84 $this->groupBy
= $apiGet->getGroupBy();
85 $this->orderBy
= $apiGet->getOrderBy();
86 $this->limit
= $apiGet->getLimit();
87 $this->offset
= $apiGet->getOffset();
88 $this->having
= $apiGet->getHaving();
89 // Always select ID of main table unless grouping is used
90 $this->forceSelectId
= !$this->groupBy
;
91 if ($apiGet->getDebug()) {
92 $this->debugOutput
=& $apiGet->_debugOutput
;
94 $baoName = CoreUtil
::getBAOFromApiName($this->entity
);
95 $this->entityFieldNames
= array_column($baoName::fields(), 'name');
96 foreach ($apiGet->entityFields() as $path => $field) {
97 $field['sql_name'] = '`' . self
::MAIN_TABLE_ALIAS
. '`.`' . $field['column_name'] . '`';
98 $this->addSpecField($path, $field);
101 $this->constructQueryObject($baoName);
103 // Add ACLs first to avoid redundant subclauses
104 $this->query
->where($this->getAclClause(self
::MAIN_TABLE_ALIAS
, $baoName));
108 * Builds final sql statement after all params are set.
111 * @throws \API_Exception
112 * @throws \CRM_Core_Exception
113 * @throws \Civi\API\Exception\UnauthorizedException
115 public function getSql() {
116 $this->buildSelectClause();
117 $this->buildWhereClause();
118 $this->buildOrderBy();
120 $this->buildGroupBy();
121 $this->buildHavingClause();
122 return $this->query
->toSQL();
126 * Why walk when you can
130 public function run() {
132 $sql = $this->getSql();
133 if (is_array($this->debugOutput
)) {
134 $this->debugOutput
['sql'][] = $sql;
136 $query = \CRM_Core_DAO
::executeQuery($sql);
138 while ($query->fetch()) {
139 $id = $query->id ??
$i++
;
140 if (in_array('row_count', $this->select
)) {
141 $results[]['row_count'] = (int) $query->c
;
145 foreach ($this->selectAliases
as $alias => $expr) {
146 $returnName = $alias;
147 $alias = str_replace('.', '_', $alias);
148 $results[$id][$returnName] = property_exists($query, $alias) ?
$query->$alias : NULL;
151 $event = new PostSelectQueryEvent($results, $this);
152 \Civi
::dispatcher()->dispatch(Events
::POST_SELECT_QUERY
, $event);
154 return $event->getResults();
157 protected function buildSelectClause() {
158 // An empty select is the same as *
159 if (empty($this->select
)) {
160 $this->select
= $this->entityFieldNames
;
162 elseif (in_array('row_count', $this->select
)) {
163 $this->query
->select("COUNT(*) AS `c`");
167 if ($this->forceSelectId
) {
168 $this->select
= array_merge(['id'], $this->select
);
171 // Expand wildcards in joins (the api wrapper already expanded non-joined wildcards)
172 $wildFields = array_filter($this->select
, function($item) {
173 return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE && strpos($item, '(') === FALSE && strpos($item, ' ') === FALSE;
175 foreach ($wildFields as $item) {
176 $pos = array_search($item, array_values($this->select
));
177 $this->joinFK($item);
178 $matches = SelectUtil
::getMatchingFields($item, array_keys($this->apiFieldSpec
));
179 array_splice($this->select
, $pos, 1, $matches);
181 $this->select
= array_unique($this->select
);
183 foreach ($this->select
as $item) {
184 $expr = SqlExpression
::convert($item, TRUE);
186 foreach ($expr->getFields() as $fieldName) {
187 $field = $this->getField($fieldName);
188 // Remove expressions with unknown fields without raising an error
190 $this->select
= array_diff($this->select
, [$item]);
191 if (is_array($this->debugOutput
)) {
192 $this->debugOutput
['undefined_fields'][] = $fieldName;
196 elseif ($field['is_many']) {
201 $alias = $expr->getAlias();
202 if ($alias != $expr->getExpr() && isset($this->apiFieldSpec
[$alias])) {
203 throw new \
API_Exception('Cannot use existing field name as alias');
205 $this->selectAliases
[$alias] = $expr->getExpr();
206 $this->query
->select($expr->render($this->apiFieldSpec
) . " AS `$alias`");
214 protected function buildWhereClause() {
215 foreach ($this->where
as $clause) {
216 $this->query
->where($this->treeWalkClauses($clause, 'WHERE'));
221 * Build HAVING clause.
223 * Every expression referenced must also be in the SELECT clause.
225 protected function buildHavingClause() {
226 foreach ($this->having
as $clause) {
227 $this->query
->having($this->treeWalkClauses($clause, 'HAVING'));
234 protected function buildOrderBy() {
235 foreach ($this->orderBy
as $item => $dir) {
236 if ($dir !== 'ASC' && $dir !== 'DESC') {
237 throw new \
API_Exception("Invalid sort direction. Cannot order by $item $dir");
239 $expr = SqlExpression
::convert($item);
240 foreach ($expr->getFields() as $fieldName) {
241 $this->getField($fieldName, TRUE);
243 $this->query
->orderBy($expr->render($this->apiFieldSpec
) . " $dir");
248 * @throws \CRM_Core_Exception
250 protected function buildLimit() {
251 if (!empty($this->limit
) ||
!empty($this->offset
)) {
252 // If limit is 0, mysql will actually return 0 results. Instead set to maximum possible.
253 $this->query
->limit($this->limit ?
: '18446744073709551615', $this->offset
);
258 * Adds GROUP BY clause to query
260 protected function buildGroupBy() {
261 foreach ($this->groupBy
as $item) {
262 $expr = SqlExpression
::convert($item);
263 foreach ($expr->getFields() as $fieldName) {
264 $this->getField($fieldName, TRUE);
266 $this->query
->groupBy($expr->render($this->apiFieldSpec
));
271 * Recursively validate and transform a branch or leaf clause array to SQL.
273 * @param array $clause
274 * @param string $type
276 * @return string SQL where clause
278 * @throws \API_Exception
279 * @uses composeClause() to generate the SQL etc.
281 protected function treeWalkClauses($clause, $type) {
282 switch ($clause[0]) {
286 if (count($clause[1]) === 1) {
287 // a single set so AND|OR is immaterial
288 return $this->treeWalkClauses($clause[1][0], $type);
291 $sql_subclauses = [];
292 foreach ($clause[1] as $subclause) {
293 $sql_subclauses[] = $this->treeWalkClauses($subclause, $type);
295 return '(' . implode("\n" . $clause[0], $sql_subclauses) . ')';
299 // If we get a group of clauses with no operator, assume AND
300 if (!is_string($clause[1][0])) {
301 $clause[1] = ['AND', $clause[1]];
303 return 'NOT (' . $this->treeWalkClauses($clause[1], $type) . ')';
306 return $this->composeClause($clause, $type);
311 * Validate and transform a leaf clause array to SQL.
312 * @param array $clause [$fieldName, $operator, $criteria]
313 * @param string $type
316 * @throws \API_Exception
319 protected function composeClause(array $clause, string $type) {
320 // Pad array for unary operators
321 list($expr, $operator, $value) = array_pad($clause, 3, NULL);
323 // For WHERE clause, expr must be the name of a field.
324 if ($type === 'WHERE') {
325 $field = $this->getField($expr, TRUE);
326 FormattingUtil
::formatInputValue($value, $expr, $field);
327 $fieldAlias = $field['sql_name'];
329 // For HAVING, expr must be an item in the SELECT clause
331 // Expr references a fieldName or alias
332 if (isset($this->selectAliases
[$expr])) {
334 // Attempt to format if this is a real field
335 if (isset($this->apiFieldSpec
[$expr])) {
336 FormattingUtil
::formatInputValue($value, $expr, $this->apiFieldSpec
[$expr]);
339 // Expr references a non-field expression like a function; convert to alias
340 elseif (in_array($expr, $this->selectAliases
)) {
341 $fieldAlias = array_search($expr, $this->selectAliases
);
343 // If either the having or select field contains a pseudoconstant suffix, match and perform substitution
345 list($fieldName) = explode(':', $expr);
346 foreach ($this->selectAliases
as $selectAlias => $selectExpr) {
347 list($selectField) = explode(':', $selectAlias);
348 if ($selectAlias === $selectExpr && $fieldName === $selectField && isset($this->apiFieldSpec
[$fieldName])) {
349 FormattingUtil
::formatInputValue($value, $expr, $this->apiFieldSpec
[$fieldName]);
350 $fieldAlias = $selectAlias;
355 if (!isset($fieldAlias)) {
356 throw new \
API_Exception("Invalid expression in HAVING clause: '$expr'. Must use a value from SELECT clause.");
358 $fieldAlias = '`' . $fieldAlias . '`';
361 $sql_clause = \CRM_Core_DAO
::createSQLFilter($fieldAlias, [$operator => $value]);
362 if ($sql_clause === NULL) {
363 throw new \
API_Exception("Invalid value in $type clause for '$expr'");
371 protected function getFields() {
372 return $this->apiFieldSpec
;
376 * Fetch a field from the getFields list
378 * @param string $expr
379 * @param bool $strict
380 * In strict mode, this will throw an exception if the field doesn't exist
382 * @return string|null
383 * @throws \API_Exception
385 public function getField($expr, $strict = FALSE) {
386 // If the expression contains a pseudoconstant filter like activity_type_id:label,
387 // strip it to look up the base field name, then add the field:filter key to apiFieldSpec
388 $col = strpos($expr, ':');
389 $fieldName = $col ?
substr($expr, 0, $col) : $expr;
390 // Perform join if field not yet available - this will add it to apiFieldSpec
391 if (!isset($this->apiFieldSpec
[$fieldName]) && strpos($fieldName, '.')) {
392 $this->joinFK($fieldName);
394 $field = $this->apiFieldSpec
[$fieldName] ??
NULL;
395 if ($strict && !$field) {
396 throw new \
API_Exception("Invalid field '$fieldName'");
398 $this->apiFieldSpec
[$expr] = $field;
403 * Joins a path and adds all fields in the joined eneity to apiFieldSpec
406 * @throws \API_Exception
409 protected function joinFK($key) {
410 if (isset($this->apiFieldSpec
[$key])) {
414 $pathArray = explode('.', $key);
416 /** @var \Civi\Api4\Service\Schema\Joiner $joiner */
417 $joiner = \Civi
::container()->get('joiner');
418 // 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.
419 array_pop($pathArray);
420 $pathString = implode('.', $pathArray);
422 if (!$joiner->canJoin($this, $pathString)) {
426 $joinPath = $joiner->join($this, $pathString);
429 foreach ($joinPath as $joinable) {
430 if ($joinable->getJoinType() === Joinable
::JOIN_TYPE_ONE_TO_MANY
) {
433 if ($joinable instanceof OptionValueJoinable
) {
434 \Civi
::log()->warning('Use API pseudoconstant suffix like :name or :label instead of join.', ['civi.tag' => 'deprecated']);
438 /** @var \Civi\Api4\Service\Schema\Joinable\Joinable $lastLink */
439 $lastLink = array_pop($joinPath);
441 // Custom field names are already prefixed
442 $isCustom = $lastLink instanceof CustomGroupJoinable
;
444 array_pop($pathArray);
446 $prefix = $pathArray ?
implode('.', $pathArray) . '.' : '';
447 // Cache field info for retrieval by $this->getField()
448 $joinEntity = $lastLink->getEntity();
449 foreach ($lastLink->getEntityFields() as $fieldObject) {
450 $fieldArray = ['entity' => $joinEntity] +
$fieldObject->toArray();
451 $fieldArray['sql_name'] = '`' . $lastLink->getAlias() . '`.`' . $fieldArray['column_name'] . '`';
452 $fieldArray['is_custom'] = $isCustom;
453 $fieldArray['is_join'] = TRUE;
454 $fieldArray['is_many'] = $isMany;
455 $this->addSpecField($prefix . $fieldArray['name'], $fieldArray);
460 * @param \Civi\Api4\Service\Schema\Joinable\Joinable $joinable
464 public function addJoinedTable(Joinable
$joinable) {
465 $this->joinedTables
[] = $joinable;
471 * @return FALSE|string
473 public function getFrom() {
474 return AllCoreTables
::getTableForClass(AllCoreTables
::getFullName($this->entity
));
480 public function getEntity() {
481 return $this->entity
;
487 public function getSelect() {
488 return $this->select
;
494 public function getWhere() {
501 public function getOrderBy() {
502 return $this->orderBy
;
508 public function getLimit() {
515 public function getOffset() {
516 return $this->offset
;
522 public function getSelectFields() {
523 return $this->selectFields
;
529 public function isFillUniqueFields() {
530 return $this->isFillUniqueFields
;
534 * @return \CRM_Utils_SQL_Select
536 public function getQuery() {
543 public function getJoins() {
550 public function getApiFieldSpec() {
551 return $this->apiFieldSpec
;
557 public function getEntityFieldNames() {
558 return $this->entityFieldNames
;
564 public function getAclFields() {
565 return $this->aclFields
;
569 * @return bool|string
571 public function getCheckPermissions() {
572 return $this->checkPermissions
;
578 public function getApiVersion() {
579 return $this->apiVersion
;
583 * @return \Civi\Api4\Service\Schema\Joinable\Joinable[]
585 public function getJoinedTables() {
586 return $this->joinedTables
;
590 * @return \Civi\Api4\Service\Schema\Joinable\Joinable
592 public function getJoinedTable($alias) {
593 foreach ($this->joinedTables
as $join) {
594 if ($join->getAlias() == $alias) {
601 * Get table name on basis of entity
603 * @param string $baoName
607 public function constructQueryObject($baoName) {
608 if (strstr($this->entity
, 'Custom_')) {
609 $this->query
= \CRM_Utils_SQL_Select
::from(CoreUtil
::getCustomTableByName(str_replace('Custom_', '', $this->entity
)) . ' ' . self
::MAIN_TABLE_ALIAS
);
610 $this->entityFieldNames
= array_keys($this->apiFieldSpec
);
613 $bao = new $baoName();
614 $this->query
= \CRM_Utils_SQL_Select
::from($bao->tableName() . ' ' . self
::MAIN_TABLE_ALIAS
);
619 * Checks if a field either belongs to the main entity or is joinable 1-to-1.
621 * Used to determine if a field can be added to the SELECT of the main query,
622 * or if it must be fetched post-query.
624 * @param string $fieldPath
627 public function isOneToOneField(string $fieldPath) {
628 return strpos($fieldPath, '.') === FALSE ||
!array_filter($this->getPathJoinTypes($fieldPath));
632 * Separates a string like 'emails.location_type.label' into an array, where
633 * each value in the array tells whether it is 1-1 or 1-n join type
635 * @param string $pathString
636 * Dot separated path to the field
639 * Index is table alias and value is boolean whether is 1-to-many join
641 public function getPathJoinTypes($pathString) {
642 $pathParts = explode('.', $pathString);
644 array_pop($pathParts);
647 $isMultipleChecker = function($alias) use ($query) {
648 foreach ($query->getJoinedTables() as $table) {
649 if ($table->getAlias() === $alias) {
650 return $table->getJoinType() === Joinable
::JOIN_TYPE_ONE_TO_MANY
;
656 foreach ($pathParts as $part) {
657 $path[$part] = $isMultipleChecker($part);
667 private function addSpecField($path, $field) {
668 // Only add field to spec if we have permission
669 if ($this->checkPermissions
&& !empty($field['permission']) && !\CRM_Core_Permission
::check($field['permission'])) {
670 $this->apiFieldSpec
[$path] = FALSE;
674 $defaults['is_custom'] = $defaults['is_join'] = $defaults['is_many'] = FALSE;
676 $this->apiFieldSpec
[$path] = $field;