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
;
23 use CRM_Utils_Array
as UtilsArray
;
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;
47 * Maps select fields to [<table_alias>, <column_alias>]
49 protected $fkSelectAliases = [];
52 * @var \Civi\Api4\Service\Schema\Joinable\Joinable[]
53 * The joinable tables that have been joined so far
55 protected $joinedTables = [];
58 * If set to an array, this will start collecting debug info.
62 public $debugOutput = NULL;
65 * @param \Civi\Api4\Generic\DAOGetAction $apiGet
67 public function __construct($apiGet) {
68 $this->entity
= $apiGet->getEntityName();
69 $this->checkPermissions
= $apiGet->getCheckPermissions();
70 $this->select
= $apiGet->getSelect();
71 $this->where
= $apiGet->getWhere();
72 $this->orderBy
= $apiGet->getOrderBy();
73 $this->limit
= $apiGet->getLimit();
74 $this->offset
= $apiGet->getOffset();
75 if ($apiGet->getDebug()) {
76 $this->debugOutput
=& $apiGet->_debugOutput
;
78 $baoName = CoreUtil
::getBAOFromApiName($this->entity
);
79 $this->entityFieldNames
= array_column($baoName::fields(), 'name');
80 $this->apiFieldSpec
= $apiGet->entityFields();
82 $this->constructQueryObject($baoName);
84 // Add ACLs first to avoid redundant subclauses
85 $this->query
->where($this->getAclClause(self
::MAIN_TABLE_ALIAS
, $baoName));
89 * Builds final sql statement after all params are set.
92 * @throws \API_Exception
93 * @throws \CRM_Core_Exception
94 * @throws \Civi\API\Exception\UnauthorizedException
96 public function getSql() {
98 $this->buildSelectFields();
99 $this->buildWhereClause();
102 if (in_array('row_count', $this->select
)) {
103 $this->query
->select("count(*) as c");
106 foreach ($this->selectFields
as $column => $alias) {
107 $this->query
->select("$column as `$alias`");
110 $this->buildOrderBy();
114 if (!empty($this->limit
) ||
!empty($this->offset
)) {
115 $this->query
->limit($this->limit
, $this->offset
);
117 return $this->query
->toSQL();
121 * Why walk when you can
125 public function run() {
127 $sql = $this->getSql();
128 if (is_array($this->debugOutput
)) {
129 $this->debugOutput
['sql'][] = $sql;
131 $query = \CRM_Core_DAO
::executeQuery($sql);
133 while ($query->fetch()) {
134 if (in_array('row_count', $this->select
)) {
135 $results[]['row_count'] = (int) $query->c
;
138 $results[$query->id
] = [];
139 foreach ($this->selectFields
as $column => $alias) {
140 $returnName = $alias;
141 $alias = str_replace('.', '_', $alias);
142 $results[$query->id
][$returnName] = property_exists($query, $alias) ?
$query->$alias : NULL;
145 $event = new PostSelectQueryEvent($results, $this);
146 \Civi
::dispatcher()->dispatch(Events
::POST_SELECT_QUERY
, $event);
148 return $event->getResults();
152 * Gets all FK fields and does the required joins
154 protected function addJoins() {
155 $allFields = array_merge($this->select
, array_keys($this->orderBy
));
156 $recurse = function($clauses) use (&$allFields, &$recurse) {
157 foreach ($clauses as $clause) {
158 if ($clause[0] === 'NOT' && is_string($clause[1][0])) {
159 $recurse($clause[1][1]);
161 elseif (in_array($clause[0], ['AND', 'OR', 'NOT'])) {
162 $recurse($clause[1]);
164 elseif (is_array($clause[0])) {
165 array_walk($clause, $recurse);
168 $allFields[] = $clause[0];
172 $recurse($this->where
);
173 $dotFields = array_unique(array_filter($allFields, function ($field) {
174 return strpos($field, '.') !== FALSE;
177 foreach ($dotFields as $dotField) {
178 $this->joinFK($dotField);
183 * Populate $this->selectFields
185 * @throws \Civi\API\Exception\UnauthorizedException
187 protected function buildSelectFields() {
188 $selectAll = (empty($this->select
) ||
in_array('*', $this->select
));
189 $select = $selectAll ?
$this->entityFieldNames
: $this->select
;
191 // Always select the ID if the table has one.
192 if (array_key_exists('id', $this->apiFieldSpec
) ||
strstr($this->entity
, 'Custom_')) {
193 $this->selectFields
[self
::MAIN_TABLE_ALIAS
. ".id"] = "id";
196 // core return fields
197 foreach ($select as $fieldName) {
198 $field = $this->getField($fieldName);
199 if (strpos($fieldName, '.') && !empty($this->fkSelectAliases
[$fieldName]) && !array_filter($this->getPathJoinTypes($fieldName))) {
200 $this->selectFields
[$this->fkSelectAliases
[$fieldName]] = $fieldName;
202 elseif ($field && in_array($field['name'], $this->entityFieldNames
)) {
203 $this->selectFields
[self
::MAIN_TABLE_ALIAS
. "." . UtilsArray
::value('column_name', $field, $field['name'])] = $field['name'];
211 protected function buildWhereClause() {
212 foreach ($this->where
as $clause) {
213 $sql_clause = $this->treeWalkWhereClause($clause);
214 $this->query
->where($sql_clause);
221 protected function buildOrderBy() {
222 foreach ($this->orderBy
as $field => $dir) {
223 if ($dir !== 'ASC' && $dir !== 'DESC') {
224 throw new \
API_Exception("Invalid sort direction. Cannot order by $field $dir");
226 if ($this->getField($field)) {
227 $this->query
->orderBy(self
::MAIN_TABLE_ALIAS
. '.' . $field . " $dir");
230 throw new \
API_Exception("Invalid sort field. Cannot order by $field $dir");
236 * Recursively validate and transform a branch or leaf clause array to SQL.
238 * @param array $clause
239 * @return string SQL where clause
241 * @uses validateClauseAndComposeSql() to generate the SQL etc.
242 * @todo if an 'and' is nested within and 'and' (or or-in-or) then should
243 * flatten that to be a single list of clauses.
245 protected function treeWalkWhereClause($clause) {
246 switch ($clause[0]) {
250 if (count($clause[1]) === 1) {
251 // a single set so AND|OR is immaterial
252 return $this->treeWalkWhereClause($clause[1][0]);
255 $sql_subclauses = [];
256 foreach ($clause[1] as $subclause) {
257 $sql_subclauses[] = $this->treeWalkWhereClause($subclause);
259 return '(' . implode("\n" . $clause[0], $sql_subclauses) . ')';
263 // If we get a group of clauses with no operator, assume AND
264 if (!is_string($clause[1][0])) {
265 $clause[1] = ['AND', $clause[1]];
267 return 'NOT (' . $this->treeWalkWhereClause($clause[1]) . ')';
270 return $this->validateClauseAndComposeSql($clause);
275 * Validate and transform a leaf clause array to SQL.
276 * @param array $clause [$fieldName, $operator, $criteria]
278 * @throws \API_Exception
281 protected function validateClauseAndComposeSql($clause) {
282 // Pad array for unary operators
283 list($key, $operator, $value) = array_pad($clause, 3, NULL);
284 $fieldSpec = $this->getField($key);
285 // derive table and column:
288 if (in_array($key, $this->entityFieldNames
)) {
289 $table_name = self
::MAIN_TABLE_ALIAS
;
292 elseif (strpos($key, '.') && isset($this->fkSelectAliases
[$key])) {
293 list($table_name, $column_name) = explode('.', $this->fkSelectAliases
[$key]);
296 if (!$table_name ||
!$column_name) {
297 throw new \
API_Exception("Invalid field '$key' in where clause.");
300 FormattingUtil
::formatInputValue($value, $fieldSpec, $this->getEntity());
302 $sql_clause = \CRM_Core_DAO
::createSQLFilter("`$table_name`.`$column_name`", [$operator => $value]);
303 if ($sql_clause === NULL) {
304 throw new \
API_Exception("Invalid value in where clause for field '$key'");
312 protected function getFields() {
313 return $this->apiFieldSpec
;
317 * Fetch a field from the getFields list
319 * @param string $fieldName
321 * @return string|null
323 protected function getField($fieldName) {
325 $fieldPath = explode('.', $fieldName);
326 if (count($fieldPath) > 1) {
327 $fieldName = implode('.', array_slice($fieldPath, -2));
329 return UtilsArray
::value($fieldName, $this->apiFieldSpec
);
336 * @throws \API_Exception
338 protected function joinFK($key) {
339 $pathArray = explode('.', $key);
341 if (count($pathArray) < 2) {
345 /** @var \Civi\Api4\Service\Schema\Joiner $joiner */
346 $joiner = \Civi
::container()->get('joiner');
347 $field = array_pop($pathArray);
348 $pathString = implode('.', $pathArray);
350 if (!$joiner->canJoin($this, $pathString)) {
354 $joinPath = $joiner->join($this, $pathString);
355 /** @var \Civi\Api4\Service\Schema\Joinable\Joinable $lastLink */
356 $lastLink = array_pop($joinPath);
358 $isWild = strpos($field, '*') !== FALSE;
360 if (!in_array($key, $this->select
)) {
361 throw new \
API_Exception('Wildcards can only be used in the SELECT clause.');
363 $this->select
= array_diff($this->select
, [$key]);
366 // Cache field info for retrieval by $this->getField()
367 $prefix = array_pop($pathArray) . '.';
368 if (!isset($this->apiFieldSpec
[$prefix . $field])) {
369 $joinEntity = $lastLink->getEntity();
370 // Custom fields are already prefixed
371 if ($lastLink instanceof CustomGroupJoinable
) {
374 foreach ($lastLink->getEntityFields() as $fieldObject) {
375 $this->apiFieldSpec
[$prefix . $fieldObject->getName()] = $fieldObject->toArray() +
['entity' => $joinEntity];
379 if (!$isWild && !$lastLink->getField($field)) {
380 throw new \
API_Exception('Invalid join');
383 $fields = $isWild ?
[] : [$field];
384 // Expand wildcard and add matching fields to $this->select
386 $fields = SelectUtil
::getMatchingFields($field, $lastLink->getEntityFieldNames());
387 foreach ($fields as $field) {
388 $this->select
[] = $pathString . '.' . $field;
390 $this->select
= array_unique($this->select
);
393 foreach ($fields as $field) {
394 // custom groups use aliases for field names
395 $col = ($lastLink instanceof CustomGroupJoinable
) ?
$lastLink->getSqlColumn($field) : $field;
396 // Check Permission on field.
397 if ($this->checkPermissions
&& !empty($this->apiFieldSpec
[$prefix . $field]['permission']) && !\CRM_Core_Permission
::check($this->apiFieldSpec
[$prefix . $field]['permission'])) {
400 $this->fkSelectAliases
[$pathString . '.' . $field] = sprintf('%s.%s', $lastLink->getAlias(), $col);
405 * @param \Civi\Api4\Service\Schema\Joinable\Joinable $joinable
409 public function addJoinedTable(Joinable
$joinable) {
410 $this->joinedTables
[] = $joinable;
416 * @return FALSE|string
418 public function getFrom() {
419 return AllCoreTables
::getTableForClass(AllCoreTables
::getFullName($this->entity
));
425 public function getEntity() {
426 return $this->entity
;
432 public function getSelect() {
433 return $this->select
;
439 public function getWhere() {
446 public function getOrderBy() {
447 return $this->orderBy
;
453 public function getLimit() {
460 public function getOffset() {
461 return $this->offset
;
467 public function getSelectFields() {
468 return $this->selectFields
;
474 public function isFillUniqueFields() {
475 return $this->isFillUniqueFields
;
479 * @return \CRM_Utils_SQL_Select
481 public function getQuery() {
488 public function getJoins() {
495 public function getApiFieldSpec() {
496 return $this->apiFieldSpec
;
502 public function getEntityFieldNames() {
503 return $this->entityFieldNames
;
509 public function getAclFields() {
510 return $this->aclFields
;
514 * @return bool|string
516 public function getCheckPermissions() {
517 return $this->checkPermissions
;
523 public function getApiVersion() {
524 return $this->apiVersion
;
530 public function getFkSelectAliases() {
531 return $this->fkSelectAliases
;
535 * @return \Civi\Api4\Service\Schema\Joinable\Joinable[]
537 public function getJoinedTables() {
538 return $this->joinedTables
;
542 * @return \Civi\Api4\Service\Schema\Joinable\Joinable
544 public function getJoinedTable($alias) {
545 foreach ($this->joinedTables
as $join) {
546 if ($join->getAlias() == $alias) {
553 * Get table name on basis of entity
555 * @param string $baoName
559 public function constructQueryObject($baoName) {
560 if (strstr($this->entity
, 'Custom_')) {
561 $this->query
= \CRM_Utils_SQL_Select
::from(CoreUtil
::getCustomTableByName(str_replace('Custom_', '', $this->entity
)) . ' ' . self
::MAIN_TABLE_ALIAS
);
562 $this->entityFieldNames
= array_keys($this->apiFieldSpec
);
565 $bao = new $baoName();
566 $this->query
= \CRM_Utils_SQL_Select
::from($bao->tableName() . ' ' . self
::MAIN_TABLE_ALIAS
);
571 * Separates a string like 'emails.location_type.label' into an array, where
572 * each value in the array tells whether it is 1-1 or 1-n join type
574 * @param string $pathString
575 * Dot separated path to the field
578 * Index is table alias and value is boolean whether is 1-to-many join
580 public function getPathJoinTypes($pathString) {
581 $pathParts = explode('.', $pathString);
583 array_pop($pathParts);
586 $isMultipleChecker = function($alias) use ($query) {
587 foreach ($query->getJoinedTables() as $table) {
588 if ($table->getAlias() === $alias) {
589 return $table->getJoinType() === Joinable
::JOIN_TYPE_ONE_TO_MANY
;
595 foreach ($pathParts as $part) {
596 $path[$part] = $isMultipleChecker($part);