3 +--------------------------------------------------------------------+
5 +--------------------------------------------------------------------+
6 | Copyright CiviCRM LLC (c) 2004-2020 |
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 namespace Civi\Api4\Query
;
30 use Civi\API\SelectQuery
;
31 use Civi\Api4\Event\Events
;
32 use Civi\Api4\Event\PostSelectQueryEvent
;
33 use Civi\Api4\Service\Schema\Joinable\CustomGroupJoinable
;
34 use Civi\Api4\Service\Schema\Joinable\Joinable
;
35 use Civi\Api4\Utils\FormattingUtil
;
36 use Civi\Api4\Utils\CoreUtil
;
37 use CRM_Core_DAO_AllCoreTables
as AllCoreTables
;
38 use CRM_Utils_Array
as UtilsArray
;
41 * A query `node` may be in one of three formats:
43 * * leaf: [$fieldName, $operator, $criteria]
44 * * negated: ['NOT', $node]
45 * * branch: ['OR|NOT', [$node, $node, ...]]
47 * Leaf operators are one of:
49 * * '=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=",
50 * * "NOT LIKE", 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN',
51 * * 'IS NOT NULL', or 'IS NULL'.
53 class Api4SelectQuery
extends SelectQuery
{
58 protected $apiVersion = 4;
62 * Maps select fields to [<table_alias>, <column_alias>]
64 protected $fkSelectAliases = [];
67 * @var \Civi\Api4\Service\Schema\Joinable\Joinable[]
68 * The joinable tables that have been joined so far
70 protected $joinedTables = [];
73 * @param string $entity
74 * @param bool $checkPermissions
75 * @param array $fields
77 public function __construct($entity, $checkPermissions, $fields) {
78 require_once 'api/v3/utils.php';
79 $this->entity
= $entity;
80 $this->checkPermissions
= $checkPermissions;
82 $baoName = CoreUtil
::getBAOFromApiName($entity);
83 $bao = new $baoName();
85 $this->entityFieldNames
= _civicrm_api3_field_names(_civicrm_api3_build_fields_array($bao));
86 $this->apiFieldSpec
= (array) $fields;
88 \CRM_Utils_SQL_Select
::from($this->getTableName($baoName) . ' ' . self
::MAIN_TABLE_ALIAS
);
90 // Add ACLs first to avoid redundant subclauses
91 $this->query
->where($this->getAclClause(self
::MAIN_TABLE_ALIAS
, $baoName));
95 * Why walk when you can
99 public function run() {
101 $this->buildSelectFields();
102 $this->buildWhereClause();
105 if (in_array('row_count', $this->select
)) {
106 $this->query
->select("count(*) as c");
109 foreach ($this->selectFields
as $column => $alias) {
110 $this->query
->select("$column as `$alias`");
113 $this->buildOrderBy();
117 if (!empty($this->limit
) ||
!empty($this->offset
)) {
118 $this->query
->limit($this->limit
, $this->offset
);
122 $sql = $this->query
->toSQL();
123 $query = \CRM_Core_DAO
::executeQuery($sql);
125 while ($query->fetch()) {
126 if (in_array('row_count', $this->select
)) {
127 $results[]['row_count'] = (int) $query->c
;
130 $results[$query->id
] = [];
131 foreach ($this->selectFields
as $column => $alias) {
132 $returnName = $alias;
133 $alias = str_replace('.', '_', $alias);
134 $results[$query->id
][$returnName] = property_exists($query, $alias) ?
$query->$alias : NULL;
137 $event = new PostSelectQueryEvent($results, $this);
138 \Civi
::dispatcher()->dispatch(Events
::POST_SELECT_QUERY
, $event);
140 return $event->getResults();
144 * Gets all FK fields and does the required joins
146 protected function addJoins() {
147 $allFields = array_merge($this->select
, array_keys($this->orderBy
));
148 $recurse = function($clauses) use (&$allFields, &$recurse) {
149 foreach ($clauses as $clause) {
150 if ($clause[0] === 'NOT' && is_string($clause[1][0])) {
151 $recurse($clause[1][1]);
153 elseif (in_array($clause[0], ['AND', 'OR', 'NOT'])) {
154 $recurse($clause[1]);
156 elseif (is_array($clause[0])) {
157 array_walk($clause, $recurse);
160 $allFields[] = $clause[0];
164 $recurse($this->where
);
165 $dotFields = array_unique(array_filter($allFields, function ($field) {
166 return strpos($field, '.') !== FALSE;
169 foreach ($dotFields as $dotField) {
170 $this->joinFK($dotField);
175 * Populate $this->selectFields
177 * @throws \Civi\API\Exception\UnauthorizedException
179 protected function buildSelectFields() {
180 $return_all_fields = (empty($this->select
) ||
!is_array($this->select
));
181 $return = $return_all_fields ?
$this->entityFieldNames
: $this->select
;
182 if ($return_all_fields ||
in_array('custom', $this->select
)) {
183 foreach (array_keys($this->apiFieldSpec
) as $fieldName) {
184 if (strpos($fieldName, 'custom_') === 0) {
185 $return[] = $fieldName;
190 // Always select the ID if the table has one.
191 if (array_key_exists('id', $this->apiFieldSpec
) ||
strstr($this->entity
, 'Custom_')) {
192 $this->selectFields
[self
::MAIN_TABLE_ALIAS
. ".id"] = "id";
195 // core return fields
196 foreach ($return as $fieldName) {
197 $field = $this->getField($fieldName);
198 if (strpos($fieldName, '.') && !empty($this->fkSelectAliases
[$fieldName]) && !array_filter($this->getPathJoinTypes($fieldName))) {
199 $this->selectFields
[$this->fkSelectAliases
[$fieldName]] = $fieldName;
201 elseif ($field && in_array($field['name'], $this->entityFieldNames
)) {
202 $this->selectFields
[self
::MAIN_TABLE_ALIAS
. "." . UtilsArray
::value('column_name', $field, $field['name'])] = $field['name'];
210 protected function buildWhereClause() {
211 foreach ($this->where
as $clause) {
212 $sql_clause = $this->treeWalkWhereClause($clause);
213 $this->query
->where($sql_clause);
220 protected function buildOrderBy() {
221 foreach ($this->orderBy
as $field => $dir) {
222 if ($dir !== 'ASC' && $dir !== 'DESC') {
223 throw new \
API_Exception("Invalid sort direction. Cannot order by $field $dir");
225 if ($this->getField($field)) {
226 $this->query
->orderBy(self
::MAIN_TABLE_ALIAS
. '.' . $field . " $dir");
229 throw new \
API_Exception("Invalid sort field. Cannot order by $field $dir");
235 * Recursively validate and transform a branch or leaf clause array to SQL.
237 * @param array $clause
238 * @return string SQL where clause
240 * @uses validateClauseAndComposeSql() to generate the SQL etc.
241 * @todo if an 'and' is nested within and 'and' (or or-in-or) then should
242 * flatten that to be a single list of clauses.
244 protected function treeWalkWhereClause($clause) {
245 switch ($clause[0]) {
249 if (count($clause[1]) === 1) {
250 // a single set so AND|OR is immaterial
251 return $this->treeWalkWhereClause($clause[1][0]);
254 $sql_subclauses = [];
255 foreach ($clause[1] as $subclause) {
256 $sql_subclauses[] = $this->treeWalkWhereClause($subclause);
258 return '(' . implode("\n" . $clause[0], $sql_subclauses) . ')';
262 // If we get a group of clauses with no operator, assume AND
263 if (!is_string($clause[1][0])) {
264 $clause[1] = ['AND', $clause[1]];
266 return 'NOT (' . $this->treeWalkWhereClause($clause[1]) . ')';
269 return $this->validateClauseAndComposeSql($clause);
274 * Validate and transform a leaf clause array to SQL.
275 * @param array $clause [$fieldName, $operator, $criteria]
277 * @throws \API_Exception
280 protected function validateClauseAndComposeSql($clause) {
281 // Pad array for unary operators
282 list($key, $operator, $value) = array_pad($clause, 3, NULL);
283 $fieldSpec = $this->getField($key);
284 // derive table and column:
287 if (in_array($key, $this->entityFieldNames
)) {
288 $table_name = self
::MAIN_TABLE_ALIAS
;
291 elseif (strpos($key, '.') && isset($this->fkSelectAliases
[$key])) {
292 list($table_name, $column_name) = explode('.', $this->fkSelectAliases
[$key]);
295 if (!$table_name ||
!$column_name) {
296 throw new \
API_Exception("Invalid field '$key' in where clause.");
299 FormattingUtil
::formatValue($value, $fieldSpec, $this->getEntity());
301 $sql_clause = \CRM_Core_DAO
::createSQLFilter("`$table_name`.`$column_name`", [$operator => $value]);
302 if ($sql_clause === NULL) {
303 throw new \
API_Exception("Invalid value in where clause for field '$key'");
311 protected function getFields() {
312 return $this->apiFieldSpec
;
316 * Fetch a field from the getFields list
318 * @param string $fieldName
320 * @return string|null
322 protected function getField($fieldName) {
324 $fieldPath = explode('.', $fieldName);
325 if (count($fieldPath) > 1) {
326 $fieldName = implode('.', array_slice($fieldPath, -2));
328 return UtilsArray
::value($fieldName, $this->apiFieldSpec
);
335 * @throws \API_Exception
337 protected function joinFK($key) {
338 $pathArray = explode('.', $key);
340 if (count($pathArray) < 2) {
344 /** @var \Civi\Api4\Service\Schema\Joiner $joiner */
345 $joiner = \Civi
::container()->get('joiner');
346 $field = array_pop($pathArray);
347 $pathString = implode('.', $pathArray);
349 if (!$joiner->canJoin($this, $pathString)) {
353 $joinPath = $joiner->join($this, $pathString);
354 /** @var \Civi\Api4\Service\Schema\Joinable\Joinable $lastLink */
355 $lastLink = array_pop($joinPath);
357 // Cache field info for retrieval by $this->getField()
358 $prefix = array_pop($pathArray) . '.';
359 if (!isset($this->apiFieldSpec
[$prefix . $field])) {
360 $joinEntity = $lastLink->getEntity();
361 // Custom fields are already prefixed
362 if ($lastLink instanceof CustomGroupJoinable
) {
365 foreach ($lastLink->getEntityFields() as $fieldObject) {
366 $this->apiFieldSpec
[$prefix . $fieldObject->getName()] = $fieldObject->toArray() +
['entity' => $joinEntity];
370 if (!$lastLink->getField($field)) {
371 throw new \
API_Exception('Invalid join');
374 // custom groups use aliases for field names
375 if ($lastLink instanceof CustomGroupJoinable
) {
376 $field = $lastLink->getSqlColumn($field);
379 $this->fkSelectAliases
[$key] = sprintf('%s.%s', $lastLink->getAlias(), $field);
383 * @param \Civi\Api4\Service\Schema\Joinable\Joinable $joinable
387 public function addJoinedTable(Joinable
$joinable) {
388 $this->joinedTables
[] = $joinable;
394 * @return FALSE|string
396 public function getFrom() {
397 return AllCoreTables
::getTableForClass(AllCoreTables
::getFullName($this->entity
));
403 public function getEntity() {
404 return $this->entity
;
410 public function getSelect() {
411 return $this->select
;
417 public function getWhere() {
424 public function getOrderBy() {
425 return $this->orderBy
;
431 public function getLimit() {
438 public function getOffset() {
439 return $this->offset
;
445 public function getSelectFields() {
446 return $this->selectFields
;
452 public function isFillUniqueFields() {
453 return $this->isFillUniqueFields
;
457 * @return \CRM_Utils_SQL_Select
459 public function getQuery() {
466 public function getJoins() {
473 public function getApiFieldSpec() {
474 return $this->apiFieldSpec
;
480 public function getEntityFieldNames() {
481 return $this->entityFieldNames
;
487 public function getAclFields() {
488 return $this->aclFields
;
492 * @return bool|string
494 public function getCheckPermissions() {
495 return $this->checkPermissions
;
501 public function getApiVersion() {
502 return $this->apiVersion
;
508 public function getFkSelectAliases() {
509 return $this->fkSelectAliases
;
513 * @return \Civi\Api4\Service\Schema\Joinable\Joinable[]
515 public function getJoinedTables() {
516 return $this->joinedTables
;
520 * @return \Civi\Api4\Service\Schema\Joinable\Joinable
522 public function getJoinedTable($alias) {
523 foreach ($this->joinedTables
as $join) {
524 if ($join->getAlias() == $alias) {
531 * Get table name on basis of entity
533 * @param string $baoName
537 public function getTableName($baoName) {
538 if (strstr($this->entity
, 'Custom_')) {
539 $this->query
= \CRM_Utils_SQL_Select
::from(CoreUtil
::getCustomTableByName(str_replace('Custom_', '', $this->entity
)) . ' ' . self
::MAIN_TABLE_ALIAS
);
540 $this->entityFieldNames
= array_keys($this->apiFieldSpec
);
543 $bao = new $baoName();
544 $this->query
= \CRM_Utils_SQL_Select
::from($bao->tableName() . ' ' . self
::MAIN_TABLE_ALIAS
);
549 * Separates a string like 'emails.location_type.label' into an array, where
550 * each value in the array tells whether it is 1-1 or 1-n join type
552 * @param string $pathString
553 * Dot separated path to the field
556 * Index is table alias and value is boolean whether is 1-to-many join
558 public function getPathJoinTypes($pathString) {
559 $pathParts = explode('.', $pathString);
561 array_pop($pathParts);
564 $isMultipleChecker = function($alias) use ($query) {
565 foreach ($query->getJoinedTables() as $table) {
566 if ($table->getAlias() === $alias) {
567 return $table->getJoinType() === Joinable
::JOIN_TYPE_ONE_TO_MANY
;
573 foreach ($pathParts as $part) {
574 $path[$part] = $isMultipleChecker($part);