From e8a5372844942ed0764f02f1a02307877a62f94c Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Tue, 6 Apr 2021 23:38:10 -0400
Subject: [PATCH] SearchKit - Apply filters to ON clause of EXCLUDE joins

Filters usually get applied to the WHERE clause, but in the case of EXCLUDE joins,
they will never be available (because their results are excluded).
So it makes more sense to add them to the ON clause of the join itself.
---
 .../Civi/Api4/Action/SearchDisplay/Run.php    | 31 ++++++++++++++-----
 1 file changed, 24 insertions(+), 7 deletions(-)

diff --git a/ext/search/Civi/Api4/Action/SearchDisplay/Run.php b/ext/search/Civi/Api4/Action/SearchDisplay/Run.php
index 8316e1d881..22725aee4d 100644
--- a/ext/search/Civi/Api4/Action/SearchDisplay/Run.php
+++ b/ext/search/Civi/Api4/Action/SearchDisplay/Run.php
@@ -90,6 +90,7 @@ class Run extends \Civi\Api4\Generic\AbstractAction {
     }
     $entityName = $this->savedSearch['api_entity'];
     $apiParams =& $this->savedSearch['api_params'];
+    $apiParams += ['where' => []];
     $settings = $this->display['settings'];
     $page = NULL;
 
@@ -205,7 +206,23 @@ class Run extends \Civi\Api4\Generic\AbstractAction {
 
     $field = $this->getField($fieldName);
     // If field is not found it must be an aggregated column & belongs in the HAVING clause.
-    $clause = $field ? 'where' : 'having';
+    if (!$field) {
+      $this->savedSearch['api_params']['having'] = $this->savedSearch['api_params']['having'] ?? [];
+      $clause =& $this->savedSearch['api_params']['having'];
+    }
+    // If field belongs to an EXCLUDE join, it should be added as a join condition
+    else {
+      $prefix = strpos($fieldName, '.') ? explode('.', $fieldName)[0] : NULL;
+      foreach ($this->savedSearch['api_params']['join'] ?? [] as $idx => $join) {
+        if (($join[1] ?? 'LEFT') === 'EXCLUDE' && (explode(' AS ', $join[0])[1] ?? '') === $prefix) {
+          $clause =& $this->savedSearch['api_params']['join'][$idx];
+        }
+      }
+    }
+    // Default: add filter to WHERE clause
+    if (!isset($clause)) {
+      $clause =& $this->savedSearch['api_params']['where'];
+    }
 
     $dataType = $field['data_type'] ?? NULL;
 
@@ -214,25 +231,25 @@ class Run extends \Civi\Api4\Generic\AbstractAction {
       $value = array_filter($value, [$this, 'hasValue']);
       // Use IN if array does not contain operators as keys
       if (array_diff_key($value, array_flip(CoreUtil::getOperators()))) {
-        $this->savedSearch['api_params'][$clause][] = [$fieldName, 'IN', $value];
+        $clause[] = [$fieldName, 'IN', $value];
       }
       else {
         foreach ($value as $operator => $val) {
-          $this->savedSearch['api_params'][$clause][] = [$fieldName, $operator, $val];
+          $clause[] = [$fieldName, $operator, $val];
         }
       }
     }
     elseif (!empty($field['serialize'])) {
-      $this->savedSearch['api_params'][$clause][] = [$fieldName, 'CONTAINS', $value];
+      $clause[] = [$fieldName, 'CONTAINS', $value];
     }
     elseif (!empty($field['options']) || in_array($dataType, ['Integer', 'Boolean', 'Date', 'Timestamp'])) {
-      $this->savedSearch['api_params'][$clause][] = [$fieldName, '=', $value];
+      $clause[] = [$fieldName, '=', $value];
     }
     elseif ($prefixWithWildcard) {
-      $this->savedSearch['api_params'][$clause][] = [$fieldName, 'CONTAINS', $value];
+      $clause[] = [$fieldName, 'CONTAINS', $value];
     }
     else {
-      $this->savedSearch['api_params'][$clause][] = [$fieldName, 'LIKE', $value . '%'];
+      $clause[] = [$fieldName, 'LIKE', $value . '%'];
     }
   }
 
-- 
2.25.1