*/
public $forceSelectId = TRUE;
+ /**
+ * @var array
+ */
+ private $explicitJoins = [];
+
/**
* @param \Civi\Api4\Generic\DAOGetAction $apiGet
*/
// Add ACLs first to avoid redundant subclauses
$baoName = CoreUtil::getBAOFromApiName($this->getEntity());
$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();
}
/**
* @throws \CRM_Core_Exception
*/
public function getSql() {
- // Add explicit joins. Other joins implied by dot notation may be added later
- $this->addExplicitJoins();
$this->buildSelectClause();
$this->buildWhereClause();
$this->buildOrderBy();
* @throws \API_Exception
*/
public function getCount() {
- $this->addExplicitJoins();
$this->buildWhereClause();
// If no having or groupBy, we only need to select count
if (!$this->getHaving() && !$this->getGroupBy()) {
return strpos($item, '*') !== FALSE && strpos($item, '.') !== FALSE && strpos($item, '(') === FALSE && strpos($item, ' ') === FALSE;
});
- foreach ($wildFields as $item) {
- $pos = array_search($item, array_values($select));
- $this->autoJoinFK($item);
- $matches = SelectUtil::getMatchingFields($item, array_keys($this->apiFieldSpec));
+ foreach ($wildFields as $wildField) {
+ $pos = array_search($wildField, array_values($select));
+ // If the joined_entity.id isn't in the fieldspec already, autoJoinFK will attempt to add the entity.
+ $idField = substr($wildField, 0, strrpos($wildField, '.')) . '.id';
+ $this->autoJoinFK($idField);
+ $matches = SelectUtil::getMatchingFields($wildField, array_keys($this->apiFieldSpec));
array_splice($select, $pos, 1, $matches);
}
$select = array_unique($select);
if ($dir !== 'ASC' && $dir !== 'DESC') {
throw new \API_Exception("Invalid sort direction. Cannot order by $item $dir");
}
- $expr = $this->getExpression($item);
- $column = $expr->render($this->apiFieldSpec);
-
- // Use FIELD() function to sort on pseudoconstant values
- $suffix = strstr($item, ':');
- if ($suffix && $expr->getType() === 'SqlField') {
- $field = $this->getField($item);
- $options = FormattingUtil::getPseudoconstantList($field, substr($suffix, 1));
- if ($options) {
- asort($options);
- $column = "FIELD($column,'" . implode("','", array_keys($options)) . "')";
+
+ try {
+ $expr = $this->getExpression($item);
+ $column = $expr->render($this->apiFieldSpec);
+
+ // Use FIELD() function to sort on pseudoconstant values
+ $suffix = strstr($item, ':');
+ if ($suffix && $expr->getType() === 'SqlField') {
+ $field = $this->getField($item);
+ $options = FormattingUtil::getPseudoconstantList($field, substr($suffix, 1));
+ if ($options) {
+ asort($options);
+ $column = "FIELD($column,'" . implode("','", array_keys($options)) . "')";
+ }
+ }
+ }
+ // If the expression could not be rendered, it might be a field alias
+ catch (\API_Exception $e) {
+ if (!empty($this->selectAliases[$item])) {
+ $column = '`' . $item . '`';
+ }
+ else {
+ throw new \API_Exception("Invalid field '{$item}'");
}
}
+
$this->query->orderBy("$column $dir");
}
}
if ($fieldName && $valExpr->getType() === 'SqlString') {
$value = $valExpr->getExpr();
FormattingUtil::formatInputValue($value, $fieldName, $this->apiFieldSpec[$fieldName], $operator);
- return \CRM_Core_DAO::createSQLFilter($fieldAlias, [$operator => $value]);
+ return $this->createSQLClause($fieldAlias, $operator, $value, $this->apiFieldSpec[$fieldName]);
}
else {
$value = $valExpr->render($this->apiFieldSpec);
}
}
+ $sqlClause = $this->createSQLClause($fieldAlias, $operator, $value, $field ?? NULL);
+ if ($sqlClause === NULL) {
+ throw new \API_Exception("Invalid value in $type clause for '$expr'");
+ }
+ return $sqlClause;
+ }
+
+ /**
+ * @param string $fieldAlias
+ * @param string $operator
+ * @param mixed $value
+ * @param array|null $field
+ * @return array|string|NULL
+ * @throws \Exception
+ */
+ protected function createSQLClause($fieldAlias, $operator, $value, $field) {
if ($operator === 'CONTAINS') {
switch ($field['serialize'] ?? NULL) {
case \CRM_Core_DAO::SERIALIZE_JSON:
}
}
- $sql_clause = \CRM_Core_DAO::createSQLFilter($fieldAlias, [$operator => $value]);
- if ($sql_clause === NULL) {
- throw new \API_Exception("Invalid value in $type clause for '$expr'");
+ if ($operator === 'IS EMPTY' || $operator === 'IS NOT EMPTY') {
+ // If field is not a string or number, this will pass through and use IS NULL/IS NOT NULL
+ $operator = str_replace('EMPTY', 'NULL', $operator);
+ // For strings & numbers, create an OR grouping of empty value OR null
+ if (in_array($field['data_type'] ?? NULL, ['String', 'Integer', 'Float'], TRUE)) {
+ $emptyVal = $field['data_type'] === 'String' ? '""' : '0';
+ $isEmptyClause = $operator === 'IS NULL' ? "= $emptyVal OR" : "<> $emptyVal AND";
+ return "($fieldAlias $isEmptyClause $fieldAlias $operator)";
+ }
}
- return $sql_clause;
+
+ return \CRM_Core_DAO::createSQLFilter($fieldAlias, [$operator => $value]);
}
/**
if ($strict && !$field) {
throw new \API_Exception("Invalid field '$fieldName'");
}
- $this->apiFieldSpec[$expr] = $field;
+ if ($field) {
+ $this->apiFieldSpec[$expr] = $field;
+ }
return $field;
}
$field['sql_name'] = '`' . $alias . '`.`' . $field['column_name'] . '`';
$this->addSpecField($alias . '.' . $field['name'], $field);
}
+ $tableName = CoreUtil::getTableName($entity);
+ // Save join info to be retrieved by $this->getExplicitJoin()
+ $this->explicitJoins[$alias] = [
+ 'entity' => $entity,
+ 'table' => $tableName,
+ 'bridge' => NULL,
+ ];
+ // If the first condition is a string, it's the name of a bridge entity
if (!empty($join[0]) && is_string($join[0]) && \CRM_Utils_Rule::alphanumeric($join[0])) {
+ $this->explicitJoins[$alias]['bridge'] = $join[0];
$conditions = $this->getBridgeJoin($join, $entity, $alias);
}
else {
foreach (array_filter($join) as $clause) {
$conditions[] = $this->treeWalkClauses($clause, 'ON');
}
- $tableName = CoreUtil::getTableName($entity);
$this->join($side, $tableName, $alias, $conditions);
}
}
$joiner = \Civi::container()->get('joiner');
// 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.
array_pop($pathArray);
- $pathString = implode('.', $pathArray);
- if (!$joiner->canAutoJoin($this->getFrom(), $pathString)) {
+ try {
+ $joinPath = $joiner->autoJoin($this, $pathArray);
+ }
+ catch (\Exception $e) {
return;
}
-
- $joinPath = $joiner->join($this, $pathString);
-
$lastLink = array_pop($joinPath);
// Custom field names are already prefixed
return $this->api->getCheckPermissions();
}
+ /**
+ * @param string $alias
+ * @return array|NULL
+ */
+ public function getExplicitJoin($alias) {
+ return $this->explicitJoins[$alias] ?? NULL;
+ }
+
/**
* @param string $path
* @param array $field
projects / civicrm-core.git / blobdiff