Commit | Line | Data |
---|---|---|
3176b04c CW |
1 | <?php |
2 | ||
3 | /* | |
4 | +--------------------------------------------------------------------+ | |
5 | | Copyright CiviCRM LLC. All rights reserved. | | |
6 | | | | |
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 | +--------------------------------------------------------------------+ | |
11 | */ | |
12 | ||
13 | /** | |
14 | * | |
15 | * @package CRM | |
16 | * @copyright CiviCRM LLC https://civicrm.org/licensing | |
3176b04c CW |
17 | */ |
18 | ||
19 | ||
20 | namespace api\v4\Action; | |
21 | ||
46f571dd | 22 | use api\v4\Api4TestBase; |
3176b04c | 23 | use Civi\Api4\Contact; |
f4138bc4 | 24 | use Civi\Api4\Email; |
46f571dd | 25 | use Civi\Test\TransactionalInterface; |
3176b04c CW |
26 | |
27 | /** | |
28 | * @group headless | |
29 | */ | |
46f571dd | 30 | class SqlExpressionTest extends Api4TestBase implements TransactionalInterface { |
3176b04c CW |
31 | |
32 | public function testSelectNull() { | |
33 | Contact::create()->addValue('first_name', 'bob')->setCheckPermissions(FALSE)->execute(); | |
34 | $result = Contact::get() | |
19fde02c | 35 | ->addSelect('NULL AS nothing', 'NULL', 'NULL AS b*d char', 'first_name') |
3176b04c CW |
36 | ->addWhere('first_name', '=', 'bob') |
37 | ->setLimit(1) | |
38 | ->execute() | |
39 | ->first(); | |
40 | $this->assertNull($result['nothing']); | |
41 | $this->assertNull($result['NULL']); | |
42 | $this->assertNull($result['b_d_char']); | |
19fde02c | 43 | $this->assertEquals('bob', $result['first_name']); |
3176b04c CW |
44 | $this->assertArrayNotHasKey('b*d char', $result); |
45 | } | |
46 | ||
47 | public function testSelectNumbers() { | |
48 | Contact::create()->addValue('first_name', 'bob')->setCheckPermissions(FALSE)->execute(); | |
49 | $result = Contact::get() | |
50 | ->addSelect('first_name', 123, 45.678, '-55 AS neg') | |
51 | ->addWhere('first_name', '=', 'bob') | |
52 | ->setLimit(1) | |
53 | ->execute() | |
54 | ->first(); | |
55 | $this->assertEquals('bob', $result['first_name']); | |
56 | $this->assertEquals('123', $result['123']); | |
57 | $this->assertEquals('-55', $result['neg']); | |
58 | $this->assertEquals('45.678', $result['45_678']); | |
59 | } | |
60 | ||
61 | public function testSelectStrings() { | |
62 | Contact::create()->addValue('first_name', 'bob')->setCheckPermissions(FALSE)->execute(); | |
63 | $result = Contact::get() | |
19fde02c | 64 | ->addSelect('first_name') |
3176b04c CW |
65 | ->addSelect('"hello world" AS hi') |
66 | ->addSelect('"can\'t \"quote\"" AS quot') | |
67 | ->addWhere('first_name', '=', 'bob') | |
68 | ->setLimit(1) | |
69 | ->execute() | |
70 | ->first(); | |
19fde02c | 71 | $this->assertEquals('bob', $result['first_name']); |
3176b04c CW |
72 | $this->assertEquals('hello world', $result['hi']); |
73 | $this->assertEquals('can\'t "quote"', $result['quot']); | |
74 | } | |
75 | ||
19fde02c CW |
76 | public function testSelectAlias() { |
77 | try { | |
78 | Contact::get() | |
79 | ->addSelect('first_name AS bob') | |
80 | ->execute(); | |
81 | } | |
82 | catch (\API_Exception $e) { | |
83 | $msg = $e->getMessage(); | |
84 | } | |
df347a8c | 85 | $this->assertStringContainsString('alias', $msg); |
19fde02c CW |
86 | try { |
87 | Contact::get() | |
88 | ->addSelect('55 AS sort_name') | |
89 | ->execute(); | |
90 | } | |
91 | catch (\API_Exception $e) { | |
92 | $msg = $e->getMessage(); | |
93 | } | |
df347a8c | 94 | $this->assertStringContainsString('existing field name', $msg); |
19fde02c CW |
95 | Contact::get() |
96 | ->addSelect('55 AS ok_alias') | |
97 | ->execute(); | |
98 | } | |
99 | ||
f4138bc4 CW |
100 | public function testSelectEquations() { |
101 | $contact = Contact::create(FALSE)->addValue('first_name', 'bob') | |
102 | ->addChain('email', Email::create()->setValues(['email' => 'hello@example.com', 'contact_id' => '$id'])) | |
103 | ->execute()->first(); | |
104 | $result = Email::get(FALSE) | |
105 | ->setSelect([ | |
106 | 'IF((contact_id.first_name = "bob"), "Yes", "No") AS is_bob', | |
107 | 'IF((contact_id.first_name != "fred"), "No", "Yes") AS is_fred', | |
108 | '(5 * 11)', | |
109 | '(5 > 11) AS five_greater_eleven', | |
110 | '(5 <= 11) AS five_less_eleven', | |
111 | '(1 BETWEEN 0 AND contact_id) AS is_between', | |
a2086e29 | 112 | // These fields don't exist |
f4138bc4 | 113 | '(illegal * stuff) AS illegal_stuff', |
a2086e29 CW |
114 | // This field will be null |
115 | '(hold_date + 5) AS null_plus_five', | |
f4138bc4 CW |
116 | ]) |
117 | ->addWhere('contact_id', '=', $contact['id']) | |
118 | ->setLimit(1) | |
119 | ->execute() | |
120 | ->first(); | |
121 | $this->assertEquals('Yes', $result['is_bob']); | |
122 | $this->assertEquals('No', $result['is_fred']); | |
123 | $this->assertEquals('55', $result['5_11']); | |
124 | $this->assertFalse($result['five_greater_eleven']); | |
125 | $this->assertTrue($result['five_less_eleven']); | |
126 | $this->assertTrue($result['is_between']); | |
127 | $this->assertArrayNotHasKey('illegal_stuff', $result); | |
a2086e29 | 128 | $this->assertEquals('5', $result['null_plus_five']); |
f4138bc4 CW |
129 | } |
130 | ||
3176b04c | 131 | } |