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