Add is empty filter to search / api

[civicrm-core.git] / Civi / Api4 / Query / Api4SelectQuery.php

diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php
index 49a3760b3141c2e53203364d276bed75fa88a2dc..e588e710853bc946af3c6f5631f5c3338b1c447c 100644 (file)
--- a/Civi/Api4/Query/Api4SelectQuery.php
+++ b/Civi/Api4/Query/Api4SelectQuery.php
@@ -103,6 +103,9 @@ class Api4SelectQuery {
     // 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();
   }
 
   /**
@@ -113,8 +116,6 @@ class Api4SelectQuery {
    * @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();
@@ -152,7 +153,6 @@ class Api4SelectQuery {
    * @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()) {
@@ -275,19 +275,32 @@ class Api4SelectQuery {
       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");
     }
   }
@@ -421,7 +434,7 @@ class Api4SelectQuery {
         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);
@@ -434,6 +447,22 @@ class Api4SelectQuery {
       }
     }
 
+    $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:
@@ -455,11 +484,18 @@ class Api4SelectQuery {
       }
     }
 
-    $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]);
   }
 
   /**
@@ -524,7 +560,9 @@ class Api4SelectQuery {
     if ($strict && !$field) {
       throw new \API_Exception("Invalid field '$fieldName'");
     }
-    $this->apiFieldSpec[$expr] = $field;
+    if ($field) {
+      $this->apiFieldSpec[$expr] = $field;
+    }
     return $field;
   }