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;
64 * @param \Civi\Api4\Generic\DAOGetAction $apiGet
66 public function __construct($apiGet) {
67 $this->entity
= $apiGet->getEntityName();
68 $this->checkPermissions
= $apiGet->getCheckPermissions();
69 $this->select
= $apiGet->getSelect();
70 $this->where
= $apiGet->getWhere();
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 $this->apiFieldSpec
= $apiGet->entityFields();
80 foreach ($this->apiFieldSpec
as $key => $field) {
81 $this->apiFieldSpec
[$key]['sql_name'] = '`' . self
::MAIN_TABLE_ALIAS
. '`.`' . $field['column_name'] . '`';
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 return $this->query
->toSQL();
107 * Why walk when you can
111 public function run() {
113 $sql = $this->getSql();
114 if (is_array($this->debugOutput
)) {
115 $this->debugOutput
['sql'][] = $sql;
117 $query = \CRM_Core_DAO
::executeQuery($sql);
119 while ($query->fetch()) {
120 if (in_array('row_count', $this->select
)) {
121 $results[]['row_count'] = (int) $query->c
;
124 $results[$query->id
] = [];
125 foreach ($this->select
as $alias) {
126 $returnName = $alias;
127 if ($this->isOneToOneField($alias)) {
128 $alias = str_replace('.', '_', $alias);
129 $results[$query->id
][$returnName] = property_exists($query, $alias) ?
$query->$alias : NULL;
133 $event = new PostSelectQueryEvent($results, $this);
134 \Civi
::dispatcher()->dispatch(Events
::POST_SELECT_QUERY
, $event);
136 return $event->getResults();
139 protected function buildSelectClause() {
140 if (empty($this->select
)) {
141 $this->select
= $this->entityFieldNames
;
143 elseif (in_array('row_count', $this->select
)) {
144 $this->query
->select("COUNT(*) AS `c`");
148 // Always select id field
149 $this->select
= array_merge(['id'], $this->select
);
151 // Expand wildcards in joins (the api wrapper already expanded non-joined wildcards)
152 $wildFields = array_filter($this->select
, function($item) {
153 return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE;
155 foreach ($wildFields as $item) {
156 $pos = array_search($item, array_values($this->select
));
157 $this->joinFK($item);
158 $matches = SelectUtil
::getMatchingFields($item, array_keys($this->apiFieldSpec
));
159 array_splice($this->select
, $pos, 1, $matches);
161 $this->select
= array_unique($this->select
);
163 foreach ($this->select
as $fieldName) {
164 $field = $this->getField($fieldName);
165 if (!$this->isOneToOneField($fieldName)) {
169 $this->query
->select($field['sql_name'] . " AS `$fieldName`");
171 // Remove unknown fields without raising an error
173 $this->select
= array_diff($this->select
, [$fieldName]);
174 if (is_array($this->debugOutput
)) {
175 $this->debugOutput
['undefined_fields'][] = $fieldName;
184 protected function buildWhereClause() {
185 foreach ($this->where
as $clause) {
186 $sql_clause = $this->treeWalkWhereClause($clause);
187 $this->query
->where($sql_clause);
194 protected function buildOrderBy() {
195 foreach ($this->orderBy
as $fieldName => $dir) {
196 if ($dir !== 'ASC' && $dir !== 'DESC') {
197 throw new \
API_Exception("Invalid sort direction. Cannot order by $fieldName $dir");
199 $this->query
->orderBy($this->getField($fieldName, TRUE)['sql_name'] . " $dir");
204 * @throws \CRM_Core_Exception
206 protected function buildLimit() {
207 if (!empty($this->limit
) ||
!empty($this->offset
)) {
208 $this->query
->limit($this->limit
, $this->offset
);
213 * Recursively validate and transform a branch or leaf clause array to SQL.
215 * @param array $clause
216 * @return string SQL where clause
218 * @uses validateClauseAndComposeSql() to generate the SQL etc.
219 * @todo if an 'and' is nested within and 'and' (or or-in-or) then should
220 * flatten that to be a single list of clauses.
222 protected function treeWalkWhereClause($clause) {
223 switch ($clause[0]) {
227 if (count($clause[1]) === 1) {
228 // a single set so AND|OR is immaterial
229 return $this->treeWalkWhereClause($clause[1][0]);
232 $sql_subclauses = [];
233 foreach ($clause[1] as $subclause) {
234 $sql_subclauses[] = $this->treeWalkWhereClause($subclause);
236 return '(' . implode("\n" . $clause[0], $sql_subclauses) . ')';
240 // If we get a group of clauses with no operator, assume AND
241 if (!is_string($clause[1][0])) {
242 $clause[1] = ['AND', $clause[1]];
244 return 'NOT (' . $this->treeWalkWhereClause($clause[1]) . ')';
247 return $this->validateClauseAndComposeSql($clause);
252 * Validate and transform a leaf clause array to SQL.
253 * @param array $clause [$fieldName, $operator, $criteria]
255 * @throws \API_Exception
258 protected function validateClauseAndComposeSql($clause) {
259 // Pad array for unary operators
260 list($fieldName, $operator, $value) = array_pad($clause, 3, NULL);
261 $field = $this->getField($fieldName, TRUE);
263 FormattingUtil
::formatInputValue($value, $field, $this->getEntity());
265 $sql_clause = \CRM_Core_DAO
::createSQLFilter($field['sql_name'], [$operator => $value]);
266 if ($sql_clause === NULL) {
267 throw new \
API_Exception("Invalid value in where clause for field '$fieldName'");
275 protected function getFields() {
276 return $this->apiFieldSpec
;
280 * Fetch a field from the getFields list
282 * @param string $fieldName
283 * @param bool $strict
285 * @return string|null
286 * @throws \API_Exception
288 public function getField($fieldName, $strict = FALSE) {
289 // Perform join if field not yet available - this will add it to apiFieldSpec
290 if (!isset($this->apiFieldSpec
[$fieldName]) && strpos($fieldName, '.')) {
291 $this->joinFK($fieldName);
293 $field = $this->apiFieldSpec
[$fieldName] ??
NULL;
294 // Check if field exists and we have permission to view it
295 if ($field && (!$this->checkPermissions ||
empty($field['permission']) || \CRM_Core_Permission
::check($field['permission']))) {
299 throw new \
API_Exception("Invalid field '$fieldName'");
305 * Joins a path and adds all fields in the joined eneity to apiFieldSpec
309 * @throws \API_Exception
312 protected function joinFK($key) {
313 if (isset($this->apiFieldSpec
[$key])) {
317 $pathArray = explode('.', $key);
319 /** @var \Civi\Api4\Service\Schema\Joiner $joiner */
320 $joiner = \Civi
::container()->get('joiner');
321 // 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.
322 array_pop($pathArray);
323 $pathString = implode('.', $pathArray);
325 if (!$joiner->canJoin($this, $pathString)) {
329 $joinPath = $joiner->join($this, $pathString);
330 /** @var \Civi\Api4\Service\Schema\Joinable\Joinable $lastLink */
331 $lastLink = array_pop($joinPath);
333 // Custom field names are already prefixed
334 if ($lastLink instanceof CustomGroupJoinable
) {
335 array_pop($pathArray);
337 $prefix = $pathArray ?
implode('.', $pathArray) . '.' : '';
338 // Cache field info for retrieval by $this->getField()
339 $joinEntity = $lastLink->getEntity();
340 foreach ($lastLink->getEntityFields() as $fieldObject) {
341 $fieldArray = ['entity' => $joinEntity] +
$fieldObject->toArray();
342 $fieldArray['sql_name'] = '`' . $lastLink->getAlias() . '`.`' . $fieldArray['column_name'] . '`';
343 $this->apiFieldSpec
[$prefix . $fieldArray['name']] = $fieldArray;
350 * @param \Civi\Api4\Service\Schema\Joinable\Joinable $joinable
354 public function addJoinedTable(Joinable
$joinable) {
355 $this->joinedTables
[] = $joinable;
361 * @return FALSE|string
363 public function getFrom() {
364 return AllCoreTables
::getTableForClass(AllCoreTables
::getFullName($this->entity
));
370 public function getEntity() {
371 return $this->entity
;
377 public function getSelect() {
378 return $this->select
;
384 public function getWhere() {
391 public function getOrderBy() {
392 return $this->orderBy
;
398 public function getLimit() {
405 public function getOffset() {
406 return $this->offset
;
412 public function getSelectFields() {
413 return $this->selectFields
;
419 public function isFillUniqueFields() {
420 return $this->isFillUniqueFields
;
424 * @return \CRM_Utils_SQL_Select
426 public function getQuery() {
433 public function getJoins() {
440 public function getApiFieldSpec() {
441 return $this->apiFieldSpec
;
447 public function getEntityFieldNames() {
448 return $this->entityFieldNames
;
454 public function getAclFields() {
455 return $this->aclFields
;
459 * @return bool|string
461 public function getCheckPermissions() {
462 return $this->checkPermissions
;
468 public function getApiVersion() {
469 return $this->apiVersion
;
473 * @return \Civi\Api4\Service\Schema\Joinable\Joinable[]
475 public function getJoinedTables() {
476 return $this->joinedTables
;
480 * @return \Civi\Api4\Service\Schema\Joinable\Joinable
482 public function getJoinedTable($alias) {
483 foreach ($this->joinedTables
as $join) {
484 if ($join->getAlias() == $alias) {
491 * Get table name on basis of entity
493 * @param string $baoName
497 public function constructQueryObject($baoName) {
498 if (strstr($this->entity
, 'Custom_')) {
499 $this->query
= \CRM_Utils_SQL_Select
::from(CoreUtil
::getCustomTableByName(str_replace('Custom_', '', $this->entity
)) . ' ' . self
::MAIN_TABLE_ALIAS
);
500 $this->entityFieldNames
= array_keys($this->apiFieldSpec
);
503 $bao = new $baoName();
504 $this->query
= \CRM_Utils_SQL_Select
::from($bao->tableName() . ' ' . self
::MAIN_TABLE_ALIAS
);
509 * Checks if a field either belongs to the main entity or is joinable 1-to-1.
511 * Used to determine if a field can be added to the SELECT of the main query,
512 * or if it must be fetched post-query.
514 * @param string $fieldPath
517 public function isOneToOneField(string $fieldPath) {
518 return strpos($fieldPath, '.') === FALSE ||
!array_filter($this->getPathJoinTypes($fieldPath));
522 * Separates a string like 'emails.location_type.label' into an array, where
523 * each value in the array tells whether it is 1-1 or 1-n join type
525 * @param string $pathString
526 * Dot separated path to the field
529 * Index is table alias and value is boolean whether is 1-to-many join
531 public function getPathJoinTypes($pathString) {
532 $pathParts = explode('.', $pathString);
534 array_pop($pathParts);
537 $isMultipleChecker = function($alias) use ($query) {
538 foreach ($query->getJoinedTables() as $table) {
539 if ($table->getAlias() === $alias) {
540 return $table->getJoinType() === Joinable
::JOIN_TYPE_ONE_TO_MANY
;
546 foreach ($pathParts as $part) {
547 $path[$part] = $isMultipleChecker($part);