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;
46 * Maps select fields to [<table_alias>, <column_alias>]
48 protected $fkSelectAliases = [];
51 * @var \Civi\Api4\Service\Schema\Joinable\Joinable[]
52 * The joinable tables that have been joined so far
54 protected $joinedTables = [];
57 * If set to an array, this will start collecting debug info.
61 public $debugOutput = NULL;
69 * @param \Civi\Api4\Generic\DAOGetAction $apiGet
71 public function __construct($apiGet) {
72 $this->entity
= $apiGet->getEntityName();
73 $this->checkPermissions
= $apiGet->getCheckPermissions();
74 $this->select
= $apiGet->getSelect();
75 $this->where
= $apiGet->getWhere();
76 $this->groupBy
= $apiGet->getGroupBy();
77 $this->orderBy
= $apiGet->getOrderBy();
78 $this->limit
= $apiGet->getLimit();
79 $this->offset
= $apiGet->getOffset();
80 if ($apiGet->getDebug()) {
81 $this->debugOutput
=& $apiGet->_debugOutput
;
83 $baoName = CoreUtil
::getBAOFromApiName($this->entity
);
84 $this->entityFieldNames
= array_column($baoName::fields(), 'name');
85 $this->apiFieldSpec
= $apiGet->entityFields();
86 foreach ($this->apiFieldSpec
as $key => $field) {
87 $this->apiFieldSpec
[$key]['sql_name'] = '`' . self
::MAIN_TABLE_ALIAS
. '`.`' . $field['column_name'] . '`';
90 $this->constructQueryObject($baoName);
92 // Add ACLs first to avoid redundant subclauses
93 $this->query
->where($this->getAclClause(self
::MAIN_TABLE_ALIAS
, $baoName));
97 * Builds final sql statement after all params are set.
100 * @throws \API_Exception
101 * @throws \CRM_Core_Exception
102 * @throws \Civi\API\Exception\UnauthorizedException
104 public function getSql() {
105 $this->buildSelectClause();
106 $this->buildWhereClause();
107 $this->buildOrderBy();
109 $this->buildGroupBy();
110 return $this->query
->toSQL();
114 * Why walk when you can
118 public function run() {
120 $sql = $this->getSql();
121 if (is_array($this->debugOutput
)) {
122 $this->debugOutput
['sql'][] = $sql;
124 $query = \CRM_Core_DAO
::executeQuery($sql);
126 while ($query->fetch()) {
127 $id = $query->id ??
$i++
;
128 if (in_array('row_count', $this->select
)) {
129 $results[]['row_count'] = (int) $query->c
;
133 foreach ($this->select
as $alias) {
134 $returnName = $alias;
135 if ($this->isOneToOneField($alias)) {
136 $alias = str_replace('.', '_', $alias);
137 $results[$id][$returnName] = property_exists($query, $alias) ?
$query->$alias : NULL;
141 $event = new PostSelectQueryEvent($results, $this);
142 \Civi
::dispatcher()->dispatch(Events
::POST_SELECT_QUERY
, $event);
144 return $event->getResults();
147 protected function buildSelectClause() {
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 // Always select ID (unless we're doing groupBy).
157 if (!$this->groupBy
) {
158 $this->select
= array_merge(['id'], $this->select
);
161 // Expand wildcards in joins (the api wrapper already expanded non-joined wildcards)
162 $wildFields = array_filter($this->select
, function($item) {
163 return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE;
165 foreach ($wildFields as $item) {
166 $pos = array_search($item, array_values($this->select
));
167 $this->joinFK($item);
168 $matches = SelectUtil
::getMatchingFields($item, array_keys($this->apiFieldSpec
));
169 array_splice($this->select
, $pos, 1, $matches);
171 $this->select
= array_unique($this->select
);
173 foreach ($this->select
as $fieldName) {
174 $field = $this->getField($fieldName);
175 if (!$this->isOneToOneField($fieldName)) {
179 $this->query
->select($field['sql_name'] . " AS `$fieldName`");
181 // Remove unknown fields without raising an error
183 $this->select
= array_diff($this->select
, [$fieldName]);
184 if (is_array($this->debugOutput
)) {
185 $this->debugOutput
['undefined_fields'][] = $fieldName;
194 protected function buildWhereClause() {
195 foreach ($this->where
as $clause) {
196 $sql_clause = $this->treeWalkWhereClause($clause);
197 $this->query
->where($sql_clause);
204 protected function buildOrderBy() {
205 foreach ($this->orderBy
as $fieldName => $dir) {
206 if ($dir !== 'ASC' && $dir !== 'DESC') {
207 throw new \
API_Exception("Invalid sort direction. Cannot order by $fieldName $dir");
209 $this->query
->orderBy($this->getField($fieldName, TRUE)['sql_name'] . " $dir");
214 * @throws \CRM_Core_Exception
216 protected function buildLimit() {
217 if (!empty($this->limit
) ||
!empty($this->offset
)) {
218 $this->query
->limit($this->limit
, $this->offset
);
225 protected function buildGroupBy() {
226 foreach ($this->groupBy
as $field) {
227 if ($this->isOneToOneField($field) && $this->getField($field)) {
228 $this->query
->groupBy($field['sql_name']);
231 throw new \
API_Exception("Invalid field. Cannot group by $field");
237 * Recursively validate and transform a branch or leaf clause array to SQL.
239 * @param array $clause
240 * @return string SQL where clause
242 * @uses validateClauseAndComposeSql() to generate the SQL etc.
243 * @todo if an 'and' is nested within and 'and' (or or-in-or) then should
244 * flatten that to be a single list of clauses.
246 protected function treeWalkWhereClause($clause) {
247 switch ($clause[0]) {
251 if (count($clause[1]) === 1) {
252 // a single set so AND|OR is immaterial
253 return $this->treeWalkWhereClause($clause[1][0]);
256 $sql_subclauses = [];
257 foreach ($clause[1] as $subclause) {
258 $sql_subclauses[] = $this->treeWalkWhereClause($subclause);
260 return '(' . implode("\n" . $clause[0], $sql_subclauses) . ')';
264 // If we get a group of clauses with no operator, assume AND
265 if (!is_string($clause[1][0])) {
266 $clause[1] = ['AND', $clause[1]];
268 return 'NOT (' . $this->treeWalkWhereClause($clause[1]) . ')';
271 return $this->validateClauseAndComposeSql($clause);
276 * Validate and transform a leaf clause array to SQL.
277 * @param array $clause [$fieldName, $operator, $criteria]
279 * @throws \API_Exception
282 protected function validateClauseAndComposeSql($clause) {
283 // Pad array for unary operators
284 list($fieldName, $operator, $value) = array_pad($clause, 3, NULL);
285 $field = $this->getField($fieldName, TRUE);
287 FormattingUtil
::formatInputValue($value, $field, $this->getEntity());
289 $sql_clause = \CRM_Core_DAO
::createSQLFilter($field['sql_name'], [$operator => $value]);
290 if ($sql_clause === NULL) {
291 throw new \
API_Exception("Invalid value in where clause for field '$fieldName'");
299 protected function getFields() {
300 return $this->apiFieldSpec
;
304 * Fetch a field from the getFields list
306 * @param string $fieldName
307 * @param bool $strict
309 * @return string|null
310 * @throws \API_Exception
312 public function getField($fieldName, $strict = FALSE) {
313 // Perform join if field not yet available - this will add it to apiFieldSpec
314 if (!isset($this->apiFieldSpec
[$fieldName]) && strpos($fieldName, '.')) {
315 $this->joinFK($fieldName);
317 $field = $this->apiFieldSpec
[$fieldName] ??
NULL;
318 // Check if field exists and we have permission to view it
319 if ($field && (!$this->checkPermissions ||
empty($field['permission']) || \CRM_Core_Permission
::check($field['permission']))) {
323 throw new \
API_Exception("Invalid field '$fieldName'");
329 * Joins a path and adds all fields in the joined eneity to apiFieldSpec
333 * @throws \API_Exception
336 protected function joinFK($key) {
337 if (isset($this->apiFieldSpec
[$key])) {
341 $pathArray = explode('.', $key);
343 /** @var \Civi\Api4\Service\Schema\Joiner $joiner */
344 $joiner = \Civi
::container()->get('joiner');
345 // 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.
346 array_pop($pathArray);
347 $pathString = implode('.', $pathArray);
349 if (!$joiner->canJoin($this, $pathString)) {
353 $joinPath = $joiner->join($this, $pathString);
354 /** @var \Civi\Api4\Service\Schema\Joinable\Joinable $lastLink */
355 $lastLink = array_pop($joinPath);
357 // Custom field names are already prefixed
358 if ($lastLink instanceof CustomGroupJoinable
) {
359 array_pop($pathArray);
361 $prefix = $pathArray ?
implode('.', $pathArray) . '.' : '';
362 // Cache field info for retrieval by $this->getField()
363 $joinEntity = $lastLink->getEntity();
364 foreach ($lastLink->getEntityFields() as $fieldObject) {
365 $fieldArray = ['entity' => $joinEntity] +
$fieldObject->toArray();
366 $fieldArray['sql_name'] = '`' . $lastLink->getAlias() . '`.`' . $fieldArray['column_name'] . '`';
367 $this->apiFieldSpec
[$prefix . $fieldArray['name']] = $fieldArray;
374 * @param \Civi\Api4\Service\Schema\Joinable\Joinable $joinable
378 public function addJoinedTable(Joinable
$joinable) {
379 $this->joinedTables
[] = $joinable;
385 * @return FALSE|string
387 public function getFrom() {
388 return AllCoreTables
::getTableForClass(AllCoreTables
::getFullName($this->entity
));
394 public function getEntity() {
395 return $this->entity
;
401 public function getSelect() {
402 return $this->select
;
408 public function getWhere() {
415 public function getOrderBy() {
416 return $this->orderBy
;
422 public function getLimit() {
429 public function getOffset() {
430 return $this->offset
;
436 public function getSelectFields() {
437 return $this->selectFields
;
443 public function isFillUniqueFields() {
444 return $this->isFillUniqueFields
;
448 * @return \CRM_Utils_SQL_Select
450 public function getQuery() {
457 public function getJoins() {
464 public function getApiFieldSpec() {
465 return $this->apiFieldSpec
;
471 public function getEntityFieldNames() {
472 return $this->entityFieldNames
;
478 public function getAclFields() {
479 return $this->aclFields
;
483 * @return bool|string
485 public function getCheckPermissions() {
486 return $this->checkPermissions
;
492 public function getApiVersion() {
493 return $this->apiVersion
;
497 * @return \Civi\Api4\Service\Schema\Joinable\Joinable[]
499 public function getJoinedTables() {
500 return $this->joinedTables
;
504 * @return \Civi\Api4\Service\Schema\Joinable\Joinable
506 public function getJoinedTable($alias) {
507 foreach ($this->joinedTables
as $join) {
508 if ($join->getAlias() == $alias) {
515 * Get table name on basis of entity
517 * @param string $baoName
521 public function constructQueryObject($baoName) {
522 if (strstr($this->entity
, 'Custom_')) {
523 $this->query
= \CRM_Utils_SQL_Select
::from(CoreUtil
::getCustomTableByName(str_replace('Custom_', '', $this->entity
)) . ' ' . self
::MAIN_TABLE_ALIAS
);
524 $this->entityFieldNames
= array_keys($this->apiFieldSpec
);
527 $bao = new $baoName();
528 $this->query
= \CRM_Utils_SQL_Select
::from($bao->tableName() . ' ' . self
::MAIN_TABLE_ALIAS
);
533 * Checks if a field either belongs to the main entity or is joinable 1-to-1.
535 * Used to determine if a field can be added to the SELECT of the main query,
536 * or if it must be fetched post-query.
538 * @param string $fieldPath
541 public function isOneToOneField(string $fieldPath) {
542 return strpos($fieldPath, '.') === FALSE ||
!array_filter($this->getPathJoinTypes($fieldPath));
546 * Separates a string like 'emails.location_type.label' into an array, where
547 * each value in the array tells whether it is 1-1 or 1-n join type
549 * @param string $pathString
550 * Dot separated path to the field
553 * Index is table alias and value is boolean whether is 1-to-many join
555 public function getPathJoinTypes($pathString) {
556 $pathParts = explode('.', $pathString);
558 array_pop($pathParts);
561 $isMultipleChecker = function($alias) use ($query) {
562 foreach ($query->getJoinedTables() as $table) {
563 if ($table->getAlias() === $alias) {
564 return $table->getJoinType() === Joinable
::JOIN_TYPE_ONE_TO_MANY
;
570 foreach ($pathParts as $part) {
571 $path[$part] = $isMultipleChecker($part);