From 1c33974643ab04439a3230c3eba270453489a234 Mon Sep 17 00:00:00 2001 From: Coleman Watts Date: Sat, 3 Jul 2021 14:04:10 -0400 Subject: [PATCH] APIv4 - Support returning join data from serialized fields This allows the API to return e.g. an array of display names from a serialized contact reference field. --- Civi/Api4/Query/Api4SelectQuery.php | 32 +++++++++++++- .../Api4/Service/Schema/Joinable/Joinable.php | 31 +++++++++++++ Civi/Api4/Service/Schema/Joiner.php | 5 +++ Civi/Api4/Service/Schema/SchemaMapBuilder.php | 5 ++- .../api/v4/Action/CustomContactRefTest.php | 43 +++++++++++++++---- 5 files changed, 106 insertions(+), 10 deletions(-) diff --git a/Civi/Api4/Query/Api4SelectQuery.php b/Civi/Api4/Query/Api4SelectQuery.php index 882331a073..452a75bb1e 100644 --- a/Civi/Api4/Query/Api4SelectQuery.php +++ b/Civi/Api4/Query/Api4SelectQuery.php @@ -990,6 +990,7 @@ class Api4SelectQuery { return; } $lastLink = array_pop($joinPath); + $previousLink = array_pop($joinPath); // Custom field names are already prefixed $isCustom = $lastLink instanceof CustomGroupJoinable; @@ -1000,7 +1001,15 @@ class Api4SelectQuery { // Cache field info for retrieval by $this->getField() foreach ($lastLink->getEntityFields() as $fieldObject) { $fieldArray = $fieldObject->toArray(); - $fieldArray['sql_name'] = '`' . $lastLink->getAlias() . '`.`' . $fieldArray['column_name'] . '`'; + // Set sql name of field, using column name for real joins + if (!$lastLink->getSerialize()) { + $fieldArray['sql_name'] = '`' . $lastLink->getAlias() . '`.`' . $fieldArray['column_name'] . '`'; + } + // For virtual joins on serialized fields, the callback function will need the sql name of the serialized field + // @see self::renderSerializedJoin() + else { + $fieldArray['sql_name'] = '`' . $previousLink->getAlias() . '`.`' . $lastLink->getBaseColumn() . '`'; + } $this->addSpecField($prefix . $fieldArray['name'], $fieldArray); } } @@ -1019,6 +1028,27 @@ class Api4SelectQuery { } } + /** + * Performs a virtual join with a serialized field using FIND_IN_SET + * + * @param array $field + * @return string + */ + public static function renderSerializedJoin(array $field): string { + $sep = \CRM_Core_DAO::VALUE_SEPARATOR; + $id = CoreUtil::getInfoItem($field['entity'], 'primary_key')[0]; + $searchFn = "FIND_IN_SET(`{$field['table_name']}`.`$id`, REPLACE({$field['sql_name']}, '$sep', ','))"; + return "( + SELECT GROUP_CONCAT( + `{$field['column_name']}` + ORDER BY $searchFn + SEPARATOR '$sep' + ) + FROM `{$field['table_name']}` + WHERE $searchFn + )"; + } + /** * @return FALSE|string */ diff --git a/Civi/Api4/Service/Schema/Joinable/Joinable.php b/Civi/Api4/Service/Schema/Joinable/Joinable.php index d98c6b5bb5..52738aac81 100644 --- a/Civi/Api4/Service/Schema/Joinable/Joinable.php +++ b/Civi/Api4/Service/Schema/Joinable/Joinable.php @@ -70,6 +70,11 @@ class Joinable { */ protected $entity; + /** + * @var int + */ + protected $serialize; + /** * @var bool */ @@ -231,6 +236,24 @@ class Joinable { return $this; } + /** + * @return int|NULL + */ + public function getSerialize():? int { + return $this->serialize; + } + + /** + * @param int|NULL $serialize + * + * @return $this + */ + public function setSerialize(?int $serialize) { + $this->serialize = $serialize; + + return $this; + } + /** * @return int */ @@ -280,6 +303,14 @@ class Joinable { /** @var \Civi\Api4\Service\Spec\SpecGatherer $gatherer */ $gatherer = \Civi::container()->get('spec_gatherer'); $spec = $gatherer->getSpec($this->entity, 'get', FALSE); + // Serialized fields require a specialized join + if ($this->serialize) { + foreach ($spec as $field) { + // The callback function expects separated values as output + $field->setSerialize(\CRM_Core_DAO::SERIALIZE_SEPARATOR_TRIMMED); + $field->setSqlRenderer(['Civi\Api4\Query\Api4SelectQuery', 'renderSerializedJoin']); + } + } return $spec; } diff --git a/Civi/Api4/Service/Schema/Joiner.php b/Civi/Api4/Service/Schema/Joiner.php index 7836cdad50..353aed300b 100644 --- a/Civi/Api4/Service/Schema/Joiner.php +++ b/Civi/Api4/Service/Schema/Joiner.php @@ -73,6 +73,11 @@ class Joiner { if ($link->isDeprecated()) { \CRM_Core_Error::deprecatedWarning("Deprecated join alias '$alias' used in APIv4 get. Should be changed to '{$alias}_id'"); } + // Serialized joins are rendered by Api4SelectQuery::renderSerializedJoin + if ($link->getSerialize()) { + // Virtual join, don't actually add this table + break; + } $bao = $joinEntity ? CoreUtil::getBAOFromApiName($joinEntity) : NULL; $conditions = $link->getConditionsForJoin($baseTableAlias); diff --git a/Civi/Api4/Service/Schema/SchemaMapBuilder.php b/Civi/Api4/Service/Schema/SchemaMapBuilder.php index e89b7c359a..49a1a5e396 100644 --- a/Civi/Api4/Service/Schema/SchemaMapBuilder.php +++ b/Civi/Api4/Service/Schema/SchemaMapBuilder.php @@ -154,7 +154,7 @@ class SchemaMapBuilder { } $fieldData = \CRM_Utils_SQL_Select::from('civicrm_custom_field f') ->join('custom_group', 'INNER JOIN civicrm_custom_group g ON g.id = f.custom_group_id') - ->select(['g.name as custom_group_name', 'g.table_name', 'g.is_multiple', 'f.name', 'f.data_type', 'label', 'column_name', 'option_group_id']) + ->select(['g.name as custom_group_name', 'g.table_name', 'g.is_multiple', 'f.name', 'f.data_type', 'label', 'column_name', 'option_group_id', 'serialize']) ->where('g.extends IN (@entity)', ['@entity' => $customInfo['extends']]) ->where('g.is_active') ->where('f.is_active') @@ -185,6 +185,9 @@ class SchemaMapBuilder { if ($fieldData->data_type === 'ContactReference') { $joinable = new Joinable('civicrm_contact', 'id', $fieldData->name); + if ($fieldData->serialize) { + $joinable->setSerialize((int) $fieldData->serialize); + } $customTable->addTableLink($fieldData->column_name, $joinable); } } diff --git a/tests/phpunit/api/v4/Action/CustomContactRefTest.php b/tests/phpunit/api/v4/Action/CustomContactRefTest.php index b6ca751316..22db0897d8 100644 --- a/tests/phpunit/api/v4/Action/CustomContactRefTest.php +++ b/tests/phpunit/api/v4/Action/CustomContactRefTest.php @@ -60,14 +60,14 @@ class CustomContactRefTest extends BaseCustomValueTest { ->first()['id']; $favPeopleId1 = Contact::create(FALSE) - ->addValue('first_name', 'Favorite1') + ->addValue('first_name', 'FirstFav') ->addValue('last_name', 'People1') ->addValue('contact_type', 'Individual') ->execute() ->first()['id']; $favPeopleId2 = Contact::create(FALSE) - ->addValue('first_name', 'Favorite2') + ->addValue('first_name', 'SecondFav') ->addValue('last_name', 'People2') ->addValue('contact_type', 'Individual') ->execute() @@ -78,22 +78,49 @@ class CustomContactRefTest extends BaseCustomValueTest { ->addValue('last_name', 'Tester') ->addValue('contact_type', 'Individual') ->addValue('MyContactRef.FavPerson', $favPersonId) - ->addValue('MyContactRef.FavPeople', [$favPeopleId1, $favPeopleId2]) + ->addValue('MyContactRef.FavPeople', [$favPeopleId2, $favPeopleId1]) ->execute() ->first()['id']; - $contact = Contact::get(FALSE) + $contactId2 = Contact::create(FALSE) + ->addValue('first_name', 'Bea') + ->addValue('last_name', 'Tester') + ->addValue('contact_type', 'Individual') + ->addValue('MyContactRef.FavPeople', [$favPeopleId2]) + ->execute() + ->first()['id']; + + $result = Contact::get(FALSE) ->addSelect('display_name') ->addSelect('MyContactRef.FavPerson.first_name') ->addSelect('MyContactRef.FavPerson.last_name') ->addSelect('MyContactRef.FavPeople') + ->addSelect('MyContactRef.FavPeople.last_name') ->addWhere('MyContactRef.FavPerson.first_name', '=', $firstName) ->execute() - ->first(); + ->single(); + + $this->assertEquals($firstName, $result['MyContactRef.FavPerson.first_name']); + $this->assertEquals('Person', $result['MyContactRef.FavPerson.last_name']); + // Ensure serialized values are returned in order + $this->assertEquals([$favPeopleId2, $favPeopleId1], $result['MyContactRef.FavPeople']); + // Values returned from virtual join should be in the same order + $this->assertEquals(['People2', 'People1'], $result['MyContactRef.FavPeople.last_name']); + + $result = Contact::get(FALSE) + ->addSelect('id') + ->addWhere('MyContactRef.FavPeople.first_name', 'CONTAINS', 'First') + ->execute() + ->single(); + + $this->assertEquals($contactId1, $result['id']); + + $result = Contact::get(FALSE) + ->addSelect('id') + ->addWhere('MyContactRef.FavPeople.first_name', 'CONTAINS', 'Second') + ->execute(); - $this->assertEquals($firstName, $contact['MyContactRef.FavPerson.first_name']); - $this->assertEquals('Person', $contact['MyContactRef.FavPerson.last_name']); - $this->assertEquals([$favPeopleId1, $favPeopleId2], $contact['MyContactRef.FavPeople']); + $this->assertCount(2, $result); } public function testCurrentUser() { -- 2.25.1