3 +--------------------------------------------------------------------+
4 | CiviCRM version 4.7 |
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2017 |
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.
43 abstract class SelectQuery
{
47 MAIN_TABLE_ALIAS
= 'a';
53 public $select = array();
54 public $where = array();
55 public $orderBy = array();
61 protected $selectFields = array();
65 public $isFillUniqueFields = FALSE;
67 * @var \CRM_Utils_SQL_Select
73 protected $joins = array();
77 protected $apiFieldSpec;
81 protected $entityFieldNames;
85 protected $aclFields = array();
89 protected $checkPermissions;
91 protected $apiVersion;
94 * @param string $entity
95 * @param bool $checkPermissions
97 public function __construct($entity, $checkPermissions) {
98 $this->entity
= $entity;
99 require_once 'api/v3/utils.php';
100 $baoName = _civicrm_api3_get_BAO($entity);
101 $bao = new $baoName();
103 $this->entityFieldNames
= _civicrm_api3_field_names(_civicrm_api3_build_fields_array($bao));
104 $this->apiFieldSpec
= $this->getFields();
106 $this->query
= \CRM_Utils_SQL_Select
::from($bao->tableName() . ' ' . self
::MAIN_TABLE_ALIAS
);
109 // Add ACLs first to avoid redundant subclauses
110 $this->checkPermissions
= $checkPermissions;
111 $this->query
->where($this->getAclClause(self
::MAIN_TABLE_ALIAS
, $baoName));
115 * Build & execute the query and return results array
118 * @throws \API_Exception
119 * @throws \CRM_Core_Exception
122 public function run() {
123 $this->buildSelectFields();
125 $this->buildWhereClause();
126 if (in_array('count_rows', $this->select
)) {
127 $this->query
->select("count(*) as c");
130 foreach ($this->selectFields
as $column => $alias) {
131 $this->query
->select("$column as `$alias`");
134 $this->buildOrderBy();
138 if (!empty($this->limit
) ||
!empty($this->offset
)) {
139 $this->query
->limit($this->limit
, $this->offset
);
142 $result_entities = array();
143 $result_dao = \CRM_Core_DAO
::executeQuery($this->query
->toSQL());
145 while ($result_dao->fetch()) {
146 if (in_array('count_rows', $this->select
)) {
148 return (int) $result_dao->c
;
150 $result_entities[$result_dao->id
] = array();
151 foreach ($this->selectFields
as $column => $alias) {
152 $returnName = $alias;
153 $alias = str_replace('.', '_', $alias);
154 if (property_exists($result_dao, $alias) && $result_dao->$alias != NULL) {
155 $result_entities[$result_dao->id
][$returnName] = $result_dao->$alias;
157 // Backward compatibility on fields names.
158 if ($this->isFillUniqueFields
&& !empty($this->apiFieldSpec
[$alias]['uniqueName'])) {
159 $result_entities[$result_dao->id
][$this->apiFieldSpec
[$alias]['uniqueName']] = $result_dao->$alias;
161 foreach ($this->apiFieldSpec
as $returnName => $spec) {
162 if (empty($result_entities[$result_dao->id
][$returnName]) && !empty($result_entities[$result_dao->id
][$spec['name']])) {
163 $result_entities[$result_dao->id
][$returnName] = $result_entities[$result_dao->id
][$spec['name']];
169 return $result_entities;
173 * @param \CRM_Utils_SQL_Select $sqlFragment
174 * @return SelectQuery
176 public function merge($sqlFragment) {
177 $this->query
->merge($sqlFragment);
182 * Joins onto an fk field
184 * Adds one or more joins to the query to make this field available for use in a clause.
186 * Enforces permissions at the api level and by appending the acl clause for that entity to the join.
188 * @param $fkFieldName
192 * Returns the table and field name for adding this field to a SELECT or WHERE clause
193 * @throws \API_Exception
194 * @throws \Civi\API\Exception\UnauthorizedException
196 protected function addFkField($fkFieldName, $side) {
197 $stack = explode('.', $fkFieldName);
198 if (count($stack) < 2) {
201 $prev = self
::MAIN_TABLE_ALIAS
;
202 foreach ($stack as $depth => $fieldName) {
203 // Setup variables then skip the first level
206 // We only join on core fields
207 // @TODO: Custom contact ref fields could be supported too
208 if (!in_array($fk, $this->entityFieldNames
)) {
211 $fkField = &$this->apiFieldSpec
[$fk];
214 // More than 4 joins deep seems excessive - DOS attack?
215 if ($depth > self
::MAX_JOINS
) {
216 throw new UnauthorizedException("Maximum number of joins exceeded in parameter $fkFieldName");
218 $subStack = array_slice($stack, 0, $depth);
219 $this->getJoinInfo($fkField, $subStack);
220 if (!isset($fkField['FKApiName']) ||
!isset($fkField['FKClassName'])) {
221 // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it.
224 // Ensure we have permission to access the other api
225 if (!$this->checkPermissionToJoin($fkField['FKApiName'], $subStack)) {
226 throw new UnauthorizedException("Authorization failed to join onto {$fkField['FKApiName']} api in parameter $fkFieldName");
228 if (!isset($fkField['FKApiSpec'])) {
229 $fkField['FKApiSpec'] = \
_civicrm_api_get_fields($fkField['FKApiName']);
231 $fieldInfo = \CRM_Utils_Array
::value($fieldName, $fkField['FKApiSpec']);
233 $keyColumn = \CRM_Utils_Array
::value('FKKeyColumn', $fkField, 'id');
234 if (!$fieldInfo ||
!isset($fkField['FKApiSpec'][$keyColumn])) {
235 // Join doesn't exist - might be another param with a dot in it for some reason, we'll just ignore it.
238 $fkTable = \CRM_Core_DAO_AllCoreTables
::getTableForClass($fkField['FKClassName']);
239 $tableAlias = implode('_to_', $subStack) . "_to_$fkTable";
242 $joinCondition = array_merge(
243 array("$prev.$fk = $tableAlias.$keyColumn"),
244 $this->getAclClause($tableAlias, \
_civicrm_api3_get_BAO($fkField['FKApiName']), $subStack)
247 if (!empty($fkField['FKCondition'])) {
248 $joinCondition[] = str_replace($fkTable, $tableAlias, $fkField['FKCondition']);
251 $this->join($side, $fkTable, $tableAlias, $joinCondition);
253 if (strpos($fieldName, 'custom_') === 0) {
254 list($tableAlias, $fieldName) = $this->addCustomField($fieldInfo, $side, $tableAlias);
257 // Get ready to recurse to the next level
259 $fkField = &$fkField['FKApiSpec'][$fieldName];
262 return array($tableAlias, $fieldName);
266 * Get join info for dynamically-joined fields (e.g. "entity_id", "option_group")
271 protected function getJoinInfo(&$fkField, $stack) {
272 if ($fkField['name'] == 'entity_id') {
273 $entityTableParam = substr(implode('.', $stack), 0, -2) . 'table';
274 $entityTable = \CRM_Utils_Array
::value($entityTableParam, $this->where
);
275 if ($entityTable && is_string($entityTable) && \CRM_Core_DAO_AllCoreTables
::getClassForTable($entityTable)) {
276 $fkField['FKClassName'] = \CRM_Core_DAO_AllCoreTables
::getClassForTable($entityTable);
277 $fkField['FKApiName'] = \CRM_Core_DAO_AllCoreTables
::getBriefName($fkField['FKClassName']);
280 if (!empty($fkField['pseudoconstant']['optionGroupName'])) {
281 $fkField['FKClassName'] = 'CRM_Core_DAO_OptionValue';
282 $fkField['FKApiName'] = 'OptionValue';
283 $fkField['FKKeyColumn'] = 'value';
284 $fkField['FKCondition'] = "civicrm_option_value.option_group_id = (SELECT id FROM civicrm_option_group WHERE name = '{$fkField['pseudoconstant']['optionGroupName']}')";
289 * Joins onto a custom field
291 * Adds a join to the query to make this field available for use in a clause.
293 * @param array $customField
294 * @param string $side
295 * @param string $baseTable
297 * Returns the table and field name for adding this field to a SELECT or WHERE clause
299 protected function addCustomField($customField, $side, $baseTable = self
::MAIN_TABLE_ALIAS
) {
300 $tableName = $customField["table_name"];
301 $columnName = $customField["column_name"];
302 $tableAlias = "{$baseTable}_to_$tableName";
303 $this->join($side, $tableName, $tableAlias, array("`$tableAlias`.entity_id = `$baseTable`.id"));
304 return array($tableAlias, $columnName);
308 * Fetch a field from the getFields list
310 * @param string $fieldName
313 abstract protected function getField($fieldName);
316 * Perform input validation on params that use the join syntax
318 * Arguably this should be done at the api wrapper level, but doing it here provides a bit more consistency
319 * in that api permissions to perform the join are checked first.
325 protected function validateNestedInput($fieldName, &$value) {
326 $stack = explode('.', $fieldName);
327 $spec = $this->apiFieldSpec
;
328 $fieldName = array_pop($stack);
329 foreach ($stack as $depth => $name) {
330 $entity = $spec[$name]['FKApiName'];
331 $spec = $spec[$name]['FKApiSpec'];
333 $params = array($fieldName => $value);
334 \
_civicrm_api3_validate_fields($entity, 'get', $params, $spec);
335 $value = $params[$fieldName];
339 * Check permission to join onto another api entity
341 * @param string $entity
342 * @param array $fieldStack
343 * The stack of fields leading up to this join
346 protected function checkPermissionToJoin($entity, $fieldStack) {
347 if (!$this->checkPermissions
) {
350 // Build an array of params that relate to the joined entity
354 'check_permissions' => $this->checkPermissions
,
356 $prefix = implode('.', $fieldStack) . '.';
357 $len = strlen($prefix);
358 foreach ($this->select
as $key => $ret) {
359 if (strpos($key, $prefix) === 0) {
360 $params['return'][substr($key, $len)] = $ret;
363 foreach ($this->where
as $key => $param) {
364 if (strpos($key, $prefix) === 0) {
365 $params[substr($key, $len)] = $param;
369 return \Civi
::service('civi_api_kernel')->runAuthorize($entity, 'get', $params);
373 * Get acl clause for an entity
375 * @param string $tableAlias
376 * @param string $baoName
377 * @param array $stack
380 protected function getAclClause($tableAlias, $baoName, $stack = array()) {
381 if (!$this->checkPermissions
) {
384 // Prevent (most) redundant acl sub clauses if they have already been applied to the main entity.
385 // FIXME: Currently this only works 1 level deep, but tracking through multiple joins would increase complexity
386 // and just doing it for the first join takes care of most acl clause deduping.
387 if (count($stack) === 1 && in_array($stack[0], $this->aclFields
)) {
390 $clauses = $baoName::getSelectWhereClause($tableAlias);
392 // Track field clauses added to the main entity
393 $this->aclFields
= array_keys($clauses);
395 return array_filter($clauses);
399 * Orders the query by one or more fields
401 * @throws \API_Exception
402 * @throws \Civi\API\Exception\UnauthorizedException
404 protected function buildOrderBy() {
406 $sortParams = is_string($this->orderBy
) ?
explode(',', $this->orderBy
) : (array) $this->orderBy
;
407 foreach ($sortParams as $item) {
408 $words = preg_split("/[\s]+/", trim($item));
410 // Direction defaults to ASC unless DESC is specified
411 $direction = strtoupper(\CRM_Utils_Array
::value(1, $words, '')) == 'DESC' ?
' DESC' : '';
412 $field = $this->getField($words[0]);
414 $orderBy[] = self
::MAIN_TABLE_ALIAS
. '.' . $field['name'] . $direction;
416 elseif (strpos($words[0], '.')) {
417 $join = $this->addFkField($words[0], 'LEFT');
419 $orderBy[] = "`{$join[0]}`.`{$join[1]}`$direction";
423 throw new \
API_Exception("Unknown field specified for sort. Cannot order by '$item'");
427 $this->query
->orderBy($orderBy);
431 * @param string $side
432 * @param string $tableName
433 * @param string $tableAlias
434 * @param array $conditions
436 public function join($side, $tableName, $tableAlias, $conditions) {
437 // INNER JOINs take precedence over LEFT JOINs
438 if ($side != 'LEFT' ||
!isset($this->joins
[$tableAlias])) {
439 $this->joins
[$tableAlias] = $side;
440 $this->query
->join($tableAlias, "$side JOIN `$tableName` `$tableAlias` ON " . implode(' AND ', $conditions));
445 * Populate where clauses
447 * @throws \Civi\API\Exception\UnauthorizedException
450 abstract protected function buildWhereClause();
453 * Populate $this->selectFields
455 * @throws \Civi\API\Exception\UnauthorizedException
457 protected function buildSelectFields() {
458 $return_all_fields = (empty($this->select
) ||
!is_array($this->select
));
459 $return = $return_all_fields ?
$this->entityFieldNames
: $this->select
;
460 if ($return_all_fields ||
in_array('custom', $this->select
)) {
461 foreach (array_keys($this->apiFieldSpec
) as $fieldName) {
462 if (strpos($fieldName, 'custom_') === 0) {
463 $return[] = $fieldName;
468 // Always select the ID.
469 $this->selectFields
[self
::MAIN_TABLE_ALIAS
. ".id"] = "id";
471 // core return fields
472 foreach ($return as $fieldName) {
473 $field = $this->getField($fieldName);
474 if ($field && in_array($field['name'], $this->entityFieldNames
)) {
475 $this->selectFields
[self
::MAIN_TABLE_ALIAS
. ".{$field['name']}"] = $field['name'];
477 elseif (strpos($fieldName, '.')) {
478 $fkField = $this->addFkField($fieldName, 'LEFT');
480 $this->selectFields
[implode('.', $fkField)] = $fieldName;
483 elseif ($field && strpos($fieldName, 'custom_') === 0) {
484 list($table_name, $column_name) = $this->addCustomField($field, 'LEFT');
486 if ($field['data_type'] != 'ContactReference') {
487 // 'ordinary' custom field. We will select the value as custom_XX.
488 $this->selectFields
["$table_name.$column_name"] = $fieldName;
491 // contact reference custom field. The ID will be stored in custom_XX_id.
492 // custom_XX will contain the sort name of the contact.
493 $this->query
->join("c_$fieldName", "LEFT JOIN civicrm_contact c_$fieldName ON c_$fieldName.id = `$table_name`.`$column_name`");
494 $this->selectFields
["$table_name.$column_name"] = $fieldName . "_id";
495 // We will call the contact table for the join c_XX.
496 $this->selectFields
["c_$fieldName.sort_name"] = $fieldName;
506 abstract protected function getFields();