use Civi\API\SelectQuery;
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
- */
- protected $joinedTables = [];
-
/**
* @var array
* [alias => expr][]
// 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());
}
/**
if ($dir !== 'ASC' && $dir !== 'DESC') {
throw new \API_Exception("Invalid sort direction. Cannot order by $item $dir");
}
- $expr = SqlExpression::convert($item);
- foreach ($expr->getFields() as $fieldName) {
- $this->getField($fieldName, TRUE);
- }
- $this->query->orderBy($expr->render($this->apiFieldSpec) . " $dir");
+ $this->query->orderBy($this->renderExpression($item) . " $dir");
}
}
*/
protected function buildGroupBy() {
foreach ($this->groupBy as $item) {
- $expr = SqlExpression::convert($item);
- foreach ($expr->getFields() as $fieldName) {
- $this->getField($fieldName, TRUE);
- }
- $this->query->groupBy($expr->render($this->apiFieldSpec));
+ $this->query->groupBy($this->renderExpression($item));
}
}
*
* @param array $clause
* @param string $type
- * WHERE|HAVING
+ * WHERE|HAVING|ON
* @return string SQL where clause
*
* @throws \API_Exception
* Validate and transform a leaf clause array to SQL.
* @param array $clause [$fieldName, $operator, $criteria]
* @param string $type
- * WHERE|HAVING
+ * WHERE|HAVING|ON
* @return string SQL
* @throws \API_Exception
* @throws \Exception
protected function composeClause(array $clause, string $type) {
// Pad array for unary operators
list($expr, $operator, $value) = array_pad($clause, 3, NULL);
+ if (!in_array($operator, \CRM_Core_DAO::acceptedSQLOperators(), TRUE)) {
+ throw new \API_Exception('Illegal operator');
+ }
// For WHERE clause, expr must be the name of a field.
if ($type === 'WHERE') {
$fieldAlias = $field['sql_name'];
}
// For HAVING, expr must be an item in the SELECT clause
- else {
+ elseif ($type === 'HAVING') {
// Expr references a fieldName or alias
if (isset($this->selectAliases[$expr])) {
$fieldAlias = $expr;
}
$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($fieldAlias, [$operator => $value]);
if ($sql_clause === NULL) {
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
*/
return $field;
}
+ /**
+ * 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 \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
*