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 +--------------------------------------------------------------------+
13 use Civi\API\Exception\UnauthorizedException
;
14 use CRM_Core_Exception
;
17 * Query builder for civicrm_api_basic_get.
19 * Fetches an entity based on specified params for the "where" clause,
20 * return properties for the "select" clause,
21 * as well as limit and order.
23 * Automatically joins on custom fields to return or filter by them.
25 * Supports an additional sql fragment which the calling api can provide.
29 abstract class SelectQuery
{
33 MAIN_TABLE_ALIAS
= 'a';
47 protected $selectFields = [];
51 public $isFillUniqueFields = FALSE;
53 * @var \CRM_Utils_SQL_Select
59 protected $joins = [];
63 protected $apiFieldSpec;
67 protected $entityFieldNames = [];
71 protected $aclFields = [];
75 protected $checkPermissions;
77 protected $apiVersion;
80 * @param string $entity
81 * @param bool $checkPermissions
83 * @throws \CRM_Core_Exception
85 public function __construct($entity, $checkPermissions) {
86 $this->entity
= $entity;
87 require_once 'api/v3/utils.php';
88 $baoName = _civicrm_api3_get_BAO($entity);
90 throw new CRM_Core_Exception('Invalid entity: ' . \CRM_Utils_Type
::validate($entity, 'Alphanumeric'));
92 $bao = new $baoName();
94 $this->entityFieldNames
= array_column($baoName::fields(), 'name');
95 $this->apiFieldSpec
= $this->getFields();
97 $this->query
= \CRM_Utils_SQL_Select
::from($bao->tableName() . ' ' . self
::MAIN_TABLE_ALIAS
);
99 // Add ACLs first to avoid redundant subclauses
100 $this->checkPermissions
= $checkPermissions;
101 $this->query
->where($this->getAclClause(self
::MAIN_TABLE_ALIAS
, $baoName));
105 * Build & execute the query and return results array
108 * @throws \API_Exception
109 * @throws \CRM_Core_Exception
112 public function run() {
113 $this->buildSelectFields();
115 $this->buildWhereClause();
116 if (in_array('count_rows', $this->select
)) {
117 $this->query
->select("count(*) as c");
120 foreach ($this->selectFields
as $column => $alias) {
121 $this->query
->select("$column as `$alias`");
124 $this->buildOrderBy();
128 if (!empty($this->limit
) ||
!empty($this->offset
)) {
129 $this->query
->limit($this->limit
, $this->offset
);
132 $result_entities = [];
133 $result_dao = \CRM_Core_DAO
::executeQuery($this->query
->toSQL());
135 while ($result_dao->fetch()) {
136 if (in_array('count_rows', $this->select
)) {
137 return (int) $result_dao->c
;
139 $result_entities[$result_dao->id
] = [];
140 foreach ($this->selectFields
as $column => $alias) {
141 $returnName = $alias;
142 $alias = str_replace('.', '_', $alias);
143 if (property_exists($result_dao, $alias) && $result_dao->$alias != NULL) {
144 $result_entities[$result_dao->id
][$returnName] = $result_dao->$alias;
146 // Backward compatibility on fields names.
147 if ($this->isFillUniqueFields
&& !empty($this->apiFieldSpec
[$alias]['uniqueName'])) {
148 $result_entities[$result_dao->id
][$this->apiFieldSpec
[$alias]['uniqueName']] = $result_dao->$alias;
150 foreach ($this->apiFieldSpec
as $returnName => $spec) {
151 if (empty($result_entities[$result_dao->id
][$returnName]) && !empty($result_entities[$result_dao->id
][$spec['name']])) {
152 $result_entities[$result_dao->id
][$returnName] = $result_entities[$result_dao->id
][$spec['name']];
157 return $result_entities;
161 * @param \CRM_Utils_SQL_Select $sqlFragment
162 * @return SelectQuery
164 public function merge($sqlFragment) {
165 $this->query
->merge($sqlFragment);
170 * Joins onto an fk field
172 * Adds one or more joins to the query to make this field available for use in a clause.
174 * Enforces permissions at the api level and by appending the acl clause for that entity to the join.
176 * @param $fkFieldName
180 * Returns the table and field name for adding this field to a SELECT or WHERE clause
181 * @throws \API_Exception
182 * @throws \Civi\API\Exception\UnauthorizedException
184 protected function addFkField($fkFieldName, $side) {
185 $stack = explode('.', $fkFieldName);
186 if (count($stack) < 2) {
189 $prev = self
::MAIN_TABLE_ALIAS
;
190 foreach ($stack as $depth => $fieldName) {
191 // Setup variables then skip the first level
194 // We only join on core fields
195 // @TODO: Custom contact ref fields could be supported too
196 if (!in_array($fk, $this->entityFieldNames
)) {
199 $fkField = &$this->apiFieldSpec
[$fk];
202 // More than 4 joins deep seems excessive - DOS attack?
203 if ($depth > self
::MAX_JOINS
) {
204 throw new UnauthorizedException("Maximum number of joins exceeded in parameter $fkFieldName");
206 $subStack = array_slice($stack, 0, $depth);
207 $this->getJoinInfo($fkField, $subStack);
208 if (!isset($fkField['FKApiName']) ||
!isset($fkField['FKClassName'])) {
209 // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it.
212 // Ensure we have permission to access the other api
213 if (!$this->checkPermissionToJoin($fkField['FKApiName'], $subStack)) {
214 throw new UnauthorizedException("Authorization failed to join onto {$fkField['FKApiName']} api in parameter $fkFieldName");
216 if (!isset($fkField['FKApiSpec'])) {
217 $fkField['FKApiSpec'] = \
_civicrm_api_get_fields($fkField['FKApiName']);
219 $fieldInfo = $fkField['FKApiSpec'][$fieldName] ??
NULL;
221 $keyColumn = \CRM_Utils_Array
::value('FKKeyColumn', $fkField, 'id');
222 if (!$fieldInfo ||
!isset($fkField['FKApiSpec'][$keyColumn])) {
223 // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it.
227 // Skip if we don't have permission to access this field
228 if ($this->checkPermissions
&& !empty($fieldInfo['permission']) && !\CRM_Core_Permission
::check($fieldInfo['permission'])) {
232 $fkTable = \CRM_Core_DAO_AllCoreTables
::getTableForClass($fkField['FKClassName']);
233 $tableAlias = implode('_to_', $subStack) . "_to_$fkTable";
236 $joinCondition = array_merge(
237 ["$prev.$fk = $tableAlias.$keyColumn"],
238 $this->getAclClause($tableAlias, \
_civicrm_api3_get_BAO($fkField['FKApiName']), $subStack)
241 if (!empty($fkField['FKCondition'])) {
242 $joinCondition[] = str_replace($fkTable, $tableAlias, $fkField['FKCondition']);
245 $this->join($side, $fkTable, $tableAlias, $joinCondition);
247 if (strpos($fieldName, 'custom_') === 0) {
248 [$tableAlias, $fieldName] = $this->addCustomField($fieldInfo, $side, $tableAlias);
251 // Get ready to recurse to the next level
253 $fkField = &$fkField['FKApiSpec'][$fieldName];
256 return [$tableAlias, $fieldName];
260 * Get join info for dynamically-joined fields (e.g. "entity_id", "option_group")
265 protected function getJoinInfo(&$fkField, $stack) {
266 if ($fkField['name'] == 'entity_id') {
267 $entityTableParam = substr(implode('.', $stack), 0, -2) . 'table';
268 $entityTable = $this->where
[$entityTableParam] ??
NULL;
269 if ($entityTable && is_string($entityTable) && \CRM_Core_DAO_AllCoreTables
::getClassForTable($entityTable)) {
270 $fkField['FKClassName'] = \CRM_Core_DAO_AllCoreTables
::getClassForTable($entityTable);
271 $fkField['FKApiName'] = \CRM_Core_DAO_AllCoreTables
::getBriefName($fkField['FKClassName']);
274 if (!empty($fkField['pseudoconstant']['optionGroupName'])) {
275 $fkField['FKClassName'] = 'CRM_Core_DAO_OptionValue';
276 $fkField['FKApiName'] = 'OptionValue';
277 $fkField['FKKeyColumn'] = 'value';
278 $fkField['FKCondition'] = "civicrm_option_value.option_group_id = (SELECT id FROM civicrm_option_group WHERE name = '{$fkField['pseudoconstant']['optionGroupName']}')";
283 * Joins onto a custom field
285 * Adds a join to the query to make this field available for use in a clause.
287 * @param array $customField
288 * @param string $side
289 * @param string $baseTable
291 * Returns the table and field name for adding this field to a SELECT or WHERE clause
293 protected function addCustomField($customField, $side, $baseTable = self
::MAIN_TABLE_ALIAS
) {
294 $tableName = $customField["table_name"];
295 $columnName = $customField["column_name"];
296 $tableAlias = "{$baseTable}_to_$tableName";
297 $this->join($side, $tableName, $tableAlias, ["`$tableAlias`.entity_id = `$baseTable`.id"]);
298 return [$tableAlias, $columnName];
302 * Fetch a field from the getFields list
304 * @param string $fieldName
307 abstract protected function getField($fieldName);
310 * Perform input validation on params that use the join syntax
312 * Arguably this should be done at the api wrapper level, but doing it here provides a bit more consistency
313 * in that api permissions to perform the join are checked first.
319 protected function validateNestedInput($fieldName, &$value) {
320 $stack = explode('.', $fieldName);
321 $spec = $this->apiFieldSpec
;
322 $fieldName = array_pop($stack);
323 foreach ($stack as $depth => $name) {
324 $entity = $spec[$name]['FKApiName'];
325 $spec = $spec[$name]['FKApiSpec'];
327 $params = [$fieldName => $value];
328 \
_civicrm_api3_validate_fields($entity, 'get', $params, $spec);
329 $value = $params[$fieldName];
333 * Check permission to join onto another api entity
335 * @param string $entity
336 * @param array $fieldStack
337 * The stack of fields leading up to this join
340 protected function checkPermissionToJoin($entity, $fieldStack) {
341 if (!$this->checkPermissions
) {
344 // Build an array of params that relate to the joined entity
348 'check_permissions' => $this->checkPermissions
,
350 $prefix = implode('.', $fieldStack) . '.';
351 $len = strlen($prefix);
352 foreach ($this->select
as $key => $ret) {
353 if (strpos($key, $prefix) === 0) {
354 $params['return'][substr($key, $len)] = $ret;
357 foreach ($this->where
as $key => $param) {
358 if (strpos($key, $prefix) === 0) {
359 $params[substr($key, $len)] = $param;
363 return \Civi
::service('civi_api_kernel')->runAuthorize($entity, 'get', $params);
367 * Get acl clause for an entity
369 * @param string $tableAlias
370 * @param \CRM_Core_DAO|string $baoName
371 * @param array $stack
374 public function getAclClause($tableAlias, $baoName, $stack = []) {
375 if (!$this->checkPermissions
) {
378 // Prevent (most) redundant acl sub clauses if they have already been applied to the main entity.
379 // FIXME: Currently this only works 1 level deep, but tracking through multiple joins would increase complexity
380 // and just doing it for the first join takes care of most acl clause deduping.
381 if (count($stack) === 1 && in_array($stack[0], $this->aclFields
)) {
384 $clauses = $baoName::getSelectWhereClause($tableAlias);
386 // Track field clauses added to the main entity
387 $this->aclFields
= array_keys($clauses);
389 return array_filter($clauses);
393 * Orders the query by one or more fields
395 * @throws \API_Exception
396 * @throws \Civi\API\Exception\UnauthorizedException
398 protected function buildOrderBy() {
399 $sortParams = is_string($this->orderBy
) ?
explode(',', $this->orderBy
) : (array) $this->orderBy
;
400 foreach ($sortParams as $index => $item) {
402 if ($item == '(1)') {
405 $words = preg_split("/[\s]+/", $item);
407 // Direction defaults to ASC unless DESC is specified
408 $direction = strtoupper(\CRM_Utils_Array
::value(1, $words, '')) == 'DESC' ?
' DESC' : '';
409 $field = $this->getField($words[0]);
411 $this->query
->orderBy(self
::MAIN_TABLE_ALIAS
. '.' . $field['name'] . $direction, NULL, $index);
413 elseif (strpos($words[0], '.')) {
414 $join = $this->addFkField($words[0], 'LEFT');
416 $this->query
->orderBy("`{$join[0]}`.`{$join[1]}`$direction", NULL, $index);
420 throw new \
API_Exception("Unknown field specified for sort. Cannot order by '$item'");
427 * @param string $side
428 * @param string $tableName
429 * @param string $tableAlias
430 * @param array $conditions
432 public function join($side, $tableName, $tableAlias, $conditions) {
433 // INNER JOINs take precedence over LEFT JOINs
434 if ($side != 'LEFT' ||
!isset($this->joins
[$tableAlias])) {
435 $this->joins
[$tableAlias] = $side;
436 $this->query
->join($tableAlias, "$side JOIN `$tableName` `$tableAlias` ON " . implode(' AND ', $conditions));
441 * Populate where clauses
443 * @throws \Civi\API\Exception\UnauthorizedException
446 abstract protected function buildWhereClause();
449 * Populate $this->selectFields
451 * @throws \Civi\API\Exception\UnauthorizedException
453 protected function buildSelectFields() {
454 $return_all_fields = (empty($this->select
) ||
!is_array($this->select
));
455 $return = $return_all_fields ?
$this->entityFieldNames
: $this->select
;
456 if ($return_all_fields ||
in_array('custom', $this->select
)) {
457 foreach (array_keys($this->apiFieldSpec
) as $fieldName) {
458 if (strpos($fieldName, 'custom_') === 0) {
459 $return[] = $fieldName;
464 // Always select the ID if the table has one.
465 if (array_key_exists('id', $this->apiFieldSpec
)) {
466 $this->selectFields
[self
::MAIN_TABLE_ALIAS
. ".id"] = "id";
469 // core return fields
470 foreach ($return as $fieldName) {
471 $field = $this->getField($fieldName);
472 if ($field && in_array($field['name'], $this->entityFieldNames
)) {
473 $this->selectFields
[self
::MAIN_TABLE_ALIAS
. ".{$field['name']}"] = $field['name'];
475 elseif (strpos($fieldName, '.')) {
476 $fkField = $this->addFkField($fieldName, 'LEFT');
478 $this->selectFields
[implode('.', $fkField)] = $fieldName;
481 elseif ($field && strpos($fieldName, 'custom_') === 0) {
482 [$table_name, $column_name] = $this->addCustomField($field, 'LEFT');
484 if ($field['data_type'] != 'ContactReference') {
485 // 'ordinary' custom field. We will select the value as custom_XX.
486 $this->selectFields
["$table_name.$column_name"] = $fieldName;
489 // contact reference custom field. The ID will be stored in custom_XX_id.
490 // custom_XX will contain the sort name of the contact.
491 $this->query
->join("c_$fieldName", "LEFT JOIN civicrm_contact c_$fieldName ON c_$fieldName.id = `$table_name`.`$column_name`");
492 $this->selectFields
["$table_name.$column_name"] = $fieldName . "_id";
493 // We will call the contact table for the join c_XX.
494 $this->selectFields
["c_$fieldName.sort_name"] = $fieldName;
504 abstract protected function getFields();