tests/phpunit/api/v4/Action/FkJoinTest.php

   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
  17  */
  18
  19
  20 namespace api\v4\Action;
  21
  22 use api\v4\UnitTestCase;
  23 use Civi\Api4\Activity;
  24 use Civi\Api4\Contact;
  25 use Civi\Api4\Email;
  26 use Civi\Api4\EntityTag;
  27 use Civi\Api4\Phone;
  28 use Civi\Api4\Relationship;
  29 use Civi\Api4\Tag;
  30
  31 /**
  32  * @group headless
  33  */
  34 class FkJoinTest extends UnitTestCase {
  35
  36   public function setUpHeadless() {
  37     $relatedTables = [
  38       'civicrm_activity',
  39       'civicrm_phone',
  40       'civicrm_activity_contact',
  41     ];
  42     $this->cleanup(['tablesToTruncate' => $relatedTables]);
  43     $this->loadDataSet('DefaultDataSet');
  44
  45     return parent::setUpHeadless();
  46   }
  47
  48   /**
  49    * Fetch all phone call activities. Expects a single activity
  50    * loaded from the data set.
  51    */
  52   public function testThreeLevelJoin() {
  53     $results = Activity::get(FALSE)
  54       ->addWhere('activity_type_id:name', '=', 'Phone Call')
  55       ->execute();
  56
  57     $this->assertCount(1, $results);
  58   }
  59
  60   public function testOptionalJoin() {
  61     // DefaultDataSet includes 2 phones for contact 1, 0 for contact 2.
  62     // We'll add one for contact 2 as a red herring to make sure we only get back the correct ones.
  63     Phone::create(FALSE)
  64       ->setValues(['contact_id' => $this->getReference('test_contact_2')['id'], 'phone' => '123456'])
  65       ->execute();
  66     $contacts = Contact::get(FALSE)
  67       ->addJoin('Phone', FALSE)
  68       ->addSelect('id', 'phone.phone')
  69       ->addWhere('id', 'IN', [$this->getReference('test_contact_1')['id']])
  70       ->addOrderBy('phone.id')
  71       ->execute();
  72     $this->assertCount(2, $contacts);
  73     $this->assertEquals($this->getReference('test_contact_1')['id'], $contacts[0]['id']);
  74     $this->assertEquals($this->getReference('test_contact_1')['id'], $contacts[1]['id']);
  75   }
  76
  77   public function testRequiredJoin() {
  78     // Joining with no condition
  79     $contacts = Contact::get(FALSE)
  80       ->addSelect('id', 'phone.phone')
  81       ->addJoin('Phone', TRUE)
  82       ->addWhere('id', 'IN', [$this->getReference('test_contact_1')['id'], $this->getReference('test_contact_2')['id']])
  83       ->addOrderBy('phone.id')
  84       ->execute();
  85     $this->assertCount(2, $contacts);
  86     $this->assertEquals($this->getReference('test_contact_1')['id'], $contacts[0]['id']);
  87     $this->assertEquals($this->getReference('test_contact_1')['id'], $contacts[1]['id']);
  88
  89     // Add is_primary condition, should result in only one record
  90     $contacts = Contact::get(FALSE)
  91       ->addSelect('id', 'phone.phone', 'phone.location_type_id')
  92       ->addJoin('Phone', TRUE, ['phone.is_primary', '=', TRUE])
  93       ->addWhere('id', 'IN', [$this->getReference('test_contact_1')['id'], $this->getReference('test_contact_2')['id']])
  94       ->addOrderBy('phone.id')
  95       ->execute();
  96     $this->assertCount(1, $contacts);
  97     $this->assertEquals($this->getReference('test_contact_1')['id'], $contacts[0]['id']);
  98     $this->assertEquals('+35355439483', $contacts[0]['phone.phone']);
  99     $this->assertEquals('1', $contacts[0]['phone.location_type_id']);
 100   }
 101
 102   public function testJoinToTheSameTableTwice() {
 103     $cid1 = Contact::create(FALSE)
 104       ->addValue('first_name', 'Aaa')
 105       ->addChain('email1', Email::create()->setValues(['email' => 'yoohoo@yahoo.test', 'contact_id' => '$id', 'location_type_id:name' => 'Home']))
 106       ->addChain('email2', Email::create()->setValues(['email' => 'yahoo@yoohoo.test', 'contact_id' => '$id', 'location_type_id:name' => 'Work']))
 107       ->execute()
 108       ->first()['id'];
 109
 110     $cid2 = Contact::create(FALSE)
 111       ->addValue('first_name', 'Bbb')
 112       ->addChain('email1', Email::create()->setValues(['email' => '1@test.test', 'contact_id' => '$id', 'location_type_id:name' => 'Home']))
 113       ->addChain('email2', Email::create()->setValues(['email' => '2@test.test', 'contact_id' => '$id', 'location_type_id:name' => 'Work']))
 114       ->addChain('email3', Email::create()->setValues(['email' => '3@test.test', 'contact_id' => '$id', 'location_type_id:name' => 'Other']))
 115       ->execute()
 116       ->first()['id'];
 117
 118     $cid3 = Contact::create(FALSE)
 119       ->addValue('first_name', 'Ccc')
 120       ->execute()
 121       ->first()['id'];
 122
 123     $contacts = Contact::get(FALSE)
 124       ->addSelect('id', 'first_name', 'any_email.email', 'any_email.location_type_id:name', 'any_email.is_primary', 'primary_email.email')
 125       ->setJoin([
 126         ['Email AS any_email', TRUE, NULL],
 127         ['Email AS primary_email', FALSE, ['primary_email.is_primary', '=', TRUE]],
 128       ])
 129       ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
 130       ->addOrderBy('any_email.id')
 131       ->setDebug(TRUE)
 132       ->execute();
 133     $this->assertCount(5, $contacts);
 134     $this->assertEquals('Home', $contacts[0]['any_email.location_type_id:name']);
 135     $this->assertEquals('yoohoo@yahoo.test', $contacts[1]['primary_email.email']);
 136     $this->assertEquals('1@test.test', $contacts[2]['primary_email.email']);
 137     $this->assertEquals('1@test.test', $contacts[3]['primary_email.email']);
 138     $this->assertEquals('1@test.test', $contacts[4]['primary_email.email']);
 139   }
 140
 141   public function testBridgeJoinTags() {
 142     $tag1 = Tag::create()->setCheckPermissions(FALSE)
 143       ->addValue('name', uniqid('join1'))
 144       ->execute()
 145       ->first()['name'];
 146     $tag2 = Tag::create()->setCheckPermissions(FALSE)
 147       ->addValue('name', uniqid('join2'))
 148       ->execute()
 149       ->first()['name'];
 150     $tag3 = Tag::create()->setCheckPermissions(FALSE)
 151       ->addValue('name', uniqid('join3'))
 152       ->execute()
 153       ->first()['name'];
 154
 155     $cid1 = Contact::create()->setCheckPermissions(FALSE)
 156       ->addValue('first_name', 'Aaa')
 157       ->addChain('tag1', EntityTag::create()->setValues(['entity_id' => '$id', 'tag_id:name' => $tag1]))
 158       ->addChain('tag2', EntityTag::create()->setValues(['entity_id' => '$id', 'tag_id:name' => $tag2]))
 159       ->execute()
 160       ->first()['id'];
 161     $cid2 = Contact::create()->setCheckPermissions(FALSE)
 162       ->addValue('first_name', 'Bbb')
 163       ->addChain('tag1', EntityTag::create()->setValues(['entity_id' => '$id', 'tag_id:name' => $tag1]))
 164       ->addChain('tag3', EntityTag::create()->setValues(['entity_id' => '$id', 'tag_id:name' => $tag3]))
 165       ->execute()
 166       ->first()['id'];
 167     $cid3 = Contact::create()->setCheckPermissions(FALSE)
 168       ->addValue('first_name', 'Ccc')
 169       ->execute()
 170       ->first()['id'];
 171
 172     $required = Contact::get()->setCheckPermissions(FALSE)
 173       ->addJoin('Tag', TRUE, 'EntityTag')
 174       ->addSelect('first_name', 'tag.name')
 175       ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
 176       ->execute();
 177     $this->assertCount(4, $required);
 178
 179     $optional = Contact::get()->setCheckPermissions(FALSE)
 180       ->addJoin('Tag', FALSE, 'EntityTag', ['tag.name', 'IN', [$tag1, $tag2, $tag3]])
 181       ->addSelect('first_name', 'tag.name')
 182       ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
 183       ->execute();
 184     $this->assertCount(5, $optional);
 185
 186     $grouped = Contact::get()->setCheckPermissions(FALSE)
 187       ->addJoin('Tag', FALSE, 'EntityTag', ['tag.name', 'IN', [$tag1, $tag3]])
 188       ->addSelect('first_name', 'COUNT(tag.name) AS tags')
 189       ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
 190       ->addGroupBy('id')
 191       ->execute()->indexBy('id');
 192     $this->assertEquals(1, (int) $grouped[$cid1]['tags']);
 193     $this->assertEquals(2, (int) $grouped[$cid2]['tags']);
 194     $this->assertEquals(0, (int) $grouped[$cid3]['tags']);
 195
 196     $reverse = Tag::get()->setCheckPermissions(FALSE)
 197       ->addJoin('Contact', FALSE, 'EntityTag', ['contact.id', 'IN', [$cid1, $cid2, $cid3]])
 198       ->addGroupBy('id')
 199       ->addSelect('name', 'COUNT(contact.id) AS contacts')
 200       ->execute()->indexBy('name');
 201     $this->assertEquals(2, (int) $reverse[$tag1]['contacts']);
 202     $this->assertEquals(1, (int) $reverse[$tag2]['contacts']);
 203     $this->assertEquals(1, (int) $reverse[$tag3]['contacts']);
 204   }
 205
 206   public function testBridgeJoinRelationshipContactActivity() {
 207     $cid1 = Contact::create()->setCheckPermissions(FALSE)
 208       ->addValue('first_name', 'Aaa')
 209       ->addChain('activity', Activity::create()
 210         ->addValue('activity_type_id:name', 'Meeting')
 211         ->addValue('source_contact_id', '$id')
 212         ->addValue('target_contact_id', '$id')
 213       )
 214       ->execute()
 215       ->first()['id'];
 216     $cid2 = Contact::create()->setCheckPermissions(FALSE)
 217       ->addValue('first_name', 'Bbb')
 218       ->addChain('activity', Activity::create()
 219         ->addValue('activity_type_id:name', 'Phone Call')
 220         ->addValue('source_contact_id', $cid1)
 221         ->addValue('target_contact_id', '$id')
 222       )
 223       ->addChain('r1', Relationship::create()
 224         ->setValues(['contact_id_a' => '$id', 'contact_id_b' => $cid1, 'relationship_type_id' => 1])
 225       )
 226       ->execute()
 227       ->first()['id'];
 228     $cid3 = Contact::create()->setCheckPermissions(FALSE)
 229       ->addValue('first_name', 'Ccc')
 230       ->addChain('activity', Activity::create()
 231         ->addValue('activity_type_id:name', 'Meeting')
 232         ->addValue('source_contact_id', $cid1)
 233         ->addValue('target_contact_id', '$id')
 234       )
 235       ->addChain('activity2', Activity::create()
 236         ->addValue('activity_type_id:name', 'Phone Call')
 237         ->addValue('source_contact_id', $cid1)
 238         ->addValue('target_contact_id', '$id')
 239       )
 240       ->addChain('r1', Relationship::create()
 241         ->setValues(['contact_id_a' => '$id', 'contact_id_b' => $cid1, 'relationship_type_id' => 1])
 242       )
 243       ->addChain('r2', Relationship::create()
 244         ->setValues(['contact_id_a' => '$id', 'contact_id_b' => $cid2, 'relationship_type_id' => 2])
 245       )
 246       ->execute()
 247       ->first()['id'];
 248
 249     $result = Contact::get(FALSE)
 250       ->addSelect('id', 'act.id')
 251       ->addJoin('Activity AS act', TRUE, 'ActivityContact', ['act.record_type_id:name', '=', "'Activity Targets'"])
 252       ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
 253       ->execute();
 254     $this->assertCount(4, $result);
 255
 256     $result = Contact::get(FALSE)
 257       ->addSelect('id', 'act.id')
 258       ->addJoin('Activity AS act', TRUE, 'ActivityContact', ['act.activity_type_id:name', '=', "'Meeting'"], ['act.record_type_id:name', '=', "'Activity Targets'"])
 259       ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
 260       ->execute();
 261     $this->assertCount(2, $result);
 262
 263     $result = Activity::get(FALSE)
 264       ->addSelect('id', 'contact.id')
 265       ->addJoin('Contact', FALSE, 'ActivityContact')
 266       ->addWhere('contact.id', 'IN', [$cid1, $cid2, $cid3])
 267       ->execute();
 268     $this->assertCount(8, $result);
 269
 270     $result = Activity::get(FALSE)
 271       ->addSelect('id', 'contact.id', 'rel.id')
 272       ->addJoin('Contact', FALSE, 'ActivityContact', ['contact.record_type_id:name', '=', "'Activity Targets'"])
 273       ->addJoin('Contact AS rel', FALSE, 'RelationshipCache', ['rel.far_contact_id', '=', 'contact.id'], ['rel.near_relation:name', '=', '"Child of"'])
 274       ->addWhere('contact.id', 'IN', [$cid1, $cid2, $cid3])
 275       ->addOrderBy('id')
 276       ->execute();
 277     $this->assertCount(5, $result);
 278     $this->assertEquals($cid1, $result[0]['contact.id']);
 279     $this->assertEquals($cid2, $result[0]['rel.id']);
 280     $this->assertEquals($cid1, $result[1]['contact.id']);
 281     $this->assertEquals($cid3, $result[1]['rel.id']);
 282     $this->assertEquals($cid2, $result[2]['contact.id']);
 283     $this->assertNull($result[2]['rel.id']);
 284     $this->assertEquals($cid3, $result[3]['contact.id']);
 285     $this->assertNull($result[3]['rel.id']);
 286     $this->assertEquals($cid3, $result[4]['contact.id']);
 287     $this->assertNull($result[3]['rel.id']);
 288
 289   }
 290
 291 }