From 63fb7eed2fcdb1b680b78ac90777779781e9e954 Mon Sep 17 00:00:00 2001
From: colemanw <coleman@civicrm.org>
Date: Thu, 5 Oct 2023 14:01:25 -0400
Subject: [PATCH] DAO - Handle OR clauses in getDynamicFkAclClauses, improve
 sql formatting for readability

Follow-up to 918e583a146876fb42932a1d959796fd710b7b74 - this correctly joins sub-arrays using OR
---
 CRM/Contact/BAO/Query.php |  4 ++--
 CRM/Core/DAO.php          | 11 ++++++++++-
 CRM/Utils/SQL.php         |  2 +-
 CRM/Utils/SQL/Select.php  |  4 ++--
 4 files changed, 15 insertions(+), 6 deletions(-)

diff --git a/CRM/Contact/BAO/Query.php b/CRM/Contact/BAO/Query.php
index 23499c9219..57039fdb89 100644
--- a/CRM/Contact/BAO/Query.php
+++ b/CRM/Contact/BAO/Query.php
@@ -583,10 +583,10 @@ class CRM_Contact_BAO_Query {
       // Unit test coverage in api_v3_FinancialTypeACLTest::testGetACLContribution.
       $clauses = [];
       if ($component === 'contribution') {
-        $clauses = CRM_Contribute_BAO_Contribution::getSelectWhereClause();
+        $clauses = array_filter(CRM_Contribute_BAO_Contribution::getSelectWhereClause());
       }
       if ($component === 'membership') {
-        $clauses = CRM_Member_BAO_Membership::getSelectWhereClause();
+        $clauses = array_filter(CRM_Member_BAO_Membership::getSelectWhereClause());
       }
       if ($clauses) {
         $this->_whereClause .= ' AND ' . implode(' AND ', $clauses);
diff --git a/CRM/Core/DAO.php b/CRM/Core/DAO.php
index bd7147725b..027ea8c5ab 100644
--- a/CRM/Core/DAO.php
+++ b/CRM/Core/DAO.php
@@ -3142,7 +3142,16 @@ SELECT contact_id
       // Prevent infinite recursion
       $subquery = $table === static::getTableName() ? NULL : CRM_Utils_SQL::mergeSubquery($ent);
       if ($subquery) {
-        $relatedClauses[] = "= '$table' AND {{$entityIdField}} " . implode(" AND {{$entityIdField}} ", $subquery);
+        foreach ($subquery as $index => $condition) {
+          // Join OR clauses
+          if (is_array($condition)) {
+            $subquery[$index] = "(({{$entityIdField}} " . implode(") OR ({{$entityIdField}} ", $condition) . '))';
+          }
+          else {
+            $subquery[$index] = "{{$entityIdField}} $condition";
+          }
+        }
+        $relatedClauses[] = "= '$table' AND " . implode(" AND ", $subquery);
       }
       // If it's the only value with no conditions, don't need to add it
       elseif (!$entityTableValues || count($relatedEntities) > 1) {
diff --git a/CRM/Utils/SQL.php b/CRM/Utils/SQL.php
index 5f7311d609..2f5e078432 100644
--- a/CRM/Utils/SQL.php
+++ b/CRM/Utils/SQL.php
@@ -71,7 +71,7 @@ class CRM_Utils_SQL {
           }
           // Arrays of arrays get joined with OR (similar to CRM_Core_Permission::check)
           elseif (is_array($formattedClause)) {
-            $subClauses[] = "($fieldName " . implode(" OR $fieldName ", $formattedClause) . ')';
+            $subClauses[] = "(($fieldName " . implode(") OR ($fieldName ", $formattedClause) . '))';
           }
           else {
             $subClauses[] = "$fieldName $formattedClause";
diff --git a/CRM/Utils/SQL/Select.php b/CRM/Utils/SQL/Select.php
index 2e93386531..3ffcb18a43 100644
--- a/CRM/Utils/SQL/Select.php
+++ b/CRM/Utils/SQL/Select.php
@@ -581,13 +581,13 @@ class CRM_Utils_SQL_Select extends CRM_Utils_SQL_BaseParamQuery {
       $sql .= $join . "\n";
     }
     if ($this->wheres) {
-      $sql .= 'WHERE (' . implode(') AND (', $this->wheres) . ")\n";
+      $sql .= 'WHERE (' . implode(")\n  AND (", $this->wheres) . ")\n";
     }
     if ($this->groupBys) {
       $sql .= 'GROUP BY ' . implode(', ', $this->groupBys) . "\n";
     }
     if ($this->havings) {
-      $sql .= 'HAVING (' . implode(') AND (', $this->havings) . ")\n";
+      $sql .= 'HAVING (' . implode(")\n  AND (", $this->havings) . ")\n";
     }
     if ($this->orderBys) {
       $orderBys = CRM_Utils_Array::crmArraySortByField($this->orderBys,
-- 
2.25.1