4 +--------------------------------------------------------------------+
5 | Copyright CiviCRM LLC. All rights reserved. |
7 | This work is published under the GNU AGPLv3 license with some |
8 | permitted exceptions and without any warranty. For full license |
9 | and copyright information, see https://civicrm.org/licensing |
10 +--------------------------------------------------------------------+
16 * @copyright CiviCRM LLC https://civicrm.org/licensing
20 namespace api\v
4\Action
;
22 use api\v
4\UnitTestCase
;
23 use Civi\Api4\Contact
;
29 class SqlExpressionTest
extends UnitTestCase
{
31 public function testSelectNull() {
32 Contact
::create()->addValue('first_name', 'bob')->setCheckPermissions(FALSE)->execute();
33 $result = Contact
::get()
34 ->addSelect('NULL AS nothing', 'NULL', 'NULL AS b*d char', 'first_name')
35 ->addWhere('first_name', '=', 'bob')
39 $this->assertNull($result['nothing']);
40 $this->assertNull($result['NULL']);
41 $this->assertNull($result['b_d_char']);
42 $this->assertEquals('bob', $result['first_name']);
43 $this->assertArrayNotHasKey('b*d char', $result);
46 public function testSelectNumbers() {
47 Contact
::create()->addValue('first_name', 'bob')->setCheckPermissions(FALSE)->execute();
48 $result = Contact
::get()
49 ->addSelect('first_name', 123, 45.678, '-55 AS neg')
50 ->addWhere('first_name', '=', 'bob')
54 $this->assertEquals('bob', $result['first_name']);
55 $this->assertEquals('123', $result['123']);
56 $this->assertEquals('-55', $result['neg']);
57 $this->assertEquals('45.678', $result['45_678']);
60 public function testSelectStrings() {
61 Contact
::create()->addValue('first_name', 'bob')->setCheckPermissions(FALSE)->execute();
62 $result = Contact
::get()
63 ->addSelect('first_name')
64 ->addSelect('"hello world" AS hi')
65 ->addSelect('"can\'t \"quote\"" AS quot')
66 ->addWhere('first_name', '=', 'bob')
70 $this->assertEquals('bob', $result['first_name']);
71 $this->assertEquals('hello world', $result['hi']);
72 $this->assertEquals('can\'t "quote"', $result['quot']);
75 public function testSelectAlias() {
78 ->addSelect('first_name AS bob')
81 catch (\API_Exception
$e) {
82 $msg = $e->getMessage();
84 $this->assertStringContainsString('alias', $msg);
87 ->addSelect('55 AS sort_name')
90 catch (\API_Exception
$e) {
91 $msg = $e->getMessage();
93 $this->assertStringContainsString('existing field name', $msg);
95 ->addSelect('55 AS ok_alias')
99 public function testSelectEquations() {
100 $contact = Contact
::create(FALSE)->addValue('first_name', 'bob')
101 ->addChain('email', Email
::create()->setValues(['email' => 'hello@example.com', 'contact_id' => '$id']))
102 ->execute()->first();
103 $result = Email
::get(FALSE)
105 'IF((contact_id.first_name = "bob"), "Yes", "No") AS is_bob',
106 'IF((contact_id.first_name != "fred"), "No", "Yes") AS is_fred',
108 '(5 > 11) AS five_greater_eleven',
109 '(5 <= 11) AS five_less_eleven',
110 '(1 BETWEEN 0 AND contact_id) AS is_between',
111 // These fields don't exist
112 '(illegal * stuff) AS illegal_stuff',
113 // This field will be null
114 '(hold_date + 5) AS null_plus_five',
116 ->addWhere('contact_id', '=', $contact['id'])
120 $this->assertEquals('Yes', $result['is_bob']);
121 $this->assertEquals('No', $result['is_fred']);
122 $this->assertEquals('55', $result['5_11']);
123 $this->assertFalse($result['five_greater_eleven']);
124 $this->assertTrue($result['five_less_eleven']);
125 $this->assertTrue($result['is_between']);
126 $this->assertArrayNotHasKey('illegal_stuff', $result);
127 $this->assertEquals('5', $result['null_plus_five']);