namespace Civi\Api4\Query;
use Civi\API\SelectQuery;
-use Civi\Api4\Event\Events;
-use Civi\Api4\Event\PostSelectQueryEvent;
use Civi\Api4\Service\Schema\Joinable\CustomGroupJoinable;
-use Civi\Api4\Service\Schema\Joinable\Joinable;
use Civi\Api4\Utils\FormattingUtil;
use Civi\Api4\Utils\CoreUtil;
use Civi\Api4\Utils\SelectUtil;
protected $apiVersion = 4;
/**
- * @var \Civi\Api4\Service\Schema\Joinable\Joinable[]
- * The joinable tables that have been joined so far
+ * @var array
+ * [alias => expr][]
*/
- protected $joinedTables = [];
+ protected $selectAliases = [];
/**
* If set to an array, this will start collecting debug info.
*/
public $groupBy = [];
+ public $forceSelectId = TRUE;
+
+ /**
+ * @var array
+ */
+ public $having = [];
+
/**
* @param \Civi\Api4\Generic\DAOGetAction $apiGet
*/
$this->orderBy = $apiGet->getOrderBy();
$this->limit = $apiGet->getLimit();
$this->offset = $apiGet->getOffset();
+ $this->having = $apiGet->getHaving();
+ // Always select ID of main table unless grouping is used
+ $this->forceSelectId = !$this->groupBy;
if ($apiGet->getDebug()) {
$this->debugOutput =& $apiGet->_debugOutput;
}
// Add ACLs first to avoid redundant subclauses
$this->query->where($this->getAclClause(self::MAIN_TABLE_ALIAS, $baoName));
+
+ // Add explicit joins. Other joins implied by dot notation may be added later
+ $this->addExplicitJoins($apiGet->getJoin());
}
/**
$this->buildOrderBy();
$this->buildLimit();
$this->buildGroupBy();
+ $this->buildHavingClause();
return $this->query->toSQL();
}
$this->debugOutput['sql'][] = $sql;
}
$query = \CRM_Core_DAO::executeQuery($sql);
- $i = 0;
while ($query->fetch()) {
- $id = $query->id ?? $i++;
if (in_array('row_count', $this->select)) {
$results[]['row_count'] = (int) $query->c;
break;
}
- $results[$id] = [];
- foreach ($this->select as $alias) {
+ $result = [];
+ foreach ($this->selectAliases as $alias => $expr) {
$returnName = $alias;
- if ($this->isOneToOneField($alias)) {
- $alias = str_replace('.', '_', $alias);
- $results[$id][$returnName] = property_exists($query, $alias) ? $query->$alias : NULL;
- }
+ $alias = str_replace('.', '_', $alias);
+ $result[$returnName] = property_exists($query, $alias) ? $query->$alias : NULL;
}
+ $results[] = $result;
}
- $event = new PostSelectQueryEvent($results, $this);
- \Civi::dispatcher()->dispatch(Events::POST_SELECT_QUERY, $event);
-
- return $event->getResults();
+ FormattingUtil::formatOutputValues($results, $this->getApiFieldSpec(), $this->getEntity());
+ return $results;
}
protected function buildSelectClause() {
return;
}
else {
- // Always select ID (unless we're doing groupBy).
- if (!$this->groupBy) {
+ if ($this->forceSelectId) {
$this->select = array_merge(['id'], $this->select);
}
// Expand wildcards in joins (the api wrapper already expanded non-joined wildcards)
$wildFields = array_filter($this->select, function($item) {
- return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE;
+ return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE && strpos($item, '(') === FALSE && strpos($item, ' ') === FALSE;
});
foreach ($wildFields as $item) {
$pos = array_search($item, array_values($this->select));
- $this->joinFK($item);
+ $this->autoJoinFK($item);
$matches = SelectUtil::getMatchingFields($item, array_keys($this->apiFieldSpec));
array_splice($this->select, $pos, 1, $matches);
}
$this->select = array_unique($this->select);
}
- foreach ($this->select as $fieldName) {
- $field = $this->getField($fieldName);
- // Remove unknown fields without raising an error
- if (!$field) {
- $this->select = array_diff($this->select, [$fieldName]);
- if (is_array($this->debugOutput)) {
- $this->debugOutput['undefined_fields'][] = $fieldName;
+ foreach ($this->select as $item) {
+ $expr = SqlExpression::convert($item, TRUE);
+ $valid = TRUE;
+ foreach ($expr->getFields() as $fieldName) {
+ $field = $this->getField($fieldName);
+ // Remove expressions with unknown fields without raising an error
+ if (!$field) {
+ $this->select = array_diff($this->select, [$item]);
+ if (is_array($this->debugOutput)) {
+ $this->debugOutput['undefined_fields'][] = $fieldName;
+ }
+ $valid = FALSE;
}
}
- elseif ($field['is_many']) {
- continue;
- }
- elseif ($field) {
- $this->query->select($field['sql_name'] . " AS `$fieldName`");
+ if ($valid) {
+ $alias = $expr->getAlias();
+ if ($alias != $expr->getExpr() && isset($this->apiFieldSpec[$alias])) {
+ throw new \API_Exception('Cannot use existing field name as alias');
+ }
+ $this->selectAliases[$alias] = $expr->getExpr();
+ $this->query->select($expr->render($this->apiFieldSpec) . " AS `$alias`");
}
}
}
*/
protected function buildWhereClause() {
foreach ($this->where as $clause) {
- $sql_clause = $this->treeWalkWhereClause($clause);
- $this->query->where($sql_clause);
+ $this->query->where($this->treeWalkClauses($clause, 'WHERE'));
+ }
+ }
+
+ /**
+ * Build HAVING clause.
+ *
+ * Every expression referenced must also be in the SELECT clause.
+ */
+ protected function buildHavingClause() {
+ foreach ($this->having as $clause) {
+ $this->query->having($this->treeWalkClauses($clause, 'HAVING'));
}
}
* @inheritDoc
*/
protected function buildOrderBy() {
- foreach ($this->orderBy as $fieldName => $dir) {
+ foreach ($this->orderBy as $item => $dir) {
if ($dir !== 'ASC' && $dir !== 'DESC') {
- throw new \API_Exception("Invalid sort direction. Cannot order by $fieldName $dir");
+ throw new \API_Exception("Invalid sort direction. Cannot order by $item $dir");
}
- $this->query->orderBy($this->getField($fieldName, TRUE)['sql_name'] . " $dir");
+ $this->query->orderBy($this->renderExpression($item) . " $dir");
}
}
*/
protected function buildLimit() {
if (!empty($this->limit) || !empty($this->offset)) {
- $this->query->limit($this->limit, $this->offset);
+ // If limit is 0, mysql will actually return 0 results. Instead set to maximum possible.
+ $this->query->limit($this->limit ?: '18446744073709551615', $this->offset);
}
}
/**
- *
+ * Adds GROUP BY clause to query
*/
protected function buildGroupBy() {
- foreach ($this->groupBy as $field) {
- if ($this->isOneToOneField($field) && $this->getField($field)) {
- $this->query->groupBy($field['sql_name']);
- }
- else {
- throw new \API_Exception("Invalid field. Cannot group by $field");
- }
+ foreach ($this->groupBy as $item) {
+ $this->query->groupBy($this->renderExpression($item));
}
}
* Recursively validate and transform a branch or leaf clause array to SQL.
*
* @param array $clause
+ * @param string $type
+ * WHERE|HAVING|ON
* @return string SQL where clause
*
- * @uses validateClauseAndComposeSql() to generate the SQL etc.
- * @todo if an 'and' is nested within and 'and' (or or-in-or) then should
- * flatten that to be a single list of clauses.
+ * @throws \API_Exception
+ * @uses composeClause() to generate the SQL etc.
*/
- protected function treeWalkWhereClause($clause) {
+ protected function treeWalkClauses($clause, $type) {
switch ($clause[0]) {
case 'OR':
case 'AND':
// handle branches
if (count($clause[1]) === 1) {
// a single set so AND|OR is immaterial
- return $this->treeWalkWhereClause($clause[1][0]);
+ return $this->treeWalkClauses($clause[1][0], $type);
}
else {
$sql_subclauses = [];
foreach ($clause[1] as $subclause) {
- $sql_subclauses[] = $this->treeWalkWhereClause($subclause);
+ $sql_subclauses[] = $this->treeWalkClauses($subclause, $type);
}
return '(' . implode("\n" . $clause[0], $sql_subclauses) . ')';
}
if (!is_string($clause[1][0])) {
$clause[1] = ['AND', $clause[1]];
}
- return 'NOT (' . $this->treeWalkWhereClause($clause[1]) . ')';
+ return 'NOT (' . $this->treeWalkClauses($clause[1], $type) . ')';
default:
- return $this->validateClauseAndComposeSql($clause);
+ return $this->composeClause($clause, $type);
}
}
/**
* Validate and transform a leaf clause array to SQL.
* @param array $clause [$fieldName, $operator, $criteria]
+ * @param string $type
+ * WHERE|HAVING|ON
* @return string SQL
* @throws \API_Exception
* @throws \Exception
*/
- protected function validateClauseAndComposeSql($clause) {
+ protected function composeClause(array $clause, string $type) {
// Pad array for unary operators
- list($fieldName, $operator, $value) = array_pad($clause, 3, NULL);
- $field = $this->getField($fieldName, TRUE);
+ list($expr, $operator, $value) = array_pad($clause, 3, NULL);
+ if (!in_array($operator, \CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
+ throw new \API_Exception('Illegal operator');
+ }
- FormattingUtil::formatInputValue($value, $field, $this->getEntity());
+ // For WHERE clause, expr must be the name of a field.
+ if ($type === 'WHERE') {
+ $field = $this->getField($expr, TRUE);
+ FormattingUtil::formatInputValue($value, $expr, $field);
+ $fieldAlias = $field['sql_name'];
+ }
+ // For HAVING, expr must be an item in the SELECT clause
+ elseif ($type === 'HAVING') {
+ // Expr references a fieldName or alias
+ if (isset($this->selectAliases[$expr])) {
+ $fieldAlias = $expr;
+ // Attempt to format if this is a real field
+ if (isset($this->apiFieldSpec[$expr])) {
+ FormattingUtil::formatInputValue($value, $expr, $this->apiFieldSpec[$expr]);
+ }
+ }
+ // Expr references a non-field expression like a function; convert to alias
+ elseif (in_array($expr, $this->selectAliases)) {
+ $fieldAlias = array_search($expr, $this->selectAliases);
+ }
+ // If either the having or select field contains a pseudoconstant suffix, match and perform substitution
+ else {
+ list($fieldName) = explode(':', $expr);
+ foreach ($this->selectAliases as $selectAlias => $selectExpr) {
+ list($selectField) = explode(':', $selectAlias);
+ if ($selectAlias === $selectExpr && $fieldName === $selectField && isset($this->apiFieldSpec[$fieldName])) {
+ FormattingUtil::formatInputValue($value, $expr, $this->apiFieldSpec[$fieldName]);
+ $fieldAlias = $selectAlias;
+ break;
+ }
+ }
+ }
+ if (!isset($fieldAlias)) {
+ throw new \API_Exception("Invalid expression in HAVING clause: '$expr'. Must use a value from SELECT clause.");
+ }
+ $fieldAlias = '`' . $fieldAlias . '`';
+ }
+ elseif ($type === 'ON') {
+ $expr = $this->getExpression($expr);
+ $fieldName = count($expr->getFields()) === 1 ? $expr->getFields()[0] : NULL;
+ $fieldAlias = $expr->render($this->apiFieldSpec);
+ if (is_string($value)) {
+ $valExpr = $this->getExpression($value);
+ if ($fieldName && $valExpr->getType() === 'SqlString') {
+ FormattingUtil::formatInputValue($valExpr->expr, $fieldName, $this->apiFieldSpec[$fieldName]);
+ }
+ return sprintf('%s %s %s', $fieldAlias, $operator, $valExpr->render($this->apiFieldSpec));
+ }
+ elseif ($fieldName) {
+ FormattingUtil::formatInputValue($value, $fieldName, $this->apiFieldSpec[$fieldName]);
+ }
+ }
- $sql_clause = \CRM_Core_DAO::createSQLFilter($field['sql_name'], [$operator => $value]);
+ $sql_clause = \CRM_Core_DAO::createSQLFilter($fieldAlias, [$operator => $value]);
if ($sql_clause === NULL) {
- throw new \API_Exception("Invalid value in where clause for field '$fieldName'");
+ throw new \API_Exception("Invalid value in $type clause for '$expr'");
}
return $sql_clause;
}
+ /**
+ * @param string $expr
+ * @return SqlExpression
+ * @throws \API_Exception
+ */
+ protected function getExpression(string $expr) {
+ $sqlExpr = SqlExpression::convert($expr);
+ foreach ($sqlExpr->getFields() as $fieldName) {
+ $this->getField($fieldName, TRUE);
+ }
+ return $sqlExpr;
+ }
+
+ /**
+ * @param string $expr
+ * @return string
+ * @throws \API_Exception
+ */
+ protected function renderExpression(string $expr) {
+ $sqlExpr = $this->getExpression($expr);
+ return $sqlExpr->render($this->apiFieldSpec);
+ }
+
/**
* @inheritDoc
*/
/**
* Fetch a field from the getFields list
*
- * @param string $fieldName
+ * @param string $expr
* @param bool $strict
+ * In strict mode, this will throw an exception if the field doesn't exist
*
* @return string|null
* @throws \API_Exception
*/
- public function getField($fieldName, $strict = FALSE) {
+ public function getField($expr, $strict = FALSE) {
+ // If the expression contains a pseudoconstant filter like activity_type_id:label,
+ // strip it to look up the base field name, then add the field:filter key to apiFieldSpec
+ $col = strpos($expr, ':');
+ $fieldName = $col ? substr($expr, 0, $col) : $expr;
// Perform join if field not yet available - this will add it to apiFieldSpec
if (!isset($this->apiFieldSpec[$fieldName]) && strpos($fieldName, '.')) {
- $this->joinFK($fieldName);
+ $this->autoJoinFK($fieldName);
}
$field = $this->apiFieldSpec[$fieldName] ?? NULL;
- if ($field) {
- return $field;
- }
- elseif ($strict) {
+ if ($strict && !$field) {
throw new \API_Exception("Invalid field '$fieldName'");
}
- return NULL;
+ $this->apiFieldSpec[$expr] = $field;
+ return $field;
}
/**
- * Joins a path and adds all fields in the joined eneity to apiFieldSpec
+ * Join onto other entities as specified by the api call.
+ *
+ * @param $joins
+ * @throws \API_Exception
+ * @throws \Civi\API\Exception\NotImplementedException
+ */
+ private function addExplicitJoins($joins) {
+ foreach ($joins as $join) {
+ // First item in the array is the entity name
+ $entity = array_shift($join);
+ // Which might contain an alias. Split on the keyword "AS"
+ list($entity, $alias) = array_pad(explode(' AS ', $entity), 2, NULL);
+ // Ensure alias is a safe string, and supply default if not given
+ $alias = $alias ? \CRM_Utils_String::munge($alias) : strtolower($entity);
+ // First item in the array is a boolean indicating if the join is required (aka INNER or LEFT).
+ // The rest are join conditions.
+ $side = array_shift($join) ? 'INNER' : 'LEFT';
+ $joinEntityGet = \Civi\API\Request::create($entity, 'get', ['version' => 4, 'checkPermissions' => $this->checkPermissions]);
+ foreach ($joinEntityGet->entityFields() as $field) {
+ $field['sql_name'] = '`' . $alias . '`.`' . $field['column_name'] . '`';
+ $field['is_join'] = TRUE;
+ $this->addSpecField($alias . '.' . $field['name'], $field);
+ }
+ $conditions = [];
+ foreach (array_merge($join, $this->getJoinConditions($entity, $alias)) as $clause) {
+ $conditions[] = $this->treeWalkClauses($clause, 'ON');
+ }
+ $tableName = AllCoreTables::getTableForEntityName($entity);
+ $this->join($side, $tableName, $alias, $conditions);
+ }
+ }
+
+ /**
+ * Supply conditions for an explicit join.
+ *
+ * @param $entity
+ * @param $alias
+ * @return array
+ */
+ private function getJoinConditions($entity, $alias) {
+ $conditions = [];
+ // getAclClause() expects a stack of 1-to-1 join fields to help it dedupe, but this is more flexible,
+ // so unless this is a direct 1-to-1 join with the main entity, we'll just hack it
+ // with a padded empty stack to bypass its deduping.
+ $stack = [NULL, NULL];
+ foreach ($this->apiFieldSpec as $name => $field) {
+ if ($field['entity'] !== $entity && $field['fk_entity'] === $entity) {
+ $conditions[] = [$name, '=', "$alias.id"];
+ $stack = [$name];
+ }
+ elseif (strpos($name, "$alias.") === 0 && substr_count($name, '.') === 1 && $field['fk_entity'] === $this->entity) {
+ $conditions[] = [$name, '=', 'id'];
+ }
+ }
+ // 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
+ if (count($conditions) > 1) {
+ return $this->getAclClause($alias, AllCoreTables::getFullName($entity), [NULL, NULL]);
+ }
+ $acls = $this->getAclClause($alias, AllCoreTables::getFullName($entity), $stack);
+ return array_merge($acls, $conditions);
+ }
+
+ /**
+ * Joins a path and adds all fields in the joined entity to apiFieldSpec
*
* @param $key
* @throws \API_Exception
* @throws \Exception
*/
- protected function joinFK($key) {
+ protected function autoJoinFK($key) {
if (isset($this->apiFieldSpec[$key])) {
return;
}
array_pop($pathArray);
$pathString = implode('.', $pathArray);
- if (!$joiner->canJoin($this, $pathString)) {
+ if (!$joiner->canAutoJoin($this->getFrom(), $pathString)) {
return;
}
$joinPath = $joiner->join($this, $pathString);
- $isMany = FALSE;
- foreach ($joinPath as $joinable) {
- if ($joinable->getJoinType() === Joinable::JOIN_TYPE_ONE_TO_MANY) {
- $isMany = TRUE;
- }
- }
-
- /** @var \Civi\Api4\Service\Schema\Joinable\Joinable $lastLink */
$lastLink = array_pop($joinPath);
// Custom field names are already prefixed
$fieldArray['sql_name'] = '`' . $lastLink->getAlias() . '`.`' . $fieldArray['column_name'] . '`';
$fieldArray['is_custom'] = $isCustom;
$fieldArray['is_join'] = TRUE;
- $fieldArray['is_many'] = $isMany;
$this->addSpecField($prefix . $fieldArray['name'], $fieldArray);
}
}
- /**
- * @param \Civi\Api4\Service\Schema\Joinable\Joinable $joinable
- *
- * @return $this
- */
- public function addJoinedTable(Joinable $joinable) {
- $this->joinedTables[] = $joinable;
-
- return $this;
- }
-
/**
* @return FALSE|string
*/
return $this->selectFields;
}
- /**
- * @return bool
- */
- public function isFillUniqueFields() {
- return $this->isFillUniqueFields;
- }
-
/**
* @return \CRM_Utils_SQL_Select
*/
return $this->apiVersion;
}
- /**
- * @return \Civi\Api4\Service\Schema\Joinable\Joinable[]
- */
- public function getJoinedTables() {
- return $this->joinedTables;
- }
-
- /**
- * @return \Civi\Api4\Service\Schema\Joinable\Joinable
- */
- public function getJoinedTable($alias) {
- foreach ($this->joinedTables as $join) {
- if ($join->getAlias() == $alias) {
- return $join;
- }
- }
- }
-
/**
* Get table name on basis of entity
*
}
}
- /**
- * Checks if a field either belongs to the main entity or is joinable 1-to-1.
- *
- * Used to determine if a field can be added to the SELECT of the main query,
- * or if it must be fetched post-query.
- *
- * @param string $fieldPath
- * @return bool
- */
- public function isOneToOneField(string $fieldPath) {
- return strpos($fieldPath, '.') === FALSE || !array_filter($this->getPathJoinTypes($fieldPath));
- }
-
- /**
- * Separates a string like 'emails.location_type.label' into an array, where
- * each value in the array tells whether it is 1-1 or 1-n join type
- *
- * @param string $pathString
- * Dot separated path to the field
- *
- * @return array
- * Index is table alias and value is boolean whether is 1-to-many join
- */
- public function getPathJoinTypes($pathString) {
- $pathParts = explode('.', $pathString);
- // remove field
- array_pop($pathParts);
- $path = [];
- $query = $this;
- $isMultipleChecker = function($alias) use ($query) {
- foreach ($query->getJoinedTables() as $table) {
- if ($table->getAlias() === $alias) {
- return $table->getJoinType() === Joinable::JOIN_TYPE_ONE_TO_MANY;
- }
- }
- return FALSE;
- };
-
- foreach ($pathParts as $part) {
- $path[$part] = $isMultipleChecker($part);
- }
-
- return $path;
- }
-
/**
* @param $path
* @param $field
return;
}
$defaults = [];
- $defaults['is_custom'] = $defaults['is_join'] = $defaults['is_many'] = FALSE;
+ $defaults['is_custom'] = $defaults['is_join'] = FALSE;
$field += $defaults;
$this->apiFieldSpec[$path] = $field;
}
projects / civicrm-core.git / blobdiff