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\Utils\FormattingUtil
;
20 use Civi\Api4\Utils\CoreUtil
;
21 use Civi\Api4\Utils\SelectUtil
;
22 use CRM_Core_DAO_AllCoreTables
as AllCoreTables
;
25 * A query `node` may be in one of three formats:
27 * * leaf: [$fieldName, $operator, $criteria]
28 * * negated: ['NOT', $node]
29 * * branch: ['OR|NOT', [$node, $node, ...]]
31 * Leaf operators are one of:
33 * * '=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=",
34 * * "NOT LIKE", 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN',
35 * * 'IS NOT NULL', or 'IS NULL'.
37 class Api4SelectQuery
extends SelectQuery
{
42 protected $apiVersion = 4;
45 * @var \Civi\Api4\Service\Schema\Joinable\Joinable[]
46 * The joinable tables that have been joined so far
48 protected $joinedTables = [];
51 * If set to an array, this will start collecting debug info.
55 public $debugOutput = NULL;
63 * @param \Civi\Api4\Generic\DAOGetAction $apiGet
65 public function __construct($apiGet) {
66 $this->entity
= $apiGet->getEntityName();
67 $this->checkPermissions
= $apiGet->getCheckPermissions();
68 $this->select
= $apiGet->getSelect();
69 $this->where
= $apiGet->getWhere();
70 $this->groupBy
= $apiGet->getGroupBy();
71 $this->orderBy
= $apiGet->getOrderBy();
72 $this->limit
= $apiGet->getLimit();
73 $this->offset
= $apiGet->getOffset();
74 if ($apiGet->getDebug()) {
75 $this->debugOutput
=& $apiGet->_debugOutput
;
77 $baoName = CoreUtil
::getBAOFromApiName($this->entity
);
78 $this->entityFieldNames
= array_column($baoName::fields(), 'name');
79 foreach ($apiGet->entityFields() as $path => $field) {
80 $field['sql_name'] = '`' . self
::MAIN_TABLE_ALIAS
. '`.`' . $field['column_name'] . '`';
81 $this->addSpecField($path, $field);
84 $this->constructQueryObject($baoName);
86 // Add ACLs first to avoid redundant subclauses
87 $this->query
->where($this->getAclClause(self
::MAIN_TABLE_ALIAS
, $baoName));
91 * Builds final sql statement after all params are set.
94 * @throws \API_Exception
95 * @throws \CRM_Core_Exception
96 * @throws \Civi\API\Exception\UnauthorizedException
98 public function getSql() {
99 $this->buildSelectClause();
100 $this->buildWhereClause();
101 $this->buildOrderBy();
103 $this->buildGroupBy();
104 return $this->query
->toSQL();
108 * Why walk when you can
112 public function run() {
114 $sql = $this->getSql();
115 if (is_array($this->debugOutput
)) {
116 $this->debugOutput
['sql'][] = $sql;
118 $query = \CRM_Core_DAO
::executeQuery($sql);
120 while ($query->fetch()) {
121 $id = $query->id ??
$i++
;
122 if (in_array('row_count', $this->select
)) {
123 $results[]['row_count'] = (int) $query->c
;
127 foreach ($this->select
as $alias) {
128 $returnName = $alias;
129 if ($this->isOneToOneField($alias)) {
130 $alias = str_replace('.', '_', $alias);
131 $results[$id][$returnName] = property_exists($query, $alias) ?
$query->$alias : NULL;
135 $event = new PostSelectQueryEvent($results, $this);
136 \Civi
::dispatcher()->dispatch(Events
::POST_SELECT_QUERY
, $event);
138 return $event->getResults();
141 protected function buildSelectClause() {
142 // An empty select is the same as *
143 if (empty($this->select
)) {
144 $this->select
= $this->entityFieldNames
;
146 elseif (in_array('row_count', $this->select
)) {
147 $this->query
->select("COUNT(*) AS `c`");
151 // Always select ID (unless we're doing groupBy).
152 if (!$this->groupBy
) {
153 $this->select
= array_merge(['id'], $this->select
);
156 // Expand wildcards in joins (the api wrapper already expanded non-joined wildcards)
157 $wildFields = array_filter($this->select
, function($item) {
158 return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE;
160 foreach ($wildFields as $item) {
161 $pos = array_search($item, array_values($this->select
));
162 $this->joinFK($item);
163 $matches = SelectUtil
::getMatchingFields($item, array_keys($this->apiFieldSpec
));
164 array_splice($this->select
, $pos, 1, $matches);
166 $this->select
= array_unique($this->select
);
168 foreach ($this->select
as $fieldName) {
169 $field = $this->getField($fieldName);
170 // Remove unknown fields without raising an error
172 $this->select
= array_diff($this->select
, [$fieldName]);
173 if (is_array($this->debugOutput
)) {
174 $this->debugOutput
['undefined_fields'][] = $fieldName;
177 elseif ($field['is_many']) {
181 $this->query
->select($field['sql_name'] . " AS `$fieldName`");
189 protected function buildWhereClause() {
190 foreach ($this->where
as $clause) {
191 $sql_clause = $this->treeWalkWhereClause($clause);
192 $this->query
->where($sql_clause);
199 protected function buildOrderBy() {
200 foreach ($this->orderBy
as $fieldName => $dir) {
201 if ($dir !== 'ASC' && $dir !== 'DESC') {
202 throw new \
API_Exception("Invalid sort direction. Cannot order by $fieldName $dir");
204 $this->query
->orderBy($this->getField($fieldName, TRUE)['sql_name'] . " $dir");
209 * @throws \CRM_Core_Exception
211 protected function buildLimit() {
212 if (!empty($this->limit
) ||
!empty($this->offset
)) {
213 $this->query
->limit($this->limit
, $this->offset
);
220 protected function buildGroupBy() {
221 foreach ($this->groupBy
as $field) {
222 if ($this->isOneToOneField($field) && $this->getField($field)) {
223 $this->query
->groupBy($field['sql_name']);
226 throw new \
API_Exception("Invalid field. Cannot group by $field");
232 * Recursively validate and transform a branch or leaf clause array to SQL.
234 * @param array $clause
235 * @return string SQL where clause
237 * @uses validateClauseAndComposeSql() to generate the SQL etc.
238 * @todo if an 'and' is nested within and 'and' (or or-in-or) then should
239 * flatten that to be a single list of clauses.
241 protected function treeWalkWhereClause($clause) {
242 switch ($clause[0]) {
246 if (count($clause[1]) === 1) {
247 // a single set so AND|OR is immaterial
248 return $this->treeWalkWhereClause($clause[1][0]);
251 $sql_subclauses = [];
252 foreach ($clause[1] as $subclause) {
253 $sql_subclauses[] = $this->treeWalkWhereClause($subclause);
255 return '(' . implode("\n" . $clause[0], $sql_subclauses) . ')';
259 // If we get a group of clauses with no operator, assume AND
260 if (!is_string($clause[1][0])) {
261 $clause[1] = ['AND', $clause[1]];
263 return 'NOT (' . $this->treeWalkWhereClause($clause[1]) . ')';
266 return $this->validateClauseAndComposeSql($clause);
271 * Validate and transform a leaf clause array to SQL.
272 * @param array $clause [$fieldName, $operator, $criteria]
274 * @throws \API_Exception
277 protected function validateClauseAndComposeSql($clause) {
278 // Pad array for unary operators
279 list($fieldName, $operator, $value) = array_pad($clause, 3, NULL);
280 $field = $this->getField($fieldName, TRUE);
282 FormattingUtil
::formatInputValue($value, $field, $this->getEntity());
284 $sql_clause = \CRM_Core_DAO
::createSQLFilter($field['sql_name'], [$operator => $value]);
285 if ($sql_clause === NULL) {
286 throw new \
API_Exception("Invalid value in where clause for field '$fieldName'");
294 protected function getFields() {
295 return $this->apiFieldSpec
;
299 * Fetch a field from the getFields list
301 * @param string $fieldName
302 * @param bool $strict
304 * @return string|null
305 * @throws \API_Exception
307 public function getField($fieldName, $strict = FALSE) {
308 // Perform join if field not yet available - this will add it to apiFieldSpec
309 if (!isset($this->apiFieldSpec
[$fieldName]) && strpos($fieldName, '.')) {
310 $this->joinFK($fieldName);
312 $field = $this->apiFieldSpec
[$fieldName] ??
NULL;
317 throw new \
API_Exception("Invalid field '$fieldName'");
323 * Joins a path and adds all fields in the joined eneity to apiFieldSpec
326 * @throws \API_Exception
329 protected function joinFK($key) {
330 if (isset($this->apiFieldSpec
[$key])) {
334 $pathArray = explode('.', $key);
336 /** @var \Civi\Api4\Service\Schema\Joiner $joiner */
337 $joiner = \Civi
::container()->get('joiner');
338 // 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.
339 array_pop($pathArray);
340 $pathString = implode('.', $pathArray);
342 if (!$joiner->canJoin($this, $pathString)) {
346 $joinPath = $joiner->join($this, $pathString);
349 foreach ($joinPath as $joinable) {
350 if ($joinable->getJoinType() === Joinable
::JOIN_TYPE_ONE_TO_MANY
) {
355 /** @var \Civi\Api4\Service\Schema\Joinable\Joinable $lastLink */
356 $lastLink = array_pop($joinPath);
358 // Custom field names are already prefixed
359 $isCustom = $lastLink instanceof CustomGroupJoinable
;
361 array_pop($pathArray);
363 $prefix = $pathArray ?
implode('.', $pathArray) . '.' : '';
364 // Cache field info for retrieval by $this->getField()
365 $joinEntity = $lastLink->getEntity();
366 foreach ($lastLink->getEntityFields() as $fieldObject) {
367 $fieldArray = ['entity' => $joinEntity] +
$fieldObject->toArray();
368 $fieldArray['sql_name'] = '`' . $lastLink->getAlias() . '`.`' . $fieldArray['column_name'] . '`';
369 $fieldArray['is_custom'] = $isCustom;
370 $fieldArray['is_join'] = TRUE;
371 $fieldArray['is_many'] = $isMany;
372 $this->addSpecField($prefix . $fieldArray['name'], $fieldArray);
377 * @param \Civi\Api4\Service\Schema\Joinable\Joinable $joinable
381 public function addJoinedTable(Joinable
$joinable) {
382 $this->joinedTables
[] = $joinable;
388 * @return FALSE|string
390 public function getFrom() {
391 return AllCoreTables
::getTableForClass(AllCoreTables
::getFullName($this->entity
));
397 public function getEntity() {
398 return $this->entity
;
404 public function getSelect() {
405 return $this->select
;
411 public function getWhere() {
418 public function getOrderBy() {
419 return $this->orderBy
;
425 public function getLimit() {
432 public function getOffset() {
433 return $this->offset
;
439 public function getSelectFields() {
440 return $this->selectFields
;
446 public function isFillUniqueFields() {
447 return $this->isFillUniqueFields
;
451 * @return \CRM_Utils_SQL_Select
453 public function getQuery() {
460 public function getJoins() {
467 public function getApiFieldSpec() {
468 return $this->apiFieldSpec
;
474 public function getEntityFieldNames() {
475 return $this->entityFieldNames
;
481 public function getAclFields() {
482 return $this->aclFields
;
486 * @return bool|string
488 public function getCheckPermissions() {
489 return $this->checkPermissions
;
495 public function getApiVersion() {
496 return $this->apiVersion
;
500 * @return \Civi\Api4\Service\Schema\Joinable\Joinable[]
502 public function getJoinedTables() {
503 return $this->joinedTables
;
507 * @return \Civi\Api4\Service\Schema\Joinable\Joinable
509 public function getJoinedTable($alias) {
510 foreach ($this->joinedTables
as $join) {
511 if ($join->getAlias() == $alias) {
518 * Get table name on basis of entity
520 * @param string $baoName
524 public function constructQueryObject($baoName) {
525 if (strstr($this->entity
, 'Custom_')) {
526 $this->query
= \CRM_Utils_SQL_Select
::from(CoreUtil
::getCustomTableByName(str_replace('Custom_', '', $this->entity
)) . ' ' . self
::MAIN_TABLE_ALIAS
);
527 $this->entityFieldNames
= array_keys($this->apiFieldSpec
);
530 $bao = new $baoName();
531 $this->query
= \CRM_Utils_SQL_Select
::from($bao->tableName() . ' ' . self
::MAIN_TABLE_ALIAS
);
536 * Checks if a field either belongs to the main entity or is joinable 1-to-1.
538 * Used to determine if a field can be added to the SELECT of the main query,
539 * or if it must be fetched post-query.
541 * @param string $fieldPath
544 public function isOneToOneField(string $fieldPath) {
545 return strpos($fieldPath, '.') === FALSE ||
!array_filter($this->getPathJoinTypes($fieldPath));
549 * Separates a string like 'emails.location_type.label' into an array, where
550 * each value in the array tells whether it is 1-1 or 1-n join type
552 * @param string $pathString
553 * Dot separated path to the field
556 * Index is table alias and value is boolean whether is 1-to-many join
558 public function getPathJoinTypes($pathString) {
559 $pathParts = explode('.', $pathString);
561 array_pop($pathParts);
564 $isMultipleChecker = function($alias) use ($query) {
565 foreach ($query->getJoinedTables() as $table) {
566 if ($table->getAlias() === $alias) {
567 return $table->getJoinType() === Joinable
::JOIN_TYPE_ONE_TO_MANY
;
573 foreach ($pathParts as $part) {
574 $path[$part] = $isMultipleChecker($part);
584 private function addSpecField($path, $field) {
585 // Only add field to spec if we have permission
586 if ($this->checkPermissions
&& !empty($field['permission']) && !\CRM_Core_Permission
::check($field['permission'])) {
587 $this->apiFieldSpec
[$path] = FALSE;
591 $defaults['is_custom'] = $defaults['is_join'] = $defaults['is_many'] = FALSE;
593 $this->apiFieldSpec
[$path] = $field;