APIv4 - Automatically coalesce potentially null field values in equations

[civicrm-core.git] / tests / phpunit / api / v4 / Action / SqlExpressionTest.php

diff --git a/tests/phpunit/api/v4/Action/SqlExpressionTest.php b/tests/phpunit/api/v4/Action/SqlExpressionTest.php
index 1de05f18e2043e059a9aaa0a9916b3b668df4ec7..402bb53ab3c89488d216f8e616b025a2507dd703 100644 (file)
--- a/tests/phpunit/api/v4/Action/SqlExpressionTest.php
+++ b/tests/phpunit/api/v4/Action/SqlExpressionTest.php
@@ -21,6 +21,7 @@ namespace api\v4\Action;
 
 use api\v4\UnitTestCase;
 use Civi\Api4\Contact;
+use Civi\Api4\Email;
 
 /**
  * @group headless
@@ -95,4 +96,35 @@ class SqlExpressionTest extends UnitTestCase {
       ->execute();
   }
 
+  public function testSelectEquations() {
+    $contact = Contact::create(FALSE)->addValue('first_name', 'bob')
+      ->addChain('email', Email::create()->setValues(['email' => 'hello@example.com', 'contact_id' => '$id']))
+      ->execute()->first();
+    $result = Email::get(FALSE)
+      ->setSelect([
+        'IF((contact_id.first_name = "bob"), "Yes", "No") AS is_bob',
+        'IF((contact_id.first_name != "fred"), "No", "Yes") AS is_fred',
+        '(5 * 11)',
+        '(5 > 11) AS five_greater_eleven',
+        '(5 <= 11) AS five_less_eleven',
+        '(1 BETWEEN 0 AND contact_id) AS is_between',
+        // These fields don't exist
+        '(illegal * stuff) AS illegal_stuff',
+        // This field will be null
+        '(hold_date + 5) AS null_plus_five',
+      ])
+      ->addWhere('contact_id', '=', $contact['id'])
+      ->setLimit(1)
+      ->execute()
+      ->first();
+    $this->assertEquals('Yes', $result['is_bob']);
+    $this->assertEquals('No', $result['is_fred']);
+    $this->assertEquals('55', $result['5_11']);
+    $this->assertFalse($result['five_greater_eleven']);
+    $this->assertTrue($result['five_less_eleven']);
+    $this->assertTrue($result['is_between']);
+    $this->assertArrayNotHasKey('illegal_stuff', $result);
+    $this->assertEquals('5', $result['null_plus_five']);
+  }
+
 }