From 17ea15063fd080480c9e8e9ee542009e99110e14 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Mon, 20 Jun 2022 20:36:08 -0400 Subject: [PATCH] APIv4 - Add Contact primary/billing joins for email, address, phone, im Declares an implicit join between the contact record and primary/billing email, phone, address & im records, making it easier to retrieve those directly from the Contact API. --- .../Subscriber/ContactSchemaMapSubscriber.php | 40 +++++++++++ Civi/Api4/Query/Api4SelectQuery.php | 5 +- .../Api4/Service/Schema/Joinable/Joinable.php | 17 +++-- .../Spec/Provider/ContactGetSpecProvider.php | 70 +++++++++++++++++++ ext/search_kit/Civi/Search/Admin.php | 13 ++++ .../phpunit/api/v4/Action/ContactGetTest.php | 33 +++++++++ 6 files changed, 170 insertions(+), 8 deletions(-) create mode 100644 Civi/Api4/Event/Subscriber/ContactSchemaMapSubscriber.php diff --git a/Civi/Api4/Event/Subscriber/ContactSchemaMapSubscriber.php b/Civi/Api4/Event/Subscriber/ContactSchemaMapSubscriber.php new file mode 100644 index 0000000000..6177ebe98a --- /dev/null +++ b/Civi/Api4/Event/Subscriber/ContactSchemaMapSubscriber.php @@ -0,0 +1,40 @@ + 'onSchemaBuild', + ]; + } + + /** + * @param \Civi\Api4\Event\SchemaMapBuildEvent $event + */ + public function onSchemaBuild(SchemaMapBuildEvent $event) { + $schema = $event->getSchemaMap(); + $table = $schema->getTableByName('civicrm_contact'); + + // Add links to primary & billing email, address, phone & im + foreach (['email', 'address', 'phone', 'im'] as $ent) { + foreach (['primary', 'billing'] as $type) { + $link = new Joinable("civicrm_$ent", 'contact_id', "{$ent}_$type"); + $link->setBaseTable('civicrm_contact'); + $link->setJoinType(Joinable::JOIN_TYPE_ONE_TO_ONE); + $link->addCondition("`{target_table}`.`is_$type` = 1"); + $table->addTableLink('id', $link); + } + } + } + +} diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php index 71ab0d2092..0ccce5e8b8 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -799,7 +799,10 @@ class Api4SelectQuery { // If we're not explicitly referencing the ID (or some other FK field) of the joinEntity, search for a default if (!$explicitFK) { foreach ($this->apiFieldSpec as $name => $field) { - if (is_array($field) && $field['entity'] !== $joinEntity && $field['fk_entity'] === $joinEntity) { + if (!is_array($field) || $field['type'] !== 'Field') { + continue; + } + if ($field['entity'] !== $joinEntity && $field['fk_entity'] === $joinEntity) { $conditions[] = $this->treeWalkClauses([$name, '=', "$alias.id"], 'ON'); } elseif (strpos($name, "$alias.") === 0 && substr_count($name, '.') === 1 && $field['fk_entity'] === $this->getEntity()) { diff --git a/Civi/Api4/Service/Schema/Joinable/Joinable.php b/Civi/Api4/Service/Schema/Joinable/Joinable.php index 076707504e..95b14cb95c 100644 --- a/Civi/Api4/Service/Schema/Joinable/Joinable.php +++ b/Civi/Api4/Service/Schema/Joinable/Joinable.php @@ -51,7 +51,7 @@ class Joinable { protected $alias; /** - * @var array + * @var string[] */ protected $conditions = []; @@ -103,15 +103,18 @@ class Joinable { * @return array */ public function getConditionsForJoin(string $baseTableAlias, string $tableAlias) { - $baseCondition = sprintf( + $conditions = []; + $conditions[] = sprintf( '`%s`.`%s` = `%s`.`%s`', $baseTableAlias, $this->baseColumn, $tableAlias, $this->targetColumn ); - - return array_merge([$baseCondition], $this->conditions); + foreach ($this->conditions as $condition) { + $conditions[] = str_replace(['{base_table}', '{target_table}'], [$baseTableAlias, $tableAlias], $condition); + } + return $conditions; } /** @@ -190,11 +193,11 @@ class Joinable { } /** - * @param $condition + * @param string $condition * * @return $this */ - public function addCondition($condition) { + public function addCondition(string $condition) { $this->conditions[] = $condition; return $this; @@ -208,7 +211,7 @@ class Joinable { } /** - * @param array $conditions + * @param string[] $conditions * * @return $this */ diff --git a/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php b/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php index 5c24e90b02..d3c9dab921 100644 --- a/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php +++ b/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php @@ -49,6 +49,64 @@ class ContactGetSpecProvider implements Generic\SpecProviderInterface { ->setSqlRenderer([__CLASS__, 'calculateAge']); $spec->addFieldSpec($field); } + + // Address, Email, Phone, IM + $entities = [ + 'Address' => [ + 'primary' => [ + 'title' => ts('Primary Address ID'), + 'label' => ts('Primary Address'), + ], + 'billing' => [ + 'title' => ts('Billing Address ID'), + 'label' => ts('Billing Address'), + ], + ], + 'Email' => [ + 'primary' => [ + 'title' => ts('Primary Email ID'), + 'label' => ts('Primary Email'), + ], + 'billing' => [ + 'title' => ts('Billing Email ID'), + 'label' => ts('Billing Email'), + ], + ], + 'Phone' => [ + 'primary' => [ + 'title' => ts('Primary Phone ID'), + 'label' => ts('Primary Phone'), + ], + 'billing' => [ + 'title' => ts('Billing Phone ID'), + 'label' => ts('Billing Phone'), + ], + ], + 'IM' => [ + 'primary' => [ + 'title' => ts('Primary IM ID'), + 'label' => ts('Primary IM'), + ], + 'billing' => [ + 'title' => ts('Billing IM ID'), + 'label' => ts('Billing IM'), + ], + ], + ]; + foreach ($entities as $entity => $types) { + foreach ($types as $type => $info) { + $name = strtolower($entity) . '_' . $type; + $field = new FieldSpec($name, 'Contact', 'String'); + $field->setLabel($info['label']) + ->setTitle($info['title']) + ->setColumnName('id') + ->setType('Extra') + ->setFkEntity($entity) + ->setSqlRenderer([__CLASS__, 'getLocationFieldSql']); + $spec->addFieldSpec($field); + } + } + } /** @@ -119,4 +177,16 @@ class ContactGetSpecProvider implements Generic\SpecProviderInterface { return "TIMESTAMPDIFF(YEAR, {$field['sql_name']}, CURDATE())"; } + /** + * Generate SQL for address/email/phone/im id field + * @param array $field + * @param \Civi\Api4\Query\Api4SelectQuery $query + * @return string + */ + public static function getLocationFieldSql(array $field, Api4SelectQuery $query) { + $prefix = empty($field['explicit_join']) ? '' : $field['explicit_join'] . '.'; + $idField = $query->getField($prefix . $field['name'] . '.id'); + return $idField['sql_name']; + } + } diff --git a/ext/search_kit/Civi/Search/Admin.php b/ext/search_kit/Civi/Search/Admin.php index 5eb3d4601a..8d0d2e2271 100644 --- a/ext/search_kit/Civi/Search/Admin.php +++ b/ext/search_kit/Civi/Search/Admin.php @@ -192,6 +192,19 @@ class Admin { array_splice($entity['fields'], $index, 0, [$newField]); } } + // Useful address fields (see ContactSchemaMapSubscriber) + if ($entity['name'] === 'Contact') { + $addressFields = ['city', 'state_province_id', 'country_id']; + foreach ($addressFields as $fieldName) { + foreach (['primary', 'billing'] as $type) { + $newField = \CRM_Utils_Array::findAll($schema['Address']['fields'], ['name' => $fieldName])[0]; + $newField['name'] = "address_$type.$fieldName"; + $arg = [1 => $newField['label']]; + $newField['label'] = $type === 'primary' ? ts('Address (primary) %1', $arg) : ts('Address (billing) %1', $arg); + $entity['fields'][] = $newField; + } + } + } } } return array_values($schema); diff --git a/tests/phpunit/api/v4/Action/ContactGetTest.php b/tests/phpunit/api/v4/Action/ContactGetTest.php index 6e4a722985..b341ba762b 100644 --- a/tests/phpunit/api/v4/Action/ContactGetTest.php +++ b/tests/phpunit/api/v4/Action/ContactGetTest.php @@ -369,4 +369,37 @@ class ContactGetTest extends Api4TestBase implements TransactionalInterface { } + public function testGetWithPrimaryEmailPhoneIMAddress() { + $lastName = uniqid(__FUNCTION__); + $email = uniqid() . '@example.com'; + $phone = uniqid('phone'); + $im = uniqid('im'); + $c1 = $this->createTestRecord('Contact', ['last_name' => $lastName]); + $c2 = $this->createTestRecord('Contact', ['last_name' => $lastName]); + $c3 = $this->createTestRecord('Contact', ['last_name' => $lastName]); + + $this->createTestRecord('Email', ['email' => $email, 'contact_id' => $c1['id']]); + $this->createTestRecord('Email', ['email' => 'not@primary.com', 'contact_id' => $c1['id']]); + $this->createTestRecord('Phone', ['phone' => $phone, 'contact_id' => $c1['id']]); + $this->createTestRecord('IM', ['name' => $im, 'contact_id' => $c2['id']]); + $this->createTestRecord('Address', ['city' => 'Somewhere', 'street_address' => '123 Street', 'contact_id' => $c2['id']]); + + $results = Contact::get(FALSE) + ->addSelect('id', 'email_primary.email', 'phone_primary.phone', 'im_primary.name', 'address_primary.*') + ->addWhere('last_name', '=', $lastName) + ->addOrderBy('id') + ->execute(); + + $this->assertEquals($email, $results[0]['email_primary.email']); + $this->assertEquals($phone, $results[0]['phone_primary.phone']); + $this->assertEquals($im, $results[1]['im_primary.name']); + $this->assertEquals('Somewhere', $results[1]['address_primary.city']); + $this->assertEquals('123 Street', $results[1]['address_primary.street_address']); + $this->assertNull($results[0]['im_primary.name']); + $this->assertNull($results[2]['email_primary.email']); + $this->assertNull($results[2]['phone_primary.phone']); + $this->assertNull($results[2]['im_primary.name']); + $this->assertNull($results[2]['address_primary.city']); + } + } -- 2.25.1