From 79f4446df9334c66aec0249a0e664105f90631b9 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Fri, 12 Aug 2022 21:30:40 -0400 Subject: [PATCH] APIv4 - Add `case_id` field to Activity entity This virtual field allows an activity to be easily filed on a case, and for cases to be looked up in SearchKit activity searches. --- .../ActivitySchemaMapSubscriber.php | 37 ++++++++++ .../Service/Schema/Joinable/ExtraJoinable.php | 34 +++++++++ .../Api4/Service/Schema/Joinable/Joinable.php | 26 ++++--- Civi/Api4/Service/Schema/Joiner.php | 20 +++++ .../Spec/Provider/ActivitySpecProvider.php | 74 +++++++++++-------- .../Spec/Provider/ContactGetSpecProvider.php | 21 +----- 6 files changed, 150 insertions(+), 62 deletions(-) create mode 100644 Civi/Api4/Event/Subscriber/ActivitySchemaMapSubscriber.php create mode 100644 Civi/Api4/Service/Schema/Joinable/ExtraJoinable.php diff --git a/Civi/Api4/Event/Subscriber/ActivitySchemaMapSubscriber.php b/Civi/Api4/Event/Subscriber/ActivitySchemaMapSubscriber.php new file mode 100644 index 0000000000..0c234dd8ad --- /dev/null +++ b/Civi/Api4/Event/Subscriber/ActivitySchemaMapSubscriber.php @@ -0,0 +1,37 @@ + 'onSchemaBuild', + ]; + } + + /** + * @param \Civi\Api4\Event\SchemaMapBuildEvent $event + */ + public function onSchemaBuild(SchemaMapBuildEvent $event): void { + $schema = $event->getSchemaMap(); + $table = $schema->getTableByName('civicrm_activity'); + + $link = (new ExtraJoinable('civicrm_case', 'id', 'case_id')) + ->setBaseTable('civicrm_activity') + ->setJoinType(Joinable::JOIN_TYPE_MANY_TO_ONE) + ->addCondition('`{target_table}`.`id` = (SELECT `civicrm_case_activity`.`case_id` FROM `civicrm_case_activity` WHERE `civicrm_case_activity`.`activity_id` = `{base_table}`.`id` LIMIT 1)'); + $table->addTableLink('id', $link); + + } + +} diff --git a/Civi/Api4/Service/Schema/Joinable/ExtraJoinable.php b/Civi/Api4/Service/Schema/Joinable/ExtraJoinable.php new file mode 100644 index 0000000000..bfd22006d3 --- /dev/null +++ b/Civi/Api4/Service/Schema/Joinable/ExtraJoinable.php @@ -0,0 +1,34 @@ +addExtraJoinConditions($conditions, $baseTableAlias, $targetTableAlias); + return $conditions; + } + +} diff --git a/Civi/Api4/Service/Schema/Joinable/Joinable.php b/Civi/Api4/Service/Schema/Joinable/Joinable.php index 95b14cb95c..33e2602610 100644 --- a/Civi/Api4/Service/Schema/Joinable/Joinable.php +++ b/Civi/Api4/Service/Schema/Joinable/Joinable.php @@ -98,23 +98,32 @@ class Joinable { * Gets conditions required when joining to a base table * * @param string $baseTableAlias - * @param string $tableAlias + * @param string $targetTableAlias * * @return array */ - public function getConditionsForJoin(string $baseTableAlias, string $tableAlias) { + public function getConditionsForJoin(string $baseTableAlias, string $targetTableAlias) { $conditions = []; $conditions[] = sprintf( '`%s`.`%s` = `%s`.`%s`', $baseTableAlias, $this->baseColumn, - $tableAlias, + $targetTableAlias, $this->targetColumn ); + $this->addExtraJoinConditions($conditions, $baseTableAlias, $targetTableAlias); + return $conditions; + } + + /** + * @param $conditions + * @param string $baseTableAlias + * @param string $targetTableAlias + */ + protected function addExtraJoinConditions(&$conditions, string $baseTableAlias, string $targetTableAlias):void { foreach ($this->conditions as $condition) { - $conditions[] = str_replace(['{base_table}', '{target_table}'], [$baseTableAlias, $tableAlias], $condition); + $conditions[] = str_replace(['{base_table}', '{target_table}'], [$baseTableAlias, $targetTableAlias], $condition); } - return $conditions; } /** @@ -203,13 +212,6 @@ class Joinable { return $this; } - /** - * @return array - */ - public function getExtraJoinConditions() { - return $this->conditions; - } - /** * @param string[] $conditions * diff --git a/Civi/Api4/Service/Schema/Joiner.php b/Civi/Api4/Service/Schema/Joiner.php index 169466dfd5..cda0a8344e 100644 --- a/Civi/Api4/Service/Schema/Joiner.php +++ b/Civi/Api4/Service/Schema/Joiner.php @@ -12,6 +12,8 @@ namespace Civi\Api4\Service\Schema; +use Civi\Api4\Query\Api4SelectQuery; + class Joiner { /** * @var SchemaMap @@ -62,4 +64,22 @@ class Joiner { return $this->cache[$cacheKey]; } + /** + * SpecProvider callback for joins added via a SchemaMapSubscriber. + * + * This works for extra joins declared via SchemaMapSubscriber. + * It allows implicit joins through custom sql, by virtue of the fact + * that `$query->getField` will create the join not just to the `id` field + * but to every field on the joined entity, allowing e.g. joins to `address_primary.country_id:label`. + * + * @param array $field + * @param \Civi\Api4\Query\Api4SelectQuery $query + * @return string + */ + public static function getExtraJoinSql(array $field, Api4SelectQuery $query): string { + $prefix = empty($field['explicit_join']) ? '' : $field['explicit_join'] . '.'; + $idField = $query->getField($prefix . $field['name'] . '.id'); + return $idField['sql_name']; + } + } diff --git a/Civi/Api4/Service/Spec/Provider/ActivitySpecProvider.php b/Civi/Api4/Service/Spec/Provider/ActivitySpecProvider.php index 5399893798..496f7e64d0 100644 --- a/Civi/Api4/Service/Spec/Provider/ActivitySpecProvider.php +++ b/Civi/Api4/Service/Spec/Provider/ActivitySpecProvider.php @@ -23,45 +23,59 @@ class ActivitySpecProvider implements Generic\SpecProviderInterface { public function modifySpec(RequestSpec $spec) { $action = $spec->getAction(); - // The database default '1' is problematic as the option list is user-configurable, - // so activity type '1' doesn't necessarily exist. Best make the field required. - $spec->getFieldByName('activity_type_id') - ->setDefaultValue(NULL) - ->setRequired($action === 'create'); + if (\CRM_Core_Component::isEnabled('CiviCase')) { + $field = new FieldSpec('case_id', 'Activity', 'Integer'); + $field->setTitle(ts('Case ID')); + $field->setLabel($action === 'get' ? ts('Filed on Case') : ts('File on Case')); + $field->setDescription(ts('CiviCase this activity belongs to.')); + $field->setFkEntity('Case'); + $field->setInputType('EntityRef'); + $field->setColumnName('id'); + $field->setSqlRenderer(['\Civi\Api4\Service\Schema\Joiner', 'getExtraJoinSql']); + $spec->addFieldSpec($field); + } - $field = new FieldSpec('source_contact_id', 'Activity', 'Integer'); - $field->setTitle(ts('Source Contact')); - $field->setLabel(ts('Added by')); - $field->setDescription(ts('Contact who created this activity.')); - $field->setRequired($action === 'create'); - $field->setFkEntity('Contact'); - $field->setInputType('EntityRef'); - $spec->addFieldSpec($field); + if (in_array($action, ['create', 'update'], TRUE)) { + // The database default '1' is problematic as the option list is user-configurable, + // so activity type '1' doesn't necessarily exist. Best make the field required. + $spec->getFieldByName('activity_type_id') + ->setDefaultValue(NULL) + ->setRequired($action === 'create'); - $field = new FieldSpec('target_contact_id', 'Activity', 'Array'); - $field->setTitle(ts('Target Contacts')); - $field->setLabel(ts('With Contact(s)')); - $field->setDescription(ts('Contact(s) involved in this activity.')); - $field->setFkEntity('Contact'); - $field->setInputType('EntityRef'); - $field->setInputAttrs(['multiple' => TRUE]); - $spec->addFieldSpec($field); + $field = new FieldSpec('source_contact_id', 'Activity', 'Integer'); + $field->setTitle(ts('Source Contact')); + $field->setLabel(ts('Added by')); + $field->setDescription(ts('Contact who created this activity.')); + $field->setRequired($action === 'create'); + $field->setFkEntity('Contact'); + $field->setInputType('EntityRef'); + $spec->addFieldSpec($field); - $field = new FieldSpec('assignee_contact_id', 'Activity', 'Array'); - $field->setTitle(ts('Assignee Contacts')); - $field->setLabel(ts('Assigned to')); - $field->setDescription(ts('Contact(s) assigned to this activity.')); - $field->setFkEntity('Contact'); - $field->setInputType('EntityRef'); - $field->setInputAttrs(['multiple' => TRUE]); - $spec->addFieldSpec($field); + $field = new FieldSpec('target_contact_id', 'Activity', 'Array'); + $field->setTitle(ts('Target Contacts')); + $field->setLabel(ts('With Contact(s)')); + $field->setDescription(ts('Contact(s) involved in this activity.')); + $field->setFkEntity('Contact'); + $field->setInputType('EntityRef'); + $field->setInputAttrs(['multiple' => TRUE]); + $spec->addFieldSpec($field); + + $field = new FieldSpec('assignee_contact_id', 'Activity', 'Array'); + $field->setTitle(ts('Assignee Contacts')); + $field->setLabel(ts('Assigned to')); + $field->setDescription(ts('Contact(s) assigned to this activity.')); + $field->setFkEntity('Contact'); + $field->setInputType('EntityRef'); + $field->setInputAttrs(['multiple' => TRUE]); + $spec->addFieldSpec($field); + } } /** * @inheritDoc */ public function applies($entity, $action) { - return $entity === 'Activity' && in_array($action, ['create', 'update'], TRUE); + return $entity === 'Activity'; } } diff --git a/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php b/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php index d87cf7dc13..31be2aeb63 100644 --- a/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php +++ b/Civi/Api4/Service/Spec/Provider/ContactGetSpecProvider.php @@ -104,7 +104,7 @@ class ContactGetSpecProvider implements Generic\SpecProviderInterface { ->setColumnName('id') ->setType('Extra') ->setFkEntity($entity) - ->setSqlRenderer([__CLASS__, 'getLocationFieldSql']); + ->setSqlRenderer(['\Civi\Api4\Service\Schema\Joiner', 'getExtraJoinSql']); $spec->addFieldSpec($field); } } @@ -179,23 +179,4 @@ class ContactGetSpecProvider implements Generic\SpecProviderInterface { return "TIMESTAMPDIFF(YEAR, {$field['sql_name']}, CURDATE())"; } - /** - * Generate SQL for address/email/phone/im id field - * - * This works because the join was declared in ContactSchemaMapSubscriber - * and that also magically allows implicit joins through this one, by virtue - * of the fact that `$query->getField` will create the join not just to the `id` field - * but to every field on the joined entity, allowing e.g. joins to `address_primary.country_id:label`. - * - * @see \Civi\Api4\Event\Subscriber\ContactSchemaMapSubscriber::onSchemaBuild() - * @param array $field - * @param \Civi\Api4\Query\Api4SelectQuery $query - * @return string - */ - public static function getLocationFieldSql(array $field, Api4SelectQuery $query): string { - $prefix = empty($field['explicit_join']) ? '' : $field['explicit_join'] . '.'; - $idField = $query->getField($prefix . $field['name'] . '.id'); - return $idField['sql_name']; - } - } -- 2.25.1