3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2016 |
7 +--------------------------------------------------------------------+
8 | This file is a part of CiviCRM. |
10 | CiviCRM is free software; you can copy, modify, and distribute it |
11 | under the terms of the GNU Affero General Public License |
12 | Version 3, 19 November 2007 and the CiviCRM Licensing Exception. |
14 | CiviCRM is distributed in the hope that it will be useful, but |
15 | WITHOUT ANY WARRANTY; without even the implied warranty of |
16 | MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. |
17 | See the GNU Affero General Public License for more details. |
19 | You should have received a copy of the GNU Affero General Public |
20 | License and the CiviCRM Licensing Exception along |
21 | with this program; if not, contact CiviCRM LLC |
22 | at info[AT]civicrm[DOT]org. If you have questions about the |
23 | GNU Affero General Public License or the licensing of CiviCRM, |
24 | see the CiviCRM license FAQ at http://civicrm.org/licensing |
25 +--------------------------------------------------------------------+
28 use Civi\API\Exception\UnauthorizedException
;
31 * Query builder for civicrm_api_basic_get.
33 * Fetches an entity based on specified params for the "where" clause,
34 * return properties for the "select" clause,
35 * as well as limit and order.
37 * Automatically joins on custom fields to return or filter by them.
39 * Supports an additional sql fragment which the calling api can provide.
47 MAIN_TABLE_ALIAS
= 'a';
64 protected $isFillUniqueFields;
66 * @var \CRM_Utils_SQL_Select
72 private $joins = array();
76 protected $apiFieldSpec;
80 protected $entityFieldNames;
84 protected $aclFields = array();
88 protected $checkPermissions;
91 * @param string $baoName
93 * @param array $params
94 * As passed into api get function.
95 * @param bool $isFillUniqueFields
96 * Do we need to ensure unique fields continue to be populated for this api? (backward compatibility).
98 public function __construct($baoName, $params, $isFillUniqueFields) {
99 $bao = new $baoName();
100 $this->entity
= _civicrm_api_get_entity_name_from_dao($bao);
101 $this->params
= $params;
102 $this->isFillUniqueFields
= $isFillUniqueFields;
103 $this->checkPermissions
= \CRM_Utils_Array
::value('check_permissions', $this->params
, FALSE);
104 $this->options
= _civicrm_api3_get_options_from_params($this->params
);
106 $this->entityFieldNames
= _civicrm_api3_field_names(_civicrm_api3_build_fields_array($bao));
107 // Call this function directly instead of using the api wrapper to force unique field names off
108 require_once 'api/v3/Generic.php';
109 $apiSpec = \
civicrm_api3_generic_getfields(array('entity' => $this->entity
, 'version' => 3, 'params' => array('action' => 'get')), FALSE);
110 $this->apiFieldSpec
= $apiSpec['values'];
112 $this->query
= \CRM_Utils_SQL_Select
::from($bao->tableName() . ' ' . self
::MAIN_TABLE_ALIAS
);
115 // Add ACLs first to avoid redundant subclauses
116 $this->query
->where($this->getAclClause(self
::MAIN_TABLE_ALIAS
, $baoName));
120 * Build & execute the query and return results array
123 * @throws \API_Exception
124 * @throws \CRM_Core_Exception
127 public function run() {
128 // $select_fields maps column names to the field names of the result values.
129 $select_fields = $custom_fields = array();
131 // populate $select_fields
132 $return_all_fields = (empty($this->options
['return']) ||
!is_array($this->options
['return']));
133 $return = $return_all_fields ?
array_fill_keys($this->entityFieldNames
, 1) : $this->options
['return'];
135 // core return fields
136 foreach ($return as $field_name => $include) {
138 $field = $this->getField($field_name);
139 if ($field && in_array($field['name'], $this->entityFieldNames
)) {
140 $select_fields[self
::MAIN_TABLE_ALIAS
. ".{$field['name']}"] = $field['name'];
142 elseif ($include && strpos($field_name, '.')) {
143 $fkField = $this->addFkField($field_name, 'LEFT');
145 $select_fields[implode('.', $fkField)] = $field_name;
151 // Do custom fields IF the params contain the word "custom" or we are returning *
152 if ($return_all_fields ||
strpos(json_encode($this->params
), 'custom')) {
153 $custom_fields = _civicrm_api3_custom_fields_for_entity($this->entity
);
154 foreach ($custom_fields as $cf_id => $custom_field) {
155 $field_name = "custom_$cf_id";
156 if ($return_all_fields ||
!empty($this->options
['return'][$field_name])
158 // This is a tested format so we support it.
159 !empty($this->options
['return']['custom'])
161 list($table_name, $column_name) = $this->addCustomField($custom_field, 'LEFT');
163 if ($custom_field["data_type"] != "ContactReference") {
164 // 'ordinary' custom field. We will select the value as custom_XX.
165 $select_fields["$table_name.$column_name"] = $field_name;
168 // contact reference custom field. The ID will be stored in custom_XX_id.
169 // custom_XX will contain the sort name of the contact.
170 $this->query
->join("c_$cf_id", "LEFT JOIN civicrm_contact c_$cf_id ON c_$cf_id.id = `$table_name`.`$column_name`");
171 $select_fields["$table_name.$column_name"] = $field_name . "_id";
172 // We will call the contact table for the join c_XX.
173 $select_fields["c_$cf_id.sort_name"] = $field_name;
178 // Always select the ID.
179 $select_fields[self
::MAIN_TABLE_ALIAS
. ".id"] = "id";
181 // populate where_clauses
182 foreach ($this->params
as $key => $value) {
186 if (substr($key, 0, 7) == 'filter.') {
187 // Legacy support for old filter syntax per the test contract.
188 // (Convert the style to the later one & then deal with them).
189 $filterArray = explode('.', $key);
190 $value = array($filterArray[1] => $value);
194 // Legacy support for 'filter's construct.
195 if ($key == 'filters') {
196 foreach ($value as $filterKey => $filterValue) {
197 if (substr($filterKey, -4, 4) == 'high') {
198 $key = substr($filterKey, 0, -5);
199 $value = array('<=' => $filterValue);
202 if (substr($filterKey, -3, 3) == 'low') {
203 $key = substr($filterKey, 0, -4);
204 $value = array('>=' => $filterValue);
207 if ($filterKey == 'is_current' ||
$filterKey == 'isCurrent') {
208 // Is current is almost worth creating as a 'sql filter' in the DAO function since several entities have the concept.
209 $todayStart = date('Ymd000000', strtotime('now'));
210 $todayEnd = date('Ymd235959', strtotime('now'));
211 $a = self
::MAIN_TABLE_ALIAS
;
212 $this->query
->where("($a.start_date <= '$todayStart' OR $a.start_date IS NULL)
213 AND ($a.end_date >= '$todayEnd' OR $a.end_date IS NULL)
214 AND a.is_active = 1");
218 // Ignore the "options" param if it is referring to api options and not a field in this entity
220 $key === 'options' && is_array($value)
221 && !in_array(\CRM_Utils_Array
::first(array_keys($value)), \CRM_Core_DAO
::acceptedSQLOperators())
225 $field = $this->getField($key);
227 $key = $field['name'];
229 if (in_array($key, $this->entityFieldNames
)) {
230 $table_name = self
::MAIN_TABLE_ALIAS
;
233 elseif (($cf_id = \CRM_Core_BAO_CustomField
::getKeyID($key)) != FALSE) {
234 list($table_name, $column_name) = $this->addCustomField($custom_fields[$cf_id], 'INNER');
236 elseif (strpos($key, '.')) {
237 $fkInfo = $this->addFkField($key, 'INNER');
239 list($table_name, $column_name) = $fkInfo;
240 $this->validateNestedInput($key, $value);
243 // I don't know why I had to specifically exclude 0 as a key - wouldn't the others have caught it?
244 // We normally silently ignore null values passed in - if people want IS_NULL they can use acceptedSqlOperator syntax.
245 if ((!$table_name) ||
empty($key) ||
is_null($value)) {
246 // No valid filter field. This might be a chained call or something.
247 // Just ignore this for the $where_clause.
250 if (!is_array($value)) {
251 $this->query
->where(array("`$table_name`.`$column_name` = @value"), array(
256 // We expect only one element in the array, of the form
257 // "operator" => "rhs".
258 $operator = \CRM_Utils_Array
::first(array_keys($value));
259 if (!in_array($operator, \CRM_Core_DAO
::acceptedSQLOperators())) {
260 $this->query
->where(array(
261 "{$table_name}.{$column_name} = @value"), array("@value" => $value)
265 $this->query
->where(\CRM_Core_DAO
::createSQLFilter("{$table_name}.{$column_name}", $value));
270 if (!$this->options
['is_count']) {
271 foreach ($select_fields as $column => $alias) {
272 $this->query
->select("$column as `$alias`");
276 $this->query
->select("count(*) as c");
280 if (!empty($this->options
['sort'])) {
281 $this->orderBy($this->options
['sort']);
285 if (!empty($this->options
['limit']) ||
!empty($this->options
['offset'])) {
286 $this->query
->limit($this->options
['limit'], $this->options
['offset']);
289 $result_entities = array();
290 $result_dao = \CRM_Core_DAO
::executeQuery($this->query
->toSQL());
292 while ($result_dao->fetch()) {
293 if ($this->options
['is_count']) {
295 return (int) $result_dao->c
;
297 $result_entities[$result_dao->id
] = array();
298 foreach ($select_fields as $column => $alias) {
299 $returnName = $alias;
300 $alias = str_replace('.', '_', $alias);
301 if (property_exists($result_dao, $alias) && $result_dao->$alias != NULL) {
302 $result_entities[$result_dao->id
][$returnName] = $result_dao->$alias;
304 // Backward compatibility on fields names.
305 if ($this->isFillUniqueFields
&& !empty($this->apiFieldSpec
[$alias]['uniqueName'])) {
306 $result_entities[$result_dao->id
][$this->apiFieldSpec
[$alias]['uniqueName']] = $result_dao->$alias;
308 foreach ($this->apiFieldSpec
as $returnName => $spec) {
309 if (empty($result_entities[$result_dao->id
][$returnName]) && !empty($result_entities[$result_dao->id
][$spec['name']])) {
310 $result_entities[$result_dao->id
][$returnName] = $result_entities[$result_dao->id
][$spec['name']];
316 return $result_entities;
320 * @param \CRM_Utils_SQL_Select $sqlFragment
323 public function merge($sqlFragment) {
324 $this->query
->merge($sqlFragment);
329 * Joins onto an fk field
331 * Adds one or more joins to the query to make this field available for use in a clause.
333 * Enforces permissions at the api level and by appending the acl clause for that entity to the join.
335 * @param $fkFieldName
339 * Returns the table and field name for adding this field to a SELECT or WHERE clause
340 * @throws \API_Exception
341 * @throws \Civi\API\Exception\UnauthorizedException
343 private function addFkField($fkFieldName, $side) {
344 $stack = explode('.', $fkFieldName);
345 if (count($stack) < 2) {
348 $prev = self
::MAIN_TABLE_ALIAS
;
349 foreach ($stack as $depth => $fieldName) {
350 // Setup variables then skip the first level
353 // We only join on core fields
354 // @TODO: Custom contact ref fields could be supported too
355 if (!in_array($fk, $this->entityFieldNames
)) {
358 $fkField = &$this->apiFieldSpec
[$fk];
361 // More than 4 joins deep seems excessive - DOS attack?
362 if ($depth > self
::MAX_JOINS
) {
363 throw new UnauthorizedException("Maximum number of joins exceeded in parameter $fkFieldName");
365 if (!isset($fkField['FKApiName']) ||
!isset($fkField['FKClassName'])) {
366 // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it.
369 $subStack = array_slice($stack, 0, $depth);
370 // Ensure we have permission to access the other api
371 if (!$this->checkPermissionToJoin($fkField['FKApiName'], $subStack)) {
372 throw new UnauthorizedException("Authorization failed to join onto {$fkField['FKApiName']} api in parameter $fkFieldName");
374 if (!isset($fkField['FKApiSpec'])) {
375 $fkField['FKApiSpec'] = \
_civicrm_api_get_fields($fkField['FKApiName']);
377 $fieldInfo = \CRM_Utils_Array
::value($fieldName, $fkField['FKApiSpec']);
379 // FIXME: What if the foreign key is not the "id" column?
380 if (!$fieldInfo ||
!isset($fkField['FKApiSpec']['id'])) {
381 // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it.
384 $fkTable = \CRM_Core_DAO_AllCoreTables
::getTableForClass($fkField['FKClassName']);
385 $tableAlias = implode('_to_', $subStack) . "_to_$fkTable";
388 $joinCondition = array_merge(
389 array("$prev.$fk = $tableAlias.id"),
390 $this->getAclClause($tableAlias, \
_civicrm_api3_get_BAO($fkField['FKApiName']), $subStack)
393 $this->join($side, $fkTable, $tableAlias, $joinCondition);
395 if (strpos($fieldName, 'custom_') === 0) {
396 list($tableAlias, $fieldName) = $this->addCustomField($fieldInfo, $side, $tableAlias);
399 // Get ready to recurse to the next level
401 $fkField = &$fkField['FKApiSpec'][$fieldName];
404 return array($tableAlias, $fieldName);
408 * Joins onto a custom field
410 * Adds a join to the query to make this field available for use in a clause.
412 * @param array $customField
413 * @param string $side
414 * @param string $baseTable
416 * Returns the table and field name for adding this field to a SELECT or WHERE clause
418 private function addCustomField($customField, $side, $baseTable = self
::MAIN_TABLE_ALIAS
) {
419 $tableName = $customField["table_name"];
420 $columnName = $customField["column_name"];
421 $tableAlias = "{$baseTable}_to_$tableName";
422 $this->join($side, $tableName, $tableAlias, array("`$tableAlias`.entity_id = `$baseTable`.id"));
423 return array($tableAlias, $columnName);
427 * Fetch a field from the getFields list
429 * Searches by name, uniqueName, and api.aliases
431 * @param string $fieldName
434 private function getField($fieldName) {
438 if (isset($this->apiFieldSpec
[$fieldName])) {
439 return $this->apiFieldSpec
[$fieldName];
441 foreach ($this->apiFieldSpec
as $field) {
443 $fieldName == \CRM_Utils_Array
::value('uniqueName', $field) ||
444 array_search($fieldName, \CRM_Utils_Array
::value('api.aliases', $field, array())) !== FALSE
453 * Perform input validation on params that use the join syntax
455 * Arguably this should be done at the api wrapper level, but doing it here provides a bit more consistency
456 * in that api permissions to perform the join are checked first.
462 private function validateNestedInput($fieldName, &$value) {
463 $stack = explode('.', $fieldName);
464 $spec = $this->apiFieldSpec
;
465 $fieldName = array_pop($stack);
466 foreach ($stack as $depth => $name) {
467 $entity = $spec[$name]['FKApiName'];
468 $spec = $spec[$name]['FKApiSpec'];
470 $params = array($fieldName => $value);
471 \
_civicrm_api3_validate_fields($entity, 'get', $params, $spec);
472 $value = $params[$fieldName];
476 * Check permission to join onto another api entity
478 * @param string $entity
479 * @param array $fieldStack
480 * The stack of fields leading up to this join
483 private function checkPermissionToJoin($entity, $fieldStack) {
484 if (!$this->checkPermissions
) {
487 // Build an array of params that relate to the joined entity
491 'check_permissions' => $this->checkPermissions
,
493 $prefix = implode('.', $fieldStack) . '.';
494 $len = strlen($prefix);
495 foreach ($this->options
['return'] as $key => $ret) {
496 if (strpos($key, $prefix) === 0) {
497 $params['return'][substr($key, $len)] = $ret;
500 foreach ($this->params
as $key => $param) {
501 if (strpos($key, $prefix) === 0) {
502 $params[substr($key, $len)] = $param;
506 return \Civi
::service('civi_api_kernel')->runAuthorize($entity, 'get', $params);
510 * Get acl clause for an entity
512 * @param string $tableAlias
513 * @param string $baoName
514 * @param array $stack
517 private function getAclClause($tableAlias, $baoName, $stack = array()) {
518 if (!$this->checkPermissions
) {
521 // Prevent (most) redundant acl sub clauses if they have already been applied to the main entity.
522 // FIXME: Currently this only works 1 level deep, but tracking through multiple joins would increase complexity
523 // and just doing it for the first join takes care of most acl clause deduping.
524 if (count($stack) === 1 && in_array($stack[0], $this->aclFields
)) {
527 $clauses = $baoName::getSelectWhereClause($tableAlias);
529 // Track field clauses added to the main entity
530 $this->aclFields
= array_keys($clauses);
532 return array_filter($clauses);
536 * Orders the query by one or more fields
540 * $this->orderBy(array('last_name DESC', 'birth_date'));
543 * @param string|array $sortParams
544 * @throws \API_Exception
545 * @throws \Civi\API\Exception\UnauthorizedException
547 public function orderBy($sortParams) {
549 foreach (is_array($sortParams) ?
$sortParams : explode(',', $sortParams) as $item) {
550 $words = preg_split("/[\s]+/", trim($item));
552 // Direction defaults to ASC unless DESC is specified
553 $direction = strtoupper(\CRM_Utils_Array
::value(1, $words, '')) == 'DESC' ?
' DESC' : '';
554 $field = $this->getField($words[0]);
556 $orderBy[] = self
::MAIN_TABLE_ALIAS
. '.' . $field['name'] . $direction;
558 elseif (strpos($words[0], '.')) {
559 $join = $this->addFkField($words[0], 'LEFT');
561 $orderBy[] = "`{$join[0]}`.`{$join[1]}`$direction";
565 throw new \
API_Exception("Unknown field specified for sort. Cannot order by '$item'");
569 $this->query
->orderBy($orderBy);
573 * @param string $side
574 * @param string $tableName
575 * @param string $tableAlias
576 * @param array $conditions
578 public function join($side, $tableName, $tableAlias, $conditions) {
579 // INNER JOINs take precedence over LEFT JOINs
580 if ($side != 'LEFT' ||
!isset($this->joins
[$tableAlias])) {
581 $this->joins
[$tableAlias] = $side;
582 $this->query
->join($tableAlias, "$side JOIN `$tableName` `$tableAlias` ON " . implode(' AND ', $conditions));