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\Activity
;
24 use Civi\Api4\Contact
;
26 use Civi\Api4\EntityTag
;
33 class FkJoinTest
extends UnitTestCase
{
35 public function setUpHeadless() {
39 'civicrm_activity_contact',
41 $this->cleanup(['tablesToTruncate' => $relatedTables]);
42 $this->loadDataSet('DefaultDataSet');
44 return parent
::setUpHeadless();
48 * Fetch all phone call activities. Expects a single activity
49 * loaded from the data set.
51 public function testThreeLevelJoin() {
52 $results = Activity
::get(FALSE)
53 ->addWhere('activity_type_id:name', '=', 'Phone Call')
56 $this->assertCount(1, $results);
59 public function testOptionalJoin() {
60 // DefaultDataSet includes 2 phones for contact 1, 0 for contact 2.
61 // We'll add one for contact 2 as a red herring to make sure we only get back the correct ones.
63 ->setValues(['contact_id' => $this->getReference('test_contact_2')['id'], 'phone' => '123456'])
65 $contacts = Contact
::get(FALSE)
66 ->addJoin('Phone', FALSE)
67 ->addSelect('id', 'phone.phone')
68 ->addWhere('id', 'IN', [$this->getReference('test_contact_1')['id']])
69 ->addOrderBy('phone.id')
71 $this->assertCount(2, $contacts);
72 $this->assertEquals($this->getReference('test_contact_1')['id'], $contacts[0]['id']);
73 $this->assertEquals($this->getReference('test_contact_1')['id'], $contacts[1]['id']);
76 public function testRequiredJoin() {
77 // Joining with no condition
78 $contacts = Contact
::get(FALSE)
79 ->addSelect('id', 'phone.phone')
80 ->addJoin('Phone', TRUE)
81 ->addWhere('id', 'IN', [$this->getReference('test_contact_1')['id'], $this->getReference('test_contact_2')['id']])
82 ->addOrderBy('phone.id')
84 $this->assertCount(2, $contacts);
85 $this->assertEquals($this->getReference('test_contact_1')['id'], $contacts[0]['id']);
86 $this->assertEquals($this->getReference('test_contact_1')['id'], $contacts[1]['id']);
88 // Add is_primary condition, should result in only one record
89 $contacts = Contact
::get(FALSE)
90 ->addSelect('id', 'phone.phone', 'phone.location_type_id')
91 ->addJoin('Phone', TRUE, ['phone.is_primary', '=', TRUE])
92 ->addWhere('id', 'IN', [$this->getReference('test_contact_1')['id'], $this->getReference('test_contact_2')['id']])
93 ->addOrderBy('phone.id')
95 $this->assertCount(1, $contacts);
96 $this->assertEquals($this->getReference('test_contact_1')['id'], $contacts[0]['id']);
97 $this->assertEquals('+35355439483', $contacts[0]['phone.phone']);
98 $this->assertEquals('1', $contacts[0]['phone.location_type_id']);
101 public function testJoinToTheSameTableTwice() {
102 $cid1 = Contact
::create(FALSE)
103 ->addValue('first_name', 'Aaa')
104 ->addChain('email1', Email
::create()->setValues(['email' => 'yoohoo@yahoo.test', 'contact_id' => '$id', 'location_type_id:name' => 'Home']))
105 ->addChain('email2', Email
::create()->setValues(['email' => 'yahoo@yoohoo.test', 'contact_id' => '$id', 'location_type_id:name' => 'Work']))
109 $cid2 = Contact
::create(FALSE)
110 ->addValue('first_name', 'Bbb')
111 ->addChain('email1', Email
::create()->setValues(['email' => '1@test.test', 'contact_id' => '$id', 'location_type_id:name' => 'Home']))
112 ->addChain('email2', Email
::create()->setValues(['email' => '2@test.test', 'contact_id' => '$id', 'location_type_id:name' => 'Work']))
113 ->addChain('email3', Email
::create()->setValues(['email' => '3@test.test', 'contact_id' => '$id', 'location_type_id:name' => 'Other']))
117 $cid3 = Contact
::create(FALSE)
118 ->addValue('first_name', 'Ccc')
122 $contacts = Contact
::get(FALSE)
123 ->addSelect('id', 'first_name', 'any_email.email', 'any_email.location_type_id:name', 'any_email.is_primary', 'primary_email.email')
125 ['Email AS any_email', TRUE, NULL],
126 ['Email AS primary_email', FALSE, ['primary_email.is_primary', '=', TRUE]],
128 ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
129 ->addOrderBy('any_email.id')
132 $this->assertCount(5, $contacts);
133 $this->assertEquals('Home', $contacts[0]['any_email.location_type_id:name']);
134 $this->assertEquals('yoohoo@yahoo.test', $contacts[1]['primary_email.email']);
135 $this->assertEquals('1@test.test', $contacts[2]['primary_email.email']);
136 $this->assertEquals('1@test.test', $contacts[3]['primary_email.email']);
137 $this->assertEquals('1@test.test', $contacts[4]['primary_email.email']);
140 public function testBridgeJoinTags() {
141 $tag1 = Tag
::create()->setCheckPermissions(FALSE)
142 ->addValue('name', uniqid('join1'))
145 $tag2 = Tag
::create()->setCheckPermissions(FALSE)
146 ->addValue('name', uniqid('join2'))
149 $tag3 = Tag
::create()->setCheckPermissions(FALSE)
150 ->addValue('name', uniqid('join3'))
154 $cid1 = Contact
::create()->setCheckPermissions(FALSE)
155 ->addValue('first_name', 'Aaa')
156 ->addChain('tag1', EntityTag
::create()->setValues(['entity_id' => '$id', 'tag_id:name' => $tag1]))
157 ->addChain('tag2', EntityTag
::create()->setValues(['entity_id' => '$id', 'tag_id:name' => $tag2]))
160 $cid2 = Contact
::create()->setCheckPermissions(FALSE)
161 ->addValue('first_name', 'Bbb')
162 ->addChain('tag1', EntityTag
::create()->setValues(['entity_id' => '$id', 'tag_id:name' => $tag1]))
163 ->addChain('tag3', EntityTag
::create()->setValues(['entity_id' => '$id', 'tag_id:name' => $tag3]))
166 $cid3 = Contact
::create()->setCheckPermissions(FALSE)
167 ->addValue('first_name', 'Ccc')
171 $required = Contact
::get()->setCheckPermissions(FALSE)
172 ->addJoin('Tag', TRUE, 'EntityTag')
173 ->addSelect('first_name', 'tag.name')
174 ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
176 $this->assertCount(4, $required);
178 $optional = Contact
::get()->setCheckPermissions(FALSE)
179 ->addJoin('Tag', FALSE, 'EntityTag', ['tag.name', 'IN', [$tag1, $tag2, $tag3]])
180 ->addSelect('first_name', 'tag.name')
181 ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
183 $this->assertCount(5, $optional);
185 $grouped = Contact
::get()->setCheckPermissions(FALSE)
186 ->addJoin('Tag', FALSE, 'EntityTag', ['tag.name', 'IN', [$tag1, $tag3]])
187 ->addSelect('first_name', 'COUNT(tag.name) AS tags')
188 ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
190 ->execute()->indexBy('id');
191 $this->assertEquals(1, (int) $grouped[$cid1]['tags']);
192 $this->assertEquals(2, (int) $grouped[$cid2]['tags']);
193 $this->assertEquals(0, (int) $grouped[$cid3]['tags']);
195 $reverse = Tag
::get()->setCheckPermissions(FALSE)
196 ->addJoin('Contact', FALSE, 'EntityTag', ['contact.id', 'IN', [$cid1, $cid2, $cid3]])
198 ->addSelect('name', 'COUNT(contact.id) AS contacts')
199 ->execute()->indexBy('name');
200 $this->assertEquals(2, (int) $reverse[$tag1]['contacts']);
201 $this->assertEquals(1, (int) $reverse[$tag2]['contacts']);
202 $this->assertEquals(1, (int) $reverse[$tag3]['contacts']);