Commit | Line | Data |
---|---|---|
19b53e5b C |
1 | <?php |
2 | /* | |
3 | +--------------------------------------------------------------------+ | |
41498ac5 | 4 | | Copyright CiviCRM LLC. All rights reserved. | |
19b53e5b | 5 | | | |
41498ac5 TO |
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 | | |
19b53e5b C |
9 | +--------------------------------------------------------------------+ |
10 | */ | |
11 | ||
12 | namespace Civi\Api4\Query; | |
13 | ||
19b53e5b | 14 | use Civi\Api4\Service\Schema\Joinable\CustomGroupJoinable; |
19b53e5b C |
15 | use Civi\Api4\Utils\FormattingUtil; |
16 | use Civi\Api4\Utils\CoreUtil; | |
39e0f675 | 17 | use Civi\Api4\Utils\SelectUtil; |
19b53e5b C |
18 | |
19 | /** | |
20 | * A query `node` may be in one of three formats: | |
21 | * | |
22 | * * leaf: [$fieldName, $operator, $criteria] | |
23 | * * negated: ['NOT', $node] | |
24 | * * branch: ['OR|NOT', [$node, $node, ...]] | |
25 | * | |
26 | * Leaf operators are one of: | |
27 | * | |
28 | * * '=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=", | |
39deabd6 CW |
29 | * * 'NOT LIKE', 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN', |
30 | * * 'IS NOT NULL', or 'IS NULL', 'CONTAINS'. | |
19b53e5b | 31 | */ |
88409aaf CW |
32 | class Api4SelectQuery { |
33 | ||
34 | const | |
651c4c95 CW |
35 | MAIN_TABLE_ALIAS = 'a', |
36 | UNLIMITED = '18446744073709551615'; | |
19b53e5b C |
37 | |
38 | /** | |
88409aaf | 39 | * @var \CRM_Utils_SQL_Select |
19b53e5b | 40 | */ |
88409aaf | 41 | protected $query; |
19b53e5b | 42 | |
3176b04c CW |
43 | /** |
44 | * @var array | |
45 | */ | |
88409aaf | 46 | protected $joins = []; |
3176b04c | 47 | |
b65fa6dc | 48 | /** |
88409aaf | 49 | * @var array[] |
b65fa6dc | 50 | */ |
88409aaf | 51 | protected $apiFieldSpec; |
b65fa6dc | 52 | |
fba513f6 CW |
53 | /** |
54 | * @var array | |
55 | */ | |
88409aaf | 56 | protected $entityFieldNames = []; |
fba513f6 | 57 | |
88409aaf CW |
58 | /** |
59 | * @var array | |
60 | */ | |
61 | protected $aclFields = []; | |
62 | ||
63 | /** | |
64 | * @var \Civi\Api4\Generic\DAOGetAction | |
65 | */ | |
66 | private $api; | |
48102254 | 67 | |
c9e3ae2e CW |
68 | /** |
69 | * @var array | |
88409aaf | 70 | * [alias => expr][] |
c9e3ae2e | 71 | */ |
88409aaf CW |
72 | protected $selectAliases = []; |
73 | ||
74 | /** | |
75 | * @var bool | |
76 | */ | |
77 | public $forceSelectId = TRUE; | |
c9e3ae2e | 78 | |
19b53e5b | 79 | /** |
3c7c8fa6 CW |
80 | * @param \Civi\Api4\Generic\DAOGetAction $apiGet |
81 | */ | |
82 | public function __construct($apiGet) { | |
88409aaf | 83 | $this->api = $apiGet; |
651c4c95 | 84 | |
88409aaf | 85 | // Always select ID of main table unless grouping by something else |
651c4c95 CW |
86 | $this->forceSelectId = !$this->getGroupBy() || $this->getGroupBy() === ['id']; |
87 | ||
88 | // Build field lists | |
89 | foreach ($this->api->entityFields() as $field) { | |
5e327f37 | 90 | $this->entityFieldNames[] = $field['name']; |
9b06167d | 91 | $field['sql_name'] = '`' . self::MAIN_TABLE_ALIAS . '`.`' . $field['column_name'] . '`'; |
5e327f37 | 92 | $this->addSpecField($field['name'], $field); |
a689294c | 93 | } |
19b53e5b | 94 | |
651c4c95 CW |
95 | $tableName = CoreUtil::getTableName($this->getEntity()); |
96 | $this->query = \CRM_Utils_SQL_Select::from($tableName . ' ' . self::MAIN_TABLE_ALIAS); | |
19b53e5b C |
97 | |
98 | // Add ACLs first to avoid redundant subclauses | |
651c4c95 | 99 | $baoName = CoreUtil::getBAOFromApiName($this->getEntity()); |
19b53e5b C |
100 | $this->query->where($this->getAclClause(self::MAIN_TABLE_ALIAS, $baoName)); |
101 | } | |
102 | ||
103 | /** | |
651c4c95 | 104 | * Builds main final sql statement after initialization. |
19b53e5b | 105 | * |
4e97c268 CW |
106 | * @return string |
107 | * @throws \API_Exception | |
108 | * @throws \CRM_Core_Exception | |
19b53e5b | 109 | */ |
4e97c268 | 110 | public function getSql() { |
88409aaf CW |
111 | // Add explicit joins. Other joins implied by dot notation may be added later |
112 | $this->addExplicitJoins(); | |
a689294c | 113 | $this->buildSelectClause(); |
19b53e5b | 114 | $this->buildWhereClause(); |
a689294c CW |
115 | $this->buildOrderBy(); |
116 | $this->buildLimit(); | |
fba513f6 | 117 | $this->buildGroupBy(); |
c9e3ae2e | 118 | $this->buildHavingClause(); |
4e97c268 CW |
119 | return $this->query->toSQL(); |
120 | } | |
19b53e5b | 121 | |
4e97c268 CW |
122 | /** |
123 | * Why walk when you can | |
124 | * | |
651c4c95 | 125 | * @return array |
4e97c268 CW |
126 | */ |
127 | public function run() { | |
19b53e5b | 128 | $results = []; |
4e97c268 | 129 | $sql = $this->getSql(); |
88409aaf | 130 | $this->debug('sql', $sql); |
19b53e5b | 131 | $query = \CRM_Core_DAO::executeQuery($sql); |
19b53e5b | 132 | while ($query->fetch()) { |
334bebdd | 133 | $result = []; |
c9e3ae2e | 134 | foreach ($this->selectAliases as $alias => $expr) { |
19b53e5b | 135 | $returnName = $alias; |
3176b04c | 136 | $alias = str_replace('.', '_', $alias); |
334bebdd | 137 | $result[$returnName] = property_exists($query, $alias) ? $query->$alias : NULL; |
fba513f6 | 138 | } |
334bebdd | 139 | $results[] = $result; |
19b53e5b | 140 | } |
7ce7b1cd | 141 | FormattingUtil::formatOutputValues($results, $this->apiFieldSpec, $this->getEntity(), 'get', $this->selectAliases); |
334bebdd | 142 | return $results; |
19b53e5b C |
143 | } |
144 | ||
88409aaf | 145 | /** |
651c4c95 | 146 | * @return int |
88409aaf CW |
147 | * @throws \API_Exception |
148 | */ | |
651c4c95 CW |
149 | public function getCount() { |
150 | $this->addExplicitJoins(); | |
151 | $this->buildWhereClause(); | |
152 | // If no having or groupBy, we only need to select count | |
153 | if (!$this->getHaving() && !$this->getGroupBy()) { | |
154 | $this->query->select('COUNT(*) AS `c`'); | |
155 | $sql = $this->query->toSQL(); | |
156 | } | |
157 | // Use a subquery to count groups from GROUP BY or results filtered by HAVING | |
158 | else { | |
159 | // With no HAVING, just select the last field grouped by | |
160 | if (!$this->getHaving()) { | |
161 | $select = array_slice($this->getGroupBy(), -1); | |
162 | } | |
163 | $this->buildSelectClause($select ?? NULL); | |
164 | $this->buildHavingClause(); | |
165 | $this->buildGroupBy(); | |
166 | $subquery = $this->query->toSQL(); | |
167 | $sql = "SELECT count(*) AS `c` FROM ( $subquery ) AS rows"; | |
168 | } | |
169 | $this->debug('sql', $sql); | |
170 | return (int) \CRM_Core_DAO::singleValueQuery($sql); | |
171 | } | |
172 | ||
173 | /** | |
174 | * @param array $select | |
175 | * Array of select expressions; defaults to $this->getSelect | |
176 | * @throws \API_Exception | |
177 | */ | |
178 | protected function buildSelectClause($select = NULL) { | |
179 | // Use default if select not provided, exclude row_count which is handled elsewhere | |
180 | $select = array_diff($select ?? $this->getSelect(), ['row_count']); | |
9b06167d | 181 | // An empty select is the same as * |
88409aaf CW |
182 | if (empty($select)) { |
183 | $select = $this->entityFieldNames; | |
19b53e5b | 184 | } |
a689294c | 185 | else { |
48102254 | 186 | if ($this->forceSelectId) { |
88409aaf | 187 | $select = array_merge(['id'], $select); |
fba513f6 | 188 | } |
a689294c | 189 | |
2f69b203 CW |
190 | // Expand the superstar 'custom.*' to select all fields in all custom groups |
191 | $customStar = array_search('custom.*', array_values($select), TRUE); | |
192 | if ($customStar !== FALSE) { | |
193 | $customGroups = civicrm_api4($this->getEntity(), 'getFields', [ | |
194 | 'checkPermissions' => FALSE, | |
195 | 'where' => [['custom_group', 'IS NOT NULL']], | |
196 | ], ['custom_group' => 'custom_group']); | |
197 | $customSelect = []; | |
198 | foreach ($customGroups as $groupName) { | |
199 | $customSelect[] = "$groupName.*"; | |
200 | } | |
201 | array_splice($select, $customStar, 1, $customSelect); | |
202 | } | |
203 | ||
a689294c | 204 | // Expand wildcards in joins (the api wrapper already expanded non-joined wildcards) |
88409aaf | 205 | $wildFields = array_filter($select, function($item) { |
3176b04c | 206 | return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE && strpos($item, '(') === FALSE && strpos($item, ' ') === FALSE; |
a689294c | 207 | }); |
2f69b203 | 208 | |
a689294c | 209 | foreach ($wildFields as $item) { |
88409aaf | 210 | $pos = array_search($item, array_values($select)); |
334bebdd | 211 | $this->autoJoinFK($item); |
a689294c | 212 | $matches = SelectUtil::getMatchingFields($item, array_keys($this->apiFieldSpec)); |
88409aaf | 213 | array_splice($select, $pos, 1, $matches); |
a689294c | 214 | } |
88409aaf | 215 | $select = array_unique($select); |
a689294c | 216 | } |
88409aaf | 217 | foreach ($select as $item) { |
3176b04c CW |
218 | $expr = SqlExpression::convert($item, TRUE); |
219 | $valid = TRUE; | |
220 | foreach ($expr->getFields() as $fieldName) { | |
221 | $field = $this->getField($fieldName); | |
222 | // Remove expressions with unknown fields without raising an error | |
223 | if (!$field) { | |
88409aaf CW |
224 | $select = array_diff($select, [$item]); |
225 | $this->debug('undefined_fields', $fieldName); | |
3176b04c CW |
226 | $valid = FALSE; |
227 | } | |
19b53e5b | 228 | } |
3176b04c | 229 | if ($valid) { |
c9e3ae2e | 230 | $alias = $expr->getAlias(); |
19fde02c CW |
231 | if ($alias != $expr->getExpr() && isset($this->apiFieldSpec[$alias])) { |
232 | throw new \API_Exception('Cannot use existing field name as alias'); | |
233 | } | |
c9e3ae2e | 234 | $this->selectAliases[$alias] = $expr->getExpr(); |
3176b04c | 235 | $this->query->select($expr->render($this->apiFieldSpec) . " AS `$alias`"); |
9b06167d | 236 | } |
19b53e5b C |
237 | } |
238 | } | |
239 | ||
240 | /** | |
88409aaf | 241 | * Add WHERE clause to query |
19b53e5b C |
242 | */ |
243 | protected function buildWhereClause() { | |
88409aaf | 244 | foreach ($this->getWhere() as $clause) { |
0c9bccf1 CW |
245 | $sql = $this->treeWalkClauses($clause, 'WHERE'); |
246 | if ($sql) { | |
247 | $this->query->where($sql); | |
248 | } | |
c9e3ae2e CW |
249 | } |
250 | } | |
251 | ||
252 | /** | |
88409aaf | 253 | * Add HAVING clause to query |
c9e3ae2e CW |
254 | * |
255 | * Every expression referenced must also be in the SELECT clause. | |
256 | */ | |
257 | protected function buildHavingClause() { | |
88409aaf | 258 | foreach ($this->getHaving() as $clause) { |
c9e3ae2e | 259 | $this->query->having($this->treeWalkClauses($clause, 'HAVING')); |
19b53e5b C |
260 | } |
261 | } | |
262 | ||
263 | /** | |
88409aaf | 264 | * Add ORDER BY to query |
19b53e5b C |
265 | */ |
266 | protected function buildOrderBy() { | |
88409aaf | 267 | foreach ($this->getOrderBy() as $item => $dir) { |
19b53e5b | 268 | if ($dir !== 'ASC' && $dir !== 'DESC') { |
3176b04c CW |
269 | throw new \API_Exception("Invalid sort direction. Cannot order by $item $dir"); |
270 | } | |
a4499ec5 CW |
271 | $expr = $this->getExpression($item); |
272 | $column = $expr->render($this->apiFieldSpec); | |
273 | ||
274 | // Use FIELD() function to sort on pseudoconstant values | |
275 | $suffix = strstr($item, ':'); | |
276 | if ($suffix && $expr->getType() === 'SqlField') { | |
277 | $field = $this->getField($item); | |
721c9da1 | 278 | $options = FormattingUtil::getPseudoconstantList($field, substr($suffix, 1)); |
a4499ec5 CW |
279 | if ($options) { |
280 | asort($options); | |
281 | $column = "FIELD($column,'" . implode("','", array_keys($options)) . "')"; | |
282 | } | |
283 | } | |
284 | $this->query->orderBy("$column $dir"); | |
a689294c CW |
285 | } |
286 | } | |
287 | ||
288 | /** | |
88409aaf CW |
289 | * Add LIMIT to query |
290 | * | |
a689294c CW |
291 | * @throws \CRM_Core_Exception |
292 | */ | |
293 | protected function buildLimit() { | |
88409aaf | 294 | if ($this->getLimit() || $this->getOffset()) { |
f8bf8e26 | 295 | // If limit is 0, mysql will actually return 0 results. Instead set to maximum possible. |
651c4c95 | 296 | $this->query->limit($this->getLimit() ?: self::UNLIMITED, $this->getOffset()); |
19b53e5b C |
297 | } |
298 | } | |
299 | ||
fba513f6 | 300 | /** |
88409aaf | 301 | * Add GROUP BY clause to query |
fba513f6 CW |
302 | */ |
303 | protected function buildGroupBy() { | |
88409aaf | 304 | foreach ($this->getGroupBy() as $item) { |
a4499ec5 | 305 | $this->query->groupBy($this->getExpression($item)->render($this->apiFieldSpec)); |
fba513f6 CW |
306 | } |
307 | } | |
308 | ||
19b53e5b C |
309 | /** |
310 | * Recursively validate and transform a branch or leaf clause array to SQL. | |
311 | * | |
312 | * @param array $clause | |
c9e3ae2e | 313 | * @param string $type |
16f5a13d | 314 | * WHERE|HAVING|ON |
19b53e5b C |
315 | * @return string SQL where clause |
316 | * | |
c9e3ae2e CW |
317 | * @throws \API_Exception |
318 | * @uses composeClause() to generate the SQL etc. | |
19b53e5b | 319 | */ |
c9e3ae2e | 320 | protected function treeWalkClauses($clause, $type) { |
0c9bccf1 CW |
321 | // Skip empty leaf. |
322 | if (in_array($clause[0], ['AND', 'OR', 'NOT']) && empty($clause[1])) { | |
323 | return ''; | |
324 | } | |
19b53e5b C |
325 | switch ($clause[0]) { |
326 | case 'OR': | |
327 | case 'AND': | |
328 | // handle branches | |
329 | if (count($clause[1]) === 1) { | |
330 | // a single set so AND|OR is immaterial | |
c9e3ae2e | 331 | return $this->treeWalkClauses($clause[1][0], $type); |
19b53e5b C |
332 | } |
333 | else { | |
334 | $sql_subclauses = []; | |
335 | foreach ($clause[1] as $subclause) { | |
c9e3ae2e | 336 | $sql_subclauses[] = $this->treeWalkClauses($subclause, $type); |
19b53e5b C |
337 | } |
338 | return '(' . implode("\n" . $clause[0], $sql_subclauses) . ')'; | |
339 | } | |
340 | ||
341 | case 'NOT': | |
342 | // If we get a group of clauses with no operator, assume AND | |
343 | if (!is_string($clause[1][0])) { | |
344 | $clause[1] = ['AND', $clause[1]]; | |
345 | } | |
c9e3ae2e | 346 | return 'NOT (' . $this->treeWalkClauses($clause[1], $type) . ')'; |
19b53e5b C |
347 | |
348 | default: | |
c9e3ae2e | 349 | return $this->composeClause($clause, $type); |
19b53e5b C |
350 | } |
351 | } | |
352 | ||
353 | /** | |
354 | * Validate and transform a leaf clause array to SQL. | |
355 | * @param array $clause [$fieldName, $operator, $criteria] | |
c9e3ae2e | 356 | * @param string $type |
16f5a13d | 357 | * WHERE|HAVING|ON |
19b53e5b C |
358 | * @return string SQL |
359 | * @throws \API_Exception | |
360 | * @throws \Exception | |
361 | */ | |
c9e3ae2e | 362 | protected function composeClause(array $clause, string $type) { |
19b53e5b | 363 | // Pad array for unary operators |
c9e3ae2e | 364 | list($expr, $operator, $value) = array_pad($clause, 3, NULL); |
9d2afe25 | 365 | if (!in_array($operator, CoreUtil::getOperators(), TRUE)) { |
16f5a13d CW |
366 | throw new \API_Exception('Illegal operator'); |
367 | } | |
19b53e5b | 368 | |
c9e3ae2e CW |
369 | // For WHERE clause, expr must be the name of a field. |
370 | if ($type === 'WHERE') { | |
371 | $field = $this->getField($expr, TRUE); | |
3dd9e4a0 | 372 | FormattingUtil::formatInputValue($value, $expr, $field, $operator); |
c9e3ae2e CW |
373 | $fieldAlias = $field['sql_name']; |
374 | } | |
375 | // For HAVING, expr must be an item in the SELECT clause | |
16f5a13d | 376 | elseif ($type === 'HAVING') { |
37d82abe | 377 | // Expr references a fieldName or alias |
c9e3ae2e CW |
378 | if (isset($this->selectAliases[$expr])) { |
379 | $fieldAlias = $expr; | |
37d82abe CW |
380 | // Attempt to format if this is a real field |
381 | if (isset($this->apiFieldSpec[$expr])) { | |
39deabd6 | 382 | $field = $this->getField($expr); |
3dd9e4a0 | 383 | FormattingUtil::formatInputValue($value, $expr, $field, $operator); |
37d82abe | 384 | } |
c9e3ae2e | 385 | } |
37d82abe | 386 | // Expr references a non-field expression like a function; convert to alias |
c9e3ae2e CW |
387 | elseif (in_array($expr, $this->selectAliases)) { |
388 | $fieldAlias = array_search($expr, $this->selectAliases); | |
389 | } | |
37d82abe | 390 | // If either the having or select field contains a pseudoconstant suffix, match and perform substitution |
c9e3ae2e | 391 | else { |
37d82abe CW |
392 | list($fieldName) = explode(':', $expr); |
393 | foreach ($this->selectAliases as $selectAlias => $selectExpr) { | |
394 | list($selectField) = explode(':', $selectAlias); | |
395 | if ($selectAlias === $selectExpr && $fieldName === $selectField && isset($this->apiFieldSpec[$fieldName])) { | |
39deabd6 | 396 | $field = $this->getField($fieldName); |
3dd9e4a0 | 397 | FormattingUtil::formatInputValue($value, $expr, $field, $operator); |
37d82abe CW |
398 | $fieldAlias = $selectAlias; |
399 | break; | |
400 | } | |
401 | } | |
402 | } | |
403 | if (!isset($fieldAlias)) { | |
404 | throw new \API_Exception("Invalid expression in HAVING clause: '$expr'. Must use a value from SELECT clause."); | |
c9e3ae2e | 405 | } |
37d82abe | 406 | $fieldAlias = '`' . $fieldAlias . '`'; |
c9e3ae2e | 407 | } |
16f5a13d CW |
408 | elseif ($type === 'ON') { |
409 | $expr = $this->getExpression($expr); | |
410 | $fieldName = count($expr->getFields()) === 1 ? $expr->getFields()[0] : NULL; | |
411 | $fieldAlias = $expr->render($this->apiFieldSpec); | |
412 | if (is_string($value)) { | |
413 | $valExpr = $this->getExpression($value); | |
414 | if ($fieldName && $valExpr->getType() === 'SqlString') { | |
3dd9e4a0 | 415 | FormattingUtil::formatInputValue($valExpr->expr, $fieldName, $this->apiFieldSpec[$fieldName], $operator); |
16f5a13d CW |
416 | } |
417 | return sprintf('%s %s %s', $fieldAlias, $operator, $valExpr->render($this->apiFieldSpec)); | |
418 | } | |
419 | elseif ($fieldName) { | |
39deabd6 | 420 | $field = $this->getField($fieldName); |
3dd9e4a0 | 421 | FormattingUtil::formatInputValue($value, $fieldName, $field, $operator); |
39deabd6 CW |
422 | } |
423 | } | |
424 | ||
425 | if ($operator === 'CONTAINS') { | |
426 | switch ($field['serialize'] ?? NULL) { | |
427 | case \CRM_Core_DAO::SERIALIZE_JSON: | |
428 | $operator = 'LIKE'; | |
429 | $value = '%"' . $value . '"%'; | |
430 | // FIXME: Use this instead of the above hack once MIN_INSTALL_MYSQL_VER is bumped to 5.7. | |
431 | // return sprintf('JSON_SEARCH(%s, "one", "%s") IS NOT NULL', $fieldAlias, \CRM_Core_DAO::escapeString($value)); | |
432 | break; | |
433 | ||
434 | case \CRM_Core_DAO::SERIALIZE_SEPARATOR_BOOKEND: | |
435 | $operator = 'LIKE'; | |
436 | $value = '%' . \CRM_Core_DAO::VALUE_SEPARATOR . $value . \CRM_Core_DAO::VALUE_SEPARATOR . '%'; | |
437 | break; | |
438 | ||
439 | default: | |
440 | $operator = 'LIKE'; | |
441 | $value = '%' . $value . '%'; | |
442 | break; | |
16f5a13d CW |
443 | } |
444 | } | |
19b53e5b | 445 | |
c9e3ae2e | 446 | $sql_clause = \CRM_Core_DAO::createSQLFilter($fieldAlias, [$operator => $value]); |
19b53e5b | 447 | if ($sql_clause === NULL) { |
c9e3ae2e | 448 | throw new \API_Exception("Invalid value in $type clause for '$expr'"); |
19b53e5b C |
449 | } |
450 | return $sql_clause; | |
451 | } | |
452 | ||
16f5a13d CW |
453 | /** |
454 | * @param string $expr | |
455 | * @return SqlExpression | |
456 | * @throws \API_Exception | |
457 | */ | |
458 | protected function getExpression(string $expr) { | |
459 | $sqlExpr = SqlExpression::convert($expr); | |
460 | foreach ($sqlExpr->getFields() as $fieldName) { | |
461 | $this->getField($fieldName, TRUE); | |
462 | } | |
463 | return $sqlExpr; | |
464 | } | |
465 | ||
19b53e5b | 466 | /** |
88409aaf CW |
467 | * Get acl clause for an entity |
468 | * | |
469 | * @param string $tableAlias | |
470 | * @param \CRM_Core_DAO|string $baoName | |
471 | * @param array $stack | |
472 | * @return array | |
19b53e5b | 473 | */ |
88409aaf CW |
474 | public function getAclClause($tableAlias, $baoName, $stack = []) { |
475 | if (!$this->getCheckPermissions()) { | |
476 | return []; | |
477 | } | |
478 | // Prevent (most) redundant acl sub clauses if they have already been applied to the main entity. | |
479 | // FIXME: Currently this only works 1 level deep, but tracking through multiple joins would increase complexity | |
480 | // and just doing it for the first join takes care of most acl clause deduping. | |
90908aac | 481 | if (count($stack) === 1 && in_array($stack[0], $this->aclFields, TRUE)) { |
88409aaf CW |
482 | return []; |
483 | } | |
484 | $clauses = $baoName::getSelectWhereClause($tableAlias); | |
485 | if (!$stack) { | |
486 | // Track field clauses added to the main entity | |
487 | $this->aclFields = array_keys($clauses); | |
488 | } | |
489 | return array_filter($clauses); | |
19b53e5b C |
490 | } |
491 | ||
492 | /** | |
493 | * Fetch a field from the getFields list | |
494 | * | |
961e974c | 495 | * @param string $expr |
a689294c | 496 | * @param bool $strict |
3176b04c | 497 | * In strict mode, this will throw an exception if the field doesn't exist |
19b53e5b | 498 | * |
88409aaf | 499 | * @return array|null |
a689294c | 500 | * @throws \API_Exception |
19b53e5b | 501 | */ |
961e974c CW |
502 | public function getField($expr, $strict = FALSE) { |
503 | // If the expression contains a pseudoconstant filter like activity_type_id:label, | |
504 | // strip it to look up the base field name, then add the field:filter key to apiFieldSpec | |
505 | $col = strpos($expr, ':'); | |
506 | $fieldName = $col ? substr($expr, 0, $col) : $expr; | |
a689294c CW |
507 | // Perform join if field not yet available - this will add it to apiFieldSpec |
508 | if (!isset($this->apiFieldSpec[$fieldName]) && strpos($fieldName, '.')) { | |
334bebdd | 509 | $this->autoJoinFK($fieldName); |
a689294c CW |
510 | } |
511 | $field = $this->apiFieldSpec[$fieldName] ?? NULL; | |
3176b04c | 512 | if ($strict && !$field) { |
a689294c | 513 | throw new \API_Exception("Invalid field '$fieldName'"); |
19b53e5b | 514 | } |
961e974c | 515 | $this->apiFieldSpec[$expr] = $field; |
3176b04c | 516 | return $field; |
19b53e5b C |
517 | } |
518 | ||
16f5a13d CW |
519 | /** |
520 | * Join onto other entities as specified by the api call. | |
521 | * | |
16f5a13d CW |
522 | * @throws \API_Exception |
523 | * @throws \Civi\API\Exception\NotImplementedException | |
524 | */ | |
88409aaf CW |
525 | private function addExplicitJoins() { |
526 | foreach ($this->getJoin() as $join) { | |
16f5a13d CW |
527 | // First item in the array is the entity name |
528 | $entity = array_shift($join); | |
529 | // Which might contain an alias. Split on the keyword "AS" | |
530 | list($entity, $alias) = array_pad(explode(' AS ', $entity), 2, NULL); | |
531 | // Ensure alias is a safe string, and supply default if not given | |
532 | $alias = $alias ? \CRM_Utils_String::munge($alias) : strtolower($entity); | |
533 | // First item in the array is a boolean indicating if the join is required (aka INNER or LEFT). | |
534 | // The rest are join conditions. | |
535 | $side = array_shift($join) ? 'INNER' : 'LEFT'; | |
90908aac | 536 | // Add all fields from joined entity to spec |
88409aaf | 537 | $joinEntityGet = \Civi\API\Request::create($entity, 'get', ['version' => 4, 'checkPermissions' => $this->getCheckPermissions()]); |
16f5a13d CW |
538 | foreach ($joinEntityGet->entityFields() as $field) { |
539 | $field['sql_name'] = '`' . $alias . '`.`' . $field['column_name'] . '`'; | |
16f5a13d CW |
540 | $this->addSpecField($alias . '.' . $field['name'], $field); |
541 | } | |
90908aac CW |
542 | if (!empty($join[0]) && is_string($join[0]) && \CRM_Utils_Rule::alphanumeric($join[0])) { |
543 | $conditions = $this->getBridgeJoin($join, $entity, $alias); | |
544 | } | |
545 | else { | |
546 | $conditions = $this->getJoinConditions($join, $entity, $alias); | |
547 | } | |
5e327f37 | 548 | foreach (array_filter($join) as $clause) { |
16f5a13d CW |
549 | $conditions[] = $this->treeWalkClauses($clause, 'ON'); |
550 | } | |
5e327f37 | 551 | $tableName = CoreUtil::getTableName($entity); |
16f5a13d CW |
552 | $this->join($side, $tableName, $alias, $conditions); |
553 | } | |
554 | } | |
555 | ||
556 | /** | |
557 | * Supply conditions for an explicit join. | |
558 | * | |
90908aac CW |
559 | * @param array $joinTree |
560 | * @param string $joinEntity | |
561 | * @param string $alias | |
16f5a13d CW |
562 | * @return array |
563 | */ | |
90908aac | 564 | private function getJoinConditions($joinTree, $joinEntity, $alias) { |
16f5a13d CW |
565 | $conditions = []; |
566 | // getAclClause() expects a stack of 1-to-1 join fields to help it dedupe, but this is more flexible, | |
567 | // so unless this is a direct 1-to-1 join with the main entity, we'll just hack it | |
568 | // with a padded empty stack to bypass its deduping. | |
569 | $stack = [NULL, NULL]; | |
90908aac CW |
570 | // If we're not explicitly referencing the joinEntity ID in the ON clause, search for a default |
571 | $explicitId = array_filter($joinTree, function($clause) use ($alias) { | |
572 | list($sideA, $op, $sideB) = array_pad((array) $clause, 3, NULL); | |
573 | return $op === '=' && ($sideA === "$alias.id" || $sideB === "$alias.id"); | |
574 | }); | |
575 | if (!$explicitId) { | |
576 | foreach ($this->apiFieldSpec as $name => $field) { | |
577 | if ($field['entity'] !== $joinEntity && $field['fk_entity'] === $joinEntity) { | |
578 | $conditions[] = $this->treeWalkClauses([$name, '=', "$alias.id"], 'ON'); | |
579 | } | |
580 | elseif (strpos($name, "$alias.") === 0 && substr_count($name, '.') === 1 && $field['fk_entity'] === $this->getEntity()) { | |
581 | $conditions[] = $this->treeWalkClauses([$name, '=', 'id'], 'ON'); | |
582 | $stack = ['id']; | |
583 | } | |
16f5a13d | 584 | } |
90908aac CW |
585 | // Hmm, if we came up with > 1 condition, then it's ambiguous how it should be joined so we won't return anything but the generic ACLs |
586 | if (count($conditions) > 1) { | |
587 | $stack = [NULL, NULL]; | |
588 | $conditions = []; | |
16f5a13d CW |
589 | } |
590 | } | |
90908aac | 591 | $baoName = CoreUtil::getBAOFromApiName($joinEntity); |
5e327f37 | 592 | $acls = array_values($this->getAclClause($alias, $baoName, $stack)); |
16f5a13d CW |
593 | return array_merge($acls, $conditions); |
594 | } | |
595 | ||
90908aac | 596 | /** |
17019d49 CW |
597 | * Join via a Bridge table |
598 | * | |
599 | * This creates a double-join in sql that appears to the API user like a single join. | |
90908aac CW |
600 | * |
601 | * @param array $joinTree | |
602 | * @param string $joinEntity | |
603 | * @param string $alias | |
604 | * @return array | |
605 | * @throws \API_Exception | |
606 | */ | |
607 | protected function getBridgeJoin(&$joinTree, $joinEntity, $alias) { | |
608 | $bridgeEntity = array_shift($joinTree); | |
465bc32a CW |
609 | /* @var \Civi\Api4\Generic\DAOEntity $bridgeEntityClass */ |
610 | $bridgeEntityClass = '\Civi\Api4\\' . $bridgeEntity; | |
90908aac | 611 | $bridgeAlias = $alias . '_via_' . strtolower($bridgeEntity); |
17019d49 CW |
612 | $bridgeInfo = $bridgeEntityClass::getInfo(); |
613 | $bridgeFields = $bridgeInfo['bridge'] ?? []; | |
614 | // Sanity check - bridge entity should declare exactly 2 FK fields | |
615 | if (count($bridgeFields) !== 2) { | |
616 | throw new \API_Exception("Illegal bridge entity specified: $bridgeEntity. Expected 2 bridge fields, found " . count($bridgeFields)); | |
617 | } | |
618 | /* @var \CRM_Core_DAO $bridgeDAO */ | |
619 | $bridgeDAO = $bridgeInfo['dao']; | |
620 | $bridgeTable = $bridgeDAO::getTableName(); | |
621 | ||
90908aac | 622 | $joinTable = CoreUtil::getTableName($joinEntity); |
465bc32a | 623 | $bridgeEntityGet = $bridgeEntityClass::get($this->getCheckPermissions()); |
17019d49 CW |
624 | // Get the 2 bridge reference columns as CRM_Core_Reference_* objects |
625 | $joinRef = $baseRef = NULL; | |
626 | foreach ($bridgeDAO::getReferenceColumns() as $ref) { | |
627 | if (in_array($ref->getReferenceKey(), $bridgeFields)) { | |
628 | if (!$joinRef && in_array($joinEntity, $ref->getTargetEntities())) { | |
629 | $joinRef = $ref; | |
630 | } | |
631 | else { | |
632 | $baseRef = $ref; | |
633 | } | |
90908aac CW |
634 | } |
635 | } | |
17019d49 | 636 | if (!$joinRef || !$baseRef) { |
90908aac CW |
637 | throw new \API_Exception("Unable to join $bridgeEntity to $joinEntity"); |
638 | } | |
639 | // Create link between bridge entity and join entity | |
640 | $joinConditions = [ | |
17019d49 | 641 | "`$bridgeAlias`.`{$joinRef->getReferenceKey()}` = `$alias`.`{$joinRef->getTargetKey()}`", |
90908aac | 642 | ]; |
17019d49 CW |
643 | // For dynamic references, also add the type column (e.g. `entity_table`) |
644 | if ($joinRef->getTypeColumn()) { | |
645 | $joinConditions[] = "`$bridgeAlias`.`{$joinRef->getTypeColumn()}` = '$joinTable'"; | |
90908aac | 646 | } |
17019d49 CW |
647 | // Register fields (other than bridge FK fields) from the bridge entity as if they belong to the join entity |
648 | $fakeFields = []; | |
90908aac | 649 | foreach ($bridgeEntityGet->entityFields() as $name => $field) { |
17019d49 | 650 | if ($name === 'id' || $name === $joinRef->getReferenceKey() || $name === $joinRef->getTypeColumn() || $name === $baseRef->getReferenceKey() || $name === $baseRef->getTypeColumn()) { |
90908aac CW |
651 | continue; |
652 | } | |
90908aac CW |
653 | // Note these fields get a sql alias pointing to the bridge entity, but an api alias pretending they belong to the join entity |
654 | $field['sql_name'] = '`' . $bridgeAlias . '`.`' . $field['column_name'] . '`'; | |
655 | $this->addSpecField($alias . '.' . $field['name'], $field); | |
17019d49 | 656 | $fakeFields[] = $alias . '.' . $field['name']; |
90908aac CW |
657 | } |
658 | // Move conditions for the bridge join out of the joinTree | |
659 | $bridgeConditions = []; | |
17019d49 CW |
660 | $isExplicit = FALSE; |
661 | $joinTree = array_filter($joinTree, function($clause) use ($baseRef, $alias, $bridgeAlias, $fakeFields, &$bridgeConditions, &$isExplicit) { | |
90908aac | 662 | list($sideA, $op, $sideB) = array_pad((array) $clause, 3, NULL); |
17019d49 CW |
663 | // Skip AND/OR/NOT branches |
664 | if (!$sideB) { | |
665 | return TRUE; | |
666 | } | |
667 | // If this condition makes an explicit link between the bridge and another entity | |
668 | if ($op === '=' && $sideB && ($sideA === "$alias.{$baseRef->getReferenceKey()}" || $sideB === "$alias.{$baseRef->getReferenceKey()}")) { | |
669 | $expr = $sideA === "$alias.{$baseRef->getReferenceKey()}" ? $sideB : $sideA; | |
670 | $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getReferenceKey()}` = " . $this->getExpression($expr)->render($this->apiFieldSpec); | |
671 | $isExplicit = TRUE; | |
90908aac CW |
672 | return FALSE; |
673 | } | |
17019d49 CW |
674 | // Explicit link with dynamic "entity_table" column |
675 | elseif ($op === '=' && $baseRef->getTypeColumn() && ($sideA === "$alias.{$baseRef->getTypeColumn()}" || $sideB === "$alias.{$baseRef->getTypeColumn()}")) { | |
676 | $expr = $sideA === "$alias.{$baseRef->getTypeColumn()}" ? $sideB : $sideA; | |
677 | $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getTypeColumn()}` = " . $this->getExpression($expr)->render($this->apiFieldSpec); | |
678 | $isExplicit = TRUE; | |
90908aac CW |
679 | return FALSE; |
680 | } | |
17019d49 CW |
681 | // Other conditions that apply only to the bridge table should be |
682 | foreach ([$sideA, $sideB] as $expr) { | |
683 | if (is_string($expr) && in_array(explode(':', $expr)[0], $fakeFields)) { | |
684 | $bridgeConditions[] = $this->composeClause($clause, 'ON'); | |
685 | return FALSE; | |
686 | } | |
687 | } | |
90908aac CW |
688 | return TRUE; |
689 | }); | |
690 | // If no bridge conditions were specified, link it to the base entity | |
17019d49 CW |
691 | if (!$isExplicit) { |
692 | if (!in_array($this->getEntity(), $baseRef->getTargetEntities())) { | |
693 | throw new \API_Exception("Unable to join $bridgeEntity to " . $this->getEntity()); | |
694 | } | |
695 | $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getReferenceKey()}` = a.`{$baseRef->getTargetKey()}`"; | |
696 | if ($baseRef->getTypeColumn()) { | |
697 | $bridgeConditions[] = "`$bridgeAlias`.`{$baseRef->getTypeColumn()}` = '" . $this->getFrom() . "'"; | |
90908aac CW |
698 | } |
699 | } | |
700 | ||
701 | $this->join('LEFT', $bridgeTable, $bridgeAlias, $bridgeConditions); | |
702 | ||
703 | $baoName = CoreUtil::getBAOFromApiName($joinEntity); | |
704 | $acls = array_values($this->getAclClause($alias, $baoName, [NULL, NULL])); | |
705 | return array_merge($acls, $joinConditions); | |
706 | } | |
707 | ||
19b53e5b | 708 | /** |
334bebdd | 709 | * Joins a path and adds all fields in the joined entity to apiFieldSpec |
a689294c | 710 | * |
19b53e5b C |
711 | * @param $key |
712 | * @throws \API_Exception | |
a689294c | 713 | * @throws \Exception |
19b53e5b | 714 | */ |
334bebdd | 715 | protected function autoJoinFK($key) { |
a689294c | 716 | if (isset($this->apiFieldSpec[$key])) { |
9b06167d | 717 | return; |
19b53e5b C |
718 | } |
719 | ||
a689294c CW |
720 | $pathArray = explode('.', $key); |
721 | ||
19b53e5b C |
722 | /** @var \Civi\Api4\Service\Schema\Joiner $joiner */ |
723 | $joiner = \Civi::container()->get('joiner'); | |
a689294c CW |
724 | // The last item in the path is the field name. We don't care about that; we'll add all fields from the joined entity. |
725 | array_pop($pathArray); | |
19b53e5b C |
726 | $pathString = implode('.', $pathArray); |
727 | ||
334bebdd | 728 | if (!$joiner->canAutoJoin($this->getFrom(), $pathString)) { |
9b06167d | 729 | return; |
19b53e5b C |
730 | } |
731 | ||
732 | $joinPath = $joiner->join($this, $pathString); | |
9b06167d | 733 | |
19b53e5b C |
734 | $lastLink = array_pop($joinPath); |
735 | ||
a689294c | 736 | // Custom field names are already prefixed |
9b06167d CW |
737 | $isCustom = $lastLink instanceof CustomGroupJoinable; |
738 | if ($isCustom) { | |
a689294c | 739 | array_pop($pathArray); |
39e0f675 | 740 | } |
a689294c | 741 | $prefix = $pathArray ? implode('.', $pathArray) . '.' : ''; |
19b53e5b | 742 | // Cache field info for retrieval by $this->getField() |
a689294c | 743 | foreach ($lastLink->getEntityFields() as $fieldObject) { |
6fe7bdee | 744 | $fieldArray = $fieldObject->toArray(); |
a689294c | 745 | $fieldArray['sql_name'] = '`' . $lastLink->getAlias() . '`.`' . $fieldArray['column_name'] . '`'; |
9b06167d | 746 | $this->addSpecField($prefix . $fieldArray['name'], $fieldArray); |
19b53e5b | 747 | } |
19b53e5b C |
748 | } |
749 | ||
88409aaf CW |
750 | /** |
751 | * @param string $side | |
752 | * @param string $tableName | |
753 | * @param string $tableAlias | |
754 | * @param array $conditions | |
755 | */ | |
756 | public function join($side, $tableName, $tableAlias, $conditions) { | |
757 | // INNER JOINs take precedence over LEFT JOINs | |
758 | if ($side != 'LEFT' || !isset($this->joins[$tableAlias])) { | |
759 | $this->joins[$tableAlias] = $side; | |
760 | $this->query->join($tableAlias, "$side JOIN `$tableName` `$tableAlias` ON " . implode(' AND ', $conditions)); | |
761 | } | |
762 | } | |
763 | ||
19b53e5b C |
764 | /** |
765 | * @return FALSE|string | |
766 | */ | |
767 | public function getFrom() { | |
88409aaf | 768 | return CoreUtil::getTableName($this->getEntity()); |
19b53e5b C |
769 | } |
770 | ||
771 | /** | |
772 | * @return string | |
773 | */ | |
774 | public function getEntity() { | |
88409aaf | 775 | return $this->api->getEntityName(); |
19b53e5b C |
776 | } |
777 | ||
778 | /** | |
779 | * @return array | |
780 | */ | |
781 | public function getSelect() { | |
88409aaf | 782 | return $this->api->getSelect(); |
19b53e5b C |
783 | } |
784 | ||
785 | /** | |
786 | * @return array | |
787 | */ | |
788 | public function getWhere() { | |
88409aaf | 789 | return $this->api->getWhere(); |
19b53e5b C |
790 | } |
791 | ||
792 | /** | |
793 | * @return array | |
794 | */ | |
88409aaf CW |
795 | public function getHaving() { |
796 | return $this->api->getHaving(); | |
19b53e5b C |
797 | } |
798 | ||
799 | /** | |
800 | * @return array | |
801 | */ | |
88409aaf CW |
802 | public function getJoin() { |
803 | return $this->api->getJoin(); | |
19b53e5b C |
804 | } |
805 | ||
19b53e5b | 806 | /** |
88409aaf | 807 | * @return array |
19b53e5b | 808 | */ |
88409aaf CW |
809 | public function getGroupBy() { |
810 | return $this->api->getGroupBy(); | |
19b53e5b C |
811 | } |
812 | ||
813 | /** | |
814 | * @return array | |
815 | */ | |
88409aaf CW |
816 | public function getOrderBy() { |
817 | return $this->api->getOrderBy(); | |
19b53e5b C |
818 | } |
819 | ||
820 | /** | |
88409aaf | 821 | * @return mixed |
19b53e5b | 822 | */ |
88409aaf CW |
823 | public function getLimit() { |
824 | return $this->api->getLimit(); | |
19b53e5b C |
825 | } |
826 | ||
827 | /** | |
88409aaf | 828 | * @return mixed |
19b53e5b | 829 | */ |
88409aaf CW |
830 | public function getOffset() { |
831 | return $this->api->getOffset(); | |
19b53e5b C |
832 | } |
833 | ||
834 | /** | |
88409aaf | 835 | * @return \CRM_Utils_SQL_Select |
19b53e5b | 836 | */ |
88409aaf CW |
837 | public function getQuery() { |
838 | return $this->query; | |
19b53e5b C |
839 | } |
840 | ||
841 | /** | |
842 | * @return bool|string | |
843 | */ | |
844 | public function getCheckPermissions() { | |
88409aaf | 845 | return $this->api->getCheckPermissions(); |
19b53e5b C |
846 | } |
847 | ||
9b06167d | 848 | /** |
88409aaf CW |
849 | * @param string $path |
850 | * @param array $field | |
9b06167d CW |
851 | */ |
852 | private function addSpecField($path, $field) { | |
853 | // Only add field to spec if we have permission | |
88409aaf | 854 | if ($this->getCheckPermissions() && !empty($field['permission']) && !\CRM_Core_Permission::check($field['permission'])) { |
9b06167d CW |
855 | $this->apiFieldSpec[$path] = FALSE; |
856 | return; | |
857 | } | |
9b06167d CW |
858 | $this->apiFieldSpec[$path] = $field; |
859 | } | |
860 | ||
88409aaf CW |
861 | /** |
862 | * Add something to the api's debug output if debugging is enabled | |
863 | * | |
864 | * @param $key | |
865 | * @param $item | |
866 | */ | |
867 | public function debug($key, $item) { | |
868 | if ($this->api->getDebug()) { | |
869 | $this->api->_debugOutput[$key][] = $item; | |
870 | } | |
871 | } | |
872 | ||
19b53e5b | 873 | } |