From e062cce34c597c761d473fc3d4914c500bad5ba0 Mon Sep 17 00:00:00 2001
From: Coleman Watts <coleman@civicrm.org>
Date: Wed, 3 Feb 2021 08:18:36 -0500
Subject: [PATCH] APIv4 - Fix bug when using relative date filters in ON clause
 of a join

---
 Civi/Api4/Query/Api4SelectQuery.php      |  9 +++-
 tests/phpunit/api/v4/Action/DateTest.php | 54 ++++++++++++++++++++++++
 2 files changed, 61 insertions(+), 2 deletions(-)

diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php
index c02568433a..20b8286e6d 100644
--- a/Civi/Api4/Query/Api4SelectQuery.php
+++ b/Civi/Api4/Query/Api4SelectQuery.php
@@ -412,9 +412,14 @@ class Api4SelectQuery {
       if (is_string($value)) {
         $valExpr = $this->getExpression($value);
         if ($fieldName && $valExpr->getType() === 'SqlString') {
-          FormattingUtil::formatInputValue($valExpr->expr, $fieldName, $this->apiFieldSpec[$fieldName], $operator);
+          $value = $valExpr->getExpr();
+          FormattingUtil::formatInputValue($value, $fieldName, $this->apiFieldSpec[$fieldName], $operator);
+          return \CRM_Core_DAO::createSQLFilter($fieldAlias, [$operator => $value]);
+        }
+        else {
+          $value = $valExpr->render($this->apiFieldSpec);
+          return sprintf('%s %s %s', $fieldAlias, $operator, $value);
         }
-        return sprintf('%s %s %s', $fieldAlias, $operator, $valExpr->render($this->apiFieldSpec));
       }
       elseif ($fieldName) {
         $field = $this->getField($fieldName);
diff --git a/tests/phpunit/api/v4/Action/DateTest.php b/tests/phpunit/api/v4/Action/DateTest.php
index 3bc2636d6b..4361d17dd3 100644
--- a/tests/phpunit/api/v4/Action/DateTest.php
+++ b/tests/phpunit/api/v4/Action/DateTest.php
@@ -21,6 +21,7 @@ namespace api\v4\Action;
 
 use Civi\Api4\Activity;
 use Civi\Api4\Contact;
+use Civi\Api4\Contribution;
 use Civi\Api4\Relationship;
 use api\v4\UnitTestCase;
 
@@ -143,4 +144,57 @@ class DateTest extends UnitTestCase {
     $this->assertNotContains($act[6], $result);
   }
 
+  public function testJoinOnRelativeDate() {
+    $c1 = Contact::create(FALSE)
+      ->addValue('first_name', 'Contributor')
+      ->addValue('last_name', 'One')
+      ->execute()
+      ->first()['id'];
+
+    // Contribution from last year
+    Contribution::create(FALSE)
+      ->addValue('contact_id', $c1)
+      ->addValue('receive_date', (date('Y') - 1) . '-06-01')
+      ->addValue('financial_type_id', 1)
+      ->addValue('total_amount', 12)
+      ->execute();
+
+    // Contribution from this year
+    Contribution::create(FALSE)
+      ->addValue('contact_id', $c1)
+      ->addValue('receive_date', date('Y') . '-06-01')
+      ->addValue('financial_type_id', 1)
+      ->addValue('total_amount', 6)
+      ->execute();
+
+    // Contribution from 2 years ago
+    Contribution::create(FALSE)
+      ->addValue('contact_id', $c1)
+      ->addValue('receive_date', (date('Y') - 2) . '-06-01')
+      ->addValue('financial_type_id', 1)
+      ->addValue('total_amount', 24)
+      ->execute();
+
+    // Find contribution from last year
+    $contact = \Civi\Api4\Contact::get()
+      ->addSelect('id', 'contribution.total_amount')
+      ->setJoin([
+        ['Contribution AS contribution', FALSE, NULL, ['contribution.receive_date', '=', '"previous.year"']],
+      ])
+      ->addWhere('id', '=', $c1)
+      ->execute();
+    $this->assertCount(1, $contact);
+    $this->assertEquals(12, $contact[0]['contribution.total_amount']);
+
+    // Find contributions not from last year
+    $contact = \Civi\Api4\Contact::get()
+      ->addSelect('id', 'contribution.total_amount')
+      ->setJoin([
+        ['Contribution AS contribution', FALSE, NULL, ['contribution.receive_date', '!=', '"previous.year"']],
+      ])
+      ->addWhere('id', '=', $c1)
+      ->execute();
+    $this->assertCount(2, $contact);
+  }
+
 }
-- 
2.25.1