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 CRM_Core_DAO_AllCoreTables
as AllCoreTables
;
22 use CRM_Utils_Array
as UtilsArray
;
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 * @param string $entity
58 * @param bool $checkPermissions
59 * @param array $fields
61 public function __construct($entity, $checkPermissions, $fields) {
62 require_once 'api/v3/utils.php';
63 $this->entity
= $entity;
64 $this->checkPermissions
= $checkPermissions;
66 $baoName = CoreUtil
::getBAOFromApiName($entity);
67 $bao = new $baoName();
69 $this->entityFieldNames
= _civicrm_api3_field_names(_civicrm_api3_build_fields_array($bao));
70 $this->apiFieldSpec
= (array) $fields;
72 \CRM_Utils_SQL_Select
::from($this->getTableName($baoName) . ' ' . self
::MAIN_TABLE_ALIAS
);
74 // Add ACLs first to avoid redundant subclauses
75 $this->query
->where($this->getAclClause(self
::MAIN_TABLE_ALIAS
, $baoName));
79 * Why walk when you can
83 public function run() {
85 $this->buildSelectFields();
86 $this->buildWhereClause();
89 if (in_array('row_count', $this->select
)) {
90 $this->query
->select("count(*) as c");
93 foreach ($this->selectFields
as $column => $alias) {
94 $this->query
->select("$column as `$alias`");
97 $this->buildOrderBy();
101 if (!empty($this->limit
) ||
!empty($this->offset
)) {
102 $this->query
->limit($this->limit
, $this->offset
);
106 $sql = $this->query
->toSQL();
107 $query = \CRM_Core_DAO
::executeQuery($sql);
109 while ($query->fetch()) {
110 if (in_array('row_count', $this->select
)) {
111 $results[]['row_count'] = (int) $query->c
;
114 $results[$query->id
] = [];
115 foreach ($this->selectFields
as $column => $alias) {
116 $returnName = $alias;
117 $alias = str_replace('.', '_', $alias);
118 $results[$query->id
][$returnName] = property_exists($query, $alias) ?
$query->$alias : NULL;
121 $event = new PostSelectQueryEvent($results, $this);
122 \Civi
::dispatcher()->dispatch(Events
::POST_SELECT_QUERY
, $event);
124 return $event->getResults();
128 * Gets all FK fields and does the required joins
130 protected function addJoins() {
131 $allFields = array_merge($this->select
, array_keys($this->orderBy
));
132 $recurse = function($clauses) use (&$allFields, &$recurse) {
133 foreach ($clauses as $clause) {
134 if ($clause[0] === 'NOT' && is_string($clause[1][0])) {
135 $recurse($clause[1][1]);
137 elseif (in_array($clause[0], ['AND', 'OR', 'NOT'])) {
138 $recurse($clause[1]);
140 elseif (is_array($clause[0])) {
141 array_walk($clause, $recurse);
144 $allFields[] = $clause[0];
148 $recurse($this->where
);
149 $dotFields = array_unique(array_filter($allFields, function ($field) {
150 return strpos($field, '.') !== FALSE;
153 foreach ($dotFields as $dotField) {
154 $this->joinFK($dotField);
159 * Populate $this->selectFields
161 * @throws \Civi\API\Exception\UnauthorizedException
163 protected function buildSelectFields() {
164 $return_all_fields = (empty($this->select
) ||
!is_array($this->select
));
165 $return = $return_all_fields ?
$this->entityFieldNames
: $this->select
;
166 if ($return_all_fields ||
in_array('custom', $this->select
)) {
167 foreach (array_keys($this->apiFieldSpec
) as $fieldName) {
168 if (strpos($fieldName, 'custom_') === 0) {
169 $return[] = $fieldName;
174 // Always select the ID if the table has one.
175 if (array_key_exists('id', $this->apiFieldSpec
) ||
strstr($this->entity
, 'Custom_')) {
176 $this->selectFields
[self
::MAIN_TABLE_ALIAS
. ".id"] = "id";
179 // core return fields
180 foreach ($return as $fieldName) {
181 $field = $this->getField($fieldName);
182 if (strpos($fieldName, '.') && !empty($this->fkSelectAliases
[$fieldName]) && !array_filter($this->getPathJoinTypes($fieldName))) {
183 $this->selectFields
[$this->fkSelectAliases
[$fieldName]] = $fieldName;
185 elseif ($field && in_array($field['name'], $this->entityFieldNames
)) {
186 $this->selectFields
[self
::MAIN_TABLE_ALIAS
. "." . UtilsArray
::value('column_name', $field, $field['name'])] = $field['name'];
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 $field => $dir) {
206 if ($dir !== 'ASC' && $dir !== 'DESC') {
207 throw new \
API_Exception("Invalid sort direction. Cannot order by $field $dir");
209 if ($this->getField($field)) {
210 $this->query
->orderBy(self
::MAIN_TABLE_ALIAS
. '.' . $field . " $dir");
213 throw new \
API_Exception("Invalid sort field. Cannot order by $field $dir");
219 * Recursively validate and transform a branch or leaf clause array to SQL.
221 * @param array $clause
222 * @return string SQL where clause
224 * @uses validateClauseAndComposeSql() to generate the SQL etc.
225 * @todo if an 'and' is nested within and 'and' (or or-in-or) then should
226 * flatten that to be a single list of clauses.
228 protected function treeWalkWhereClause($clause) {
229 switch ($clause[0]) {
233 if (count($clause[1]) === 1) {
234 // a single set so AND|OR is immaterial
235 return $this->treeWalkWhereClause($clause[1][0]);
238 $sql_subclauses = [];
239 foreach ($clause[1] as $subclause) {
240 $sql_subclauses[] = $this->treeWalkWhereClause($subclause);
242 return '(' . implode("\n" . $clause[0], $sql_subclauses) . ')';
246 // If we get a group of clauses with no operator, assume AND
247 if (!is_string($clause[1][0])) {
248 $clause[1] = ['AND', $clause[1]];
250 return 'NOT (' . $this->treeWalkWhereClause($clause[1]) . ')';
253 return $this->validateClauseAndComposeSql($clause);
258 * Validate and transform a leaf clause array to SQL.
259 * @param array $clause [$fieldName, $operator, $criteria]
261 * @throws \API_Exception
264 protected function validateClauseAndComposeSql($clause) {
265 // Pad array for unary operators
266 list($key, $operator, $value) = array_pad($clause, 3, NULL);
267 $fieldSpec = $this->getField($key);
268 // derive table and column:
271 if (in_array($key, $this->entityFieldNames
)) {
272 $table_name = self
::MAIN_TABLE_ALIAS
;
275 elseif (strpos($key, '.') && isset($this->fkSelectAliases
[$key])) {
276 list($table_name, $column_name) = explode('.', $this->fkSelectAliases
[$key]);
279 if (!$table_name ||
!$column_name) {
280 throw new \
API_Exception("Invalid field '$key' in where clause.");
283 FormattingUtil
::formatValue($value, $fieldSpec, $this->getEntity());
285 $sql_clause = \CRM_Core_DAO
::createSQLFilter("`$table_name`.`$column_name`", [$operator => $value]);
286 if ($sql_clause === NULL) {
287 throw new \
API_Exception("Invalid value in where clause for field '$key'");
295 protected function getFields() {
296 return $this->apiFieldSpec
;
300 * Fetch a field from the getFields list
302 * @param string $fieldName
304 * @return string|null
306 protected function getField($fieldName) {
308 $fieldPath = explode('.', $fieldName);
309 if (count($fieldPath) > 1) {
310 $fieldName = implode('.', array_slice($fieldPath, -2));
312 return UtilsArray
::value($fieldName, $this->apiFieldSpec
);
319 * @throws \API_Exception
321 protected function joinFK($key) {
322 $pathArray = explode('.', $key);
324 if (count($pathArray) < 2) {
328 /** @var \Civi\Api4\Service\Schema\Joiner $joiner */
329 $joiner = \Civi
::container()->get('joiner');
330 $field = array_pop($pathArray);
331 $pathString = implode('.', $pathArray);
333 if (!$joiner->canJoin($this, $pathString)) {
337 $joinPath = $joiner->join($this, $pathString);
338 /** @var \Civi\Api4\Service\Schema\Joinable\Joinable $lastLink */
339 $lastLink = array_pop($joinPath);
341 // Cache field info for retrieval by $this->getField()
342 $prefix = array_pop($pathArray) . '.';
343 if (!isset($this->apiFieldSpec
[$prefix . $field])) {
344 $joinEntity = $lastLink->getEntity();
345 // Custom fields are already prefixed
346 if ($lastLink instanceof CustomGroupJoinable
) {
349 foreach ($lastLink->getEntityFields() as $fieldObject) {
350 $this->apiFieldSpec
[$prefix . $fieldObject->getName()] = $fieldObject->toArray() +
['entity' => $joinEntity];
354 if (!$lastLink->getField($field)) {
355 throw new \
API_Exception('Invalid join');
358 // custom groups use aliases for field names
359 if ($lastLink instanceof CustomGroupJoinable
) {
360 $field = $lastLink->getSqlColumn($field);
362 // Check Permission on field.
363 if ($this->checkPermissions
&& !empty($this->apiFieldSpec
[$prefix . $field]['permission']) && !\CRM_Core_Permission
::check($this->apiFieldSpec
[$prefix . $field]['permission'])) {
366 $this->fkSelectAliases
[$key] = sprintf('%s.%s', $lastLink->getAlias(), $field);
370 * @param \Civi\Api4\Service\Schema\Joinable\Joinable $joinable
374 public function addJoinedTable(Joinable
$joinable) {
375 $this->joinedTables
[] = $joinable;
381 * @return FALSE|string
383 public function getFrom() {
384 return AllCoreTables
::getTableForClass(AllCoreTables
::getFullName($this->entity
));
390 public function getEntity() {
391 return $this->entity
;
397 public function getSelect() {
398 return $this->select
;
404 public function getWhere() {
411 public function getOrderBy() {
412 return $this->orderBy
;
418 public function getLimit() {
425 public function getOffset() {
426 return $this->offset
;
432 public function getSelectFields() {
433 return $this->selectFields
;
439 public function isFillUniqueFields() {
440 return $this->isFillUniqueFields
;
444 * @return \CRM_Utils_SQL_Select
446 public function getQuery() {
453 public function getJoins() {
460 public function getApiFieldSpec() {
461 return $this->apiFieldSpec
;
467 public function getEntityFieldNames() {
468 return $this->entityFieldNames
;
474 public function getAclFields() {
475 return $this->aclFields
;
479 * @return bool|string
481 public function getCheckPermissions() {
482 return $this->checkPermissions
;
488 public function getApiVersion() {
489 return $this->apiVersion
;
495 public function getFkSelectAliases() {
496 return $this->fkSelectAliases
;
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 getTableName($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 * Separates a string like 'emails.location_type.label' into an array, where
537 * each value in the array tells whether it is 1-1 or 1-n join type
539 * @param string $pathString
540 * Dot separated path to the field
543 * Index is table alias and value is boolean whether is 1-to-many join
545 public function getPathJoinTypes($pathString) {
546 $pathParts = explode('.', $pathString);
548 array_pop($pathParts);
551 $isMultipleChecker = function($alias) use ($query) {
552 foreach ($query->getJoinedTables() as $table) {
553 if ($table->getAlias() === $alias) {
554 return $table->getJoinType() === Joinable
::JOIN_TYPE_ONE_TO_MANY
;
560 foreach ($pathParts as $part) {
561 $path[$part] = $isMultipleChecker($part);