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 testImplicitJoinOnExplicitJoin() {
 103     $contacts = Contact::get(FALSE)
 104       ->addWhere('id', '=', $this->getReference('test_contact_1')['id'])
 105       ->addJoin('Address AS address', TRUE, ['id', '=', 'address.contact_id'], ['address.location_type_id', '=', 1])
 106       ->addSelect('id', 'address.country.iso_code')
 107       ->execute();
 108     $this->assertCount(1, $contacts);
 109     $this->assertEquals('US', $contacts[0]['address.country.iso_code']);
 110   }
 111
 112   public function testJoinToTheSameTableTwice() {
 113     $cid1 = Contact::create(FALSE)
 114       ->addValue('first_name', 'Aaa')
 115       ->addChain('email1', Email::create()->setValues(['email' => 'yoohoo@yahoo.test', 'contact_id' => '$id', 'location_type_id:name' => 'Home']))
 116       ->addChain('email2', Email::create()->setValues(['email' => 'yahoo@yoohoo.test', 'contact_id' => '$id', 'location_type_id:name' => 'Work']))
 117       ->execute()
 118       ->first()['id'];
 119
 120     $cid2 = Contact::create(FALSE)
 121       ->addValue('first_name', 'Bbb')
 122       ->addChain('email1', Email::create()->setValues(['email' => '1@test.test', 'contact_id' => '$id', 'location_type_id:name' => 'Home']))
 123       ->addChain('email2', Email::create()->setValues(['email' => '2@test.test', 'contact_id' => '$id', 'location_type_id:name' => 'Work']))
 124       ->addChain('email3', Email::create()->setValues(['email' => '3@test.test', 'contact_id' => '$id', 'location_type_id:name' => 'Other']))
 125       ->execute()
 126       ->first()['id'];
 127
 128     $cid3 = Contact::create(FALSE)
 129       ->addValue('first_name', 'Ccc')
 130       ->execute()
 131       ->first()['id'];
 132
 133     $contacts = Contact::get(FALSE)
 134       ->addSelect('id', 'first_name', 'any_email.email', 'any_email.location_type_id:name', 'any_email.is_primary', 'primary_email.email')
 135       ->setJoin([
 136         ['Email AS any_email', TRUE, NULL],
 137         ['Email AS primary_email', FALSE, ['primary_email.is_primary', '=', TRUE]],
 138       ])
 139       ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
 140       ->addOrderBy('any_email.id')
 141       ->setDebug(TRUE)
 142       ->execute();
 143     $this->assertCount(5, $contacts);
 144     $this->assertEquals('Home', $contacts[0]['any_email.location_type_id:name']);
 145     $this->assertEquals('yoohoo@yahoo.test', $contacts[1]['primary_email.email']);
 146     $this->assertEquals('1@test.test', $contacts[2]['primary_email.email']);
 147     $this->assertEquals('1@test.test', $contacts[3]['primary_email.email']);
 148     $this->assertEquals('1@test.test', $contacts[4]['primary_email.email']);
 149   }
 150
 151   public function testBridgeJoinTags() {
 152     $tag1 = Tag::create()->setCheckPermissions(FALSE)
 153       ->addValue('name', uniqid('join1'))
 154       ->execute()
 155       ->first()['name'];
 156     $tag2 = Tag::create()->setCheckPermissions(FALSE)
 157       ->addValue('name', uniqid('join2'))
 158       ->execute()
 159       ->first()['name'];
 160     $tag3 = Tag::create()->setCheckPermissions(FALSE)
 161       ->addValue('name', uniqid('join3'))
 162       ->execute()
 163       ->first()['name'];
 164
 165     $cid1 = Contact::create()->setCheckPermissions(FALSE)
 166       ->addValue('first_name', 'Aaa')
 167       ->addChain('tag1', EntityTag::create()->setValues(['entity_id' => '$id', 'tag_id:name' => $tag1]))
 168       ->addChain('tag2', EntityTag::create()->setValues(['entity_id' => '$id', 'tag_id:name' => $tag2]))
 169       ->execute()
 170       ->first()['id'];
 171     $cid2 = Contact::create()->setCheckPermissions(FALSE)
 172       ->addValue('first_name', 'Bbb')
 173       ->addChain('tag1', EntityTag::create()->setValues(['entity_id' => '$id', 'tag_id:name' => $tag1]))
 174       ->addChain('tag3', EntityTag::create()->setValues(['entity_id' => '$id', 'tag_id:name' => $tag3]))
 175       ->execute()
 176       ->first()['id'];
 177     $cid3 = Contact::create()->setCheckPermissions(FALSE)
 178       ->addValue('first_name', 'Ccc')
 179       ->execute()
 180       ->first()['id'];
 181
 182     $required = Contact::get()->setCheckPermissions(FALSE)
 183       ->addJoin('Tag', TRUE, 'EntityTag')
 184       ->addSelect('first_name', 'tag.name')
 185       ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
 186       ->execute();
 187     $this->assertCount(4, $required);
 188
 189     $optional = Contact::get()->setCheckPermissions(FALSE)
 190       ->addJoin('Tag', FALSE, 'EntityTag', ['tag.name', 'IN', [$tag1, $tag2, $tag3]])
 191       ->addSelect('first_name', 'tag.name')
 192       ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
 193       ->execute();
 194     $this->assertCount(5, $optional);
 195
 196     $grouped = Contact::get()->setCheckPermissions(FALSE)
 197       ->addJoin('Tag', FALSE, 'EntityTag', ['tag.name', 'IN', [$tag1, $tag3]])
 198       ->addSelect('first_name', 'COUNT(tag.name) AS tags')
 199       ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
 200       ->addGroupBy('id')
 201       ->execute()->indexBy('id');
 202     $this->assertEquals(1, (int) $grouped[$cid1]['tags']);
 203     $this->assertEquals(2, (int) $grouped[$cid2]['tags']);
 204     $this->assertEquals(0, (int) $grouped[$cid3]['tags']);
 205
 206     $reverse = Tag::get()->setCheckPermissions(FALSE)
 207       ->addJoin('Contact', FALSE, 'EntityTag', ['contact.id', 'IN', [$cid1, $cid2, $cid3]])
 208       ->addGroupBy('id')
 209       ->addSelect('name', 'COUNT(contact.id) AS contacts')
 210       ->execute()->indexBy('name');
 211     $this->assertEquals(2, (int) $reverse[$tag1]['contacts']);
 212     $this->assertEquals(1, (int) $reverse[$tag2]['contacts']);
 213     $this->assertEquals(1, (int) $reverse[$tag3]['contacts']);
 214   }
 215
 216   public function testBridgeJoinRelationshipContactActivity() {
 217     $cid1 = Contact::create()->setCheckPermissions(FALSE)
 218       ->addValue('first_name', 'Aaa')
 219       ->addChain('activity', Activity::create()
 220         ->addValue('activity_type_id:name', 'Meeting')
 221         ->addValue('source_contact_id', '$id')
 222         ->addValue('target_contact_id', '$id')
 223       )
 224       ->execute()
 225       ->first()['id'];
 226     $cid2 = Contact::create()->setCheckPermissions(FALSE)
 227       ->addValue('first_name', 'Bbb')
 228       ->addChain('activity', Activity::create()
 229         ->addValue('activity_type_id:name', 'Phone Call')
 230         ->addValue('source_contact_id', $cid1)
 231         ->addValue('target_contact_id', '$id')
 232       )
 233       ->addChain('r1', Relationship::create()
 234         ->setValues(['contact_id_a' => '$id', 'contact_id_b' => $cid1, 'relationship_type_id' => 1])
 235       )
 236       ->execute()
 237       ->first()['id'];
 238     $cid3 = Contact::create()->setCheckPermissions(FALSE)
 239       ->addValue('first_name', 'Ccc')
 240       ->addChain('activity', Activity::create()
 241         ->addValue('activity_type_id:name', 'Meeting')
 242         ->addValue('source_contact_id', $cid1)
 243         ->addValue('target_contact_id', '$id')
 244       )
 245       ->addChain('activity2', Activity::create()
 246         ->addValue('activity_type_id:name', 'Phone Call')
 247         ->addValue('source_contact_id', $cid1)
 248         ->addValue('target_contact_id', '$id')
 249       )
 250       ->addChain('r1', Relationship::create()
 251         ->setValues(['contact_id_a' => '$id', 'contact_id_b' => $cid1, 'relationship_type_id' => 1])
 252       )
 253       ->addChain('r2', Relationship::create()
 254         ->setValues(['contact_id_a' => '$id', 'contact_id_b' => $cid2, 'relationship_type_id' => 2])
 255       )
 256       ->execute()
 257       ->first()['id'];
 258
 259     $result = Contact::get(FALSE)
 260       ->addSelect('id', 'act.id')
 261       ->addJoin('Activity AS act', TRUE, 'ActivityContact', ['act.record_type_id:name', '=', "'Activity Targets'"])
 262       ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
 263       ->execute();
 264     $this->assertCount(4, $result);
 265
 266     $result = Contact::get(FALSE)
 267       ->addSelect('id', 'act.id')
 268       ->addJoin('Activity AS act', TRUE, 'ActivityContact', ['act.activity_type_id:name', '=', "'Meeting'"], ['act.record_type_id:name', '=', "'Activity Targets'"])
 269       ->addWhere('id', 'IN', [$cid1, $cid2, $cid3])
 270       ->execute();
 271     $this->assertCount(2, $result);
 272
 273     $result = Activity::get(FALSE)
 274       ->addSelect('id', 'contact.id')
 275       ->addJoin('Contact', FALSE, 'ActivityContact')
 276       ->addWhere('contact.id', 'IN', [$cid1, $cid2, $cid3])
 277       ->execute();
 278     $this->assertCount(8, $result);
 279
 280     $result = Activity::get(FALSE)
 281       ->addSelect('id', 'contact.id', 'rel.id')
 282       ->addJoin('Contact', FALSE, 'ActivityContact', ['contact.record_type_id:name', '=', "'Activity Targets'"])
 283       ->addJoin('Contact AS rel', FALSE, 'RelationshipCache', ['rel.far_contact_id', '=', 'contact.id'], ['rel.near_relation:name', '=', '"Child of"'])
 284       ->addWhere('contact.id', 'IN', [$cid1, $cid2, $cid3])
 285       ->addOrderBy('id')
 286       ->execute();
 287     $this->assertCount(5, $result);
 288     $this->assertEquals($cid1, $result[0]['contact.id']);
 289     $this->assertEquals($cid2, $result[0]['rel.id']);
 290     $this->assertEquals($cid1, $result[1]['contact.id']);
 291     $this->assertEquals($cid3, $result[1]['rel.id']);
 292     $this->assertEquals($cid2, $result[2]['contact.id']);
 293     $this->assertNull($result[2]['rel.id']);
 294     $this->assertEquals($cid3, $result[3]['contact.id']);
 295     $this->assertNull($result[3]['rel.id']);
 296     $this->assertEquals($cid3, $result[4]['contact.id']);
 297     $this->assertNull($result[3]['rel.id']);
 298
 299   }
 300
 301 }