From e4c5583da7a9acb3a264e51cafb49a2e14d88202 Mon Sep 17 00:00:00 2001
From: colemanw <coleman@civicrm.org>
Date: Tue, 12 Sep 2023 14:04:53 -0400
Subject: [PATCH] SearchKit - Show (some) self-reference joins

---
 ext/search_kit/Civi/Search/Admin.php | 32 +++++++++++++++++-----------
 1 file changed, 20 insertions(+), 12 deletions(-)

diff --git a/ext/search_kit/Civi/Search/Admin.php b/ext/search_kit/Civi/Search/Admin.php
index 066c31cb91..bcce70ae5b 100644
--- a/ext/search_kit/Civi/Search/Admin.php
+++ b/ext/search_kit/Civi/Search/Admin.php
@@ -334,21 +334,29 @@ class Admin {
 
             // For dynamic references getTargetEntities will return multiple targets; for normal joins this loop will only run once
             foreach ($reference->getTargetEntities() as $targetTable => $targetEntityName) {
-              if (!isset($allowedEntities[$targetEntityName]) || $targetEntityName === $entity['name']) {
+              if (
+                !isset($allowedEntities[$targetEntityName]) ||
+                // What to do with self-references? They're weird but sometimes useful.
+                // For now, only allowing it for dynamic columns since they're explicitly declared
+                // (e.g. a Note can be a comment on a Note), and only the 1-n join since n-1 can be done with implicit joins.
+                ($targetEntityName === $entity['name'] && !$dynamicCol)
+              ) {
                 continue;
               }
               $targetEntity = $allowedEntities[$targetEntityName];
-              // Add the straight 1-1 join
-              $alias = $entity['name'] . '_' . $targetEntityName . '_' . $keyField['name'];
-              $joins[$entity['name']][] = [
-                'label' => $entity['title'] . ' ' . ($dynamicCol ? $targetEntity['title'] : $keyField['label']),
-                'description' => '',
-                'entity' => $targetEntityName,
-                'conditions' => self::getJoinConditions($keyField['name'], $alias . '.' . $reference->getTargetKey(), $targetTable, $dynamicCol),
-                'defaults' => self::getJoinDefaults($alias, $targetEntity),
-                'alias' => $alias,
-                'multi' => FALSE,
-              ];
+              // Add the straight 1-1 join (but only if it's not a reference to itself, see above)
+              if ($targetEntityName !== $entity['name']) {
+                $alias = $entity['name'] . '_' . $targetEntityName . '_' . $keyField['name'];
+                $joins[$entity['name']][] = [
+                  'label' => $entity['title'] . ' ' . ($dynamicCol ? $targetEntity['title'] : $keyField['label']),
+                  'description' => '',
+                  'entity' => $targetEntityName,
+                  'conditions' => self::getJoinConditions($keyField['name'], $alias . '.' . $reference->getTargetKey(), $targetTable, $dynamicCol),
+                  'defaults' => self::getJoinDefaults($alias, $targetEntity),
+                  'alias' => $alias,
+                  'multi' => FALSE,
+                ];
+              }
               // Flip the conditions & add the reverse (1-n) join
               $alias = $targetEntityName . '_' . $entity['name'] . '_' . $keyField['name'];
               $joins[$targetEntityName][] = [
-- 
2.25.1