diff options
author | Yaco <franco@reevo.org> | 2020-06-04 11:01:00 -0300 |
---|---|---|
committer | Yaco <franco@reevo.org> | 2020-06-04 11:01:00 -0300 |
commit | fc7369835258467bf97eb64f184b93691f9a9fd5 (patch) | |
tree | daabd60089d2dd76d9f5fb416b005fbe159c799d /www/crm/wp-content/plugins/civicrm/civicrm/ext/api4/Civi/Api4/Event/Subscriber/PostSelectQuerySubscriber.php |
first commit
Diffstat (limited to 'www/crm/wp-content/plugins/civicrm/civicrm/ext/api4/Civi/Api4/Event/Subscriber/PostSelectQuerySubscriber.php')
-rw-r--r-- | www/crm/wp-content/plugins/civicrm/civicrm/ext/api4/Civi/Api4/Event/Subscriber/PostSelectQuerySubscriber.php | 369 |
1 files changed, 369 insertions, 0 deletions
diff --git a/www/crm/wp-content/plugins/civicrm/civicrm/ext/api4/Civi/Api4/Event/Subscriber/PostSelectQuerySubscriber.php b/www/crm/wp-content/plugins/civicrm/civicrm/ext/api4/Civi/Api4/Event/Subscriber/PostSelectQuerySubscriber.php new file mode 100644 index 00000000..ff7e6d20 --- /dev/null +++ b/www/crm/wp-content/plugins/civicrm/civicrm/ext/api4/Civi/Api4/Event/Subscriber/PostSelectQuerySubscriber.php @@ -0,0 +1,369 @@ +<?php + +namespace Civi\Api4\Event\Subscriber; + +use Civi\Api4\Event\Events; +use Civi\Api4\Event\PostSelectQueryEvent; +use Civi\Api4\Query\Api4SelectQuery; +use Civi\Api4\Service\Schema\Joinable\Joinable; +use Civi\Api4\Utils\ArrayInsertionUtil; +use Symfony\Component\EventDispatcher\EventSubscriberInterface; + +/** + * Changes the results of a select query, doing 1-n joins and unserializing data + */ +class PostSelectQuerySubscriber implements EventSubscriberInterface { + + /** + * @inheritdoc + */ + public static function getSubscribedEvents() { + return [ + Events::POST_SELECT_QUERY => 'onPostQuery' + ]; + } + + /** + * @param PostSelectQueryEvent $event + */ + public function onPostQuery(PostSelectQueryEvent $event) { + $results = $event->getResults(); + $event->setResults($this->postRun($results, $event->getQuery())); + } + + /** + * @param array $results + * @param Api4SelectQuery $query + * + * @return array + */ + protected function postRun(array $results, Api4SelectQuery $query) { + if (empty($results)) { + return $results; + } + + $fieldSpec = $query->getApiFieldSpec(); + $this->unserializeFields($results, $query->getEntity(), $fieldSpec); + + // Group the selects to avoid queries for each field + $groupedSelects = $this->getJoinedDotSelects($query); + foreach ($groupedSelects as $finalAlias => $selects) { + $joinPath = $this->getJoinPathInfo($selects[0], $query); + $selects = $this->formatSelects($finalAlias, $selects, $query); + $joinResults = $this->getJoinResults($query, $finalAlias, $selects); + $this->formatJoinResults($joinResults, $query, $finalAlias); + + // Insert join results into original result + foreach ($results as &$primaryResult) { + $baseId = $primaryResult['id']; + $filtered = array_filter($joinResults, function ($res) use ($baseId) { + return ($res['_base_id'] === $baseId); + }); + $filtered = array_values($filtered); + ArrayInsertionUtil::insert($primaryResult, $joinPath, $filtered); + } + } + + return array_values($results); + } + + /** + * @param array $joinResults + * @param Api4SelectQuery $query + * @param string $alias + */ + private function formatJoinResults(&$joinResults, $query, $alias) { + $join = $query->getJoinedTable($alias); + $fields = []; + foreach ($join->getEntityFields() as $field) { + $name = explode('.', $field->getName()); + $fields[array_pop($name)] = $field->toArray(); + } + if ($fields) { + $this->unserializeFields($joinResults, NULL, $fields); + } + } + + /** + * Unserialize values + * + * @param array $results + * @param string $entity + * @param array $fields + */ + protected function unserializeFields(&$results, $entity, $fields = []) { + if (empty($fields)) { + $params = ['action' => 'get', 'includeCustom' => FALSE]; + $fields = civicrm_api4($entity, 'getFields', $params)->indexBy('name'); + } + + foreach ($results as &$result) { + foreach ($result as $field => &$value) { + if (!empty($fields[$field]['serialize']) && is_string($value)) { + $serializationType = $fields[$field]['serialize']; + $value = \CRM_Core_DAO::unSerializeField($value, $serializationType); + } + } + } + } + + /** + * @param Api4SelectQuery $query + * + * @return array + */ + private function getJoinedDotSelects(Api4SelectQuery $query) { + // Remove selects that are not in a joined table + $fkAliases = $query->getFkSelectAliases(); + $joinedDotSelects = array_filter( + $query->getSelect(), + function ($select) use ($fkAliases) { + return isset($fkAliases[$select]); + } + ); + + $selects = []; + // group related selects by alias so they can be executed in one query + foreach ($joinedDotSelects as $select) { + $parts = explode('.', $select); + $finalAlias = $parts[count($parts) - 2]; + $selects[$finalAlias][] = $select; + } + + // sort by depth, e.g. email selects should be done before email.location + uasort($selects, function ($a, $b) { + $aFirst = $a[0]; + $bFirst = $b[0]; + return substr_count($aFirst, '.') > substr_count($bFirst, '.'); + }); + + return $selects; + } + + + /** + * @param array $selects + * @param $serializationType + * @param Api4SelectQuery $query + * + * @return array + */ + private function getResultsForSerializedField( + array $selects, + $serializationType, + Api4SelectQuery $query + ) { + // Get the alias (Selects are grouped and all target the same table) + $sampleField = current($selects); + $alias = strstr($sampleField, '.', TRUE); + + // Fetch the results with the serialized field + $selects['serialized'] = $query::MAIN_TABLE_ALIAS . '.' . $alias; + $serializedResults = $this->runWithNewSelects($selects, $query); + $newResults = []; + + // Create a new results array, with a separate entry for each option value + foreach ($serializedResults as $result) { + $optionValues = \CRM_Core_DAO::unSerializeField( + $result['serialized'], + $serializationType + ); + unset($result['serialized']); + foreach ($optionValues as $value) { + $newResults[] = array_merge($result, ['value' => $value]); + } + } + + $optionValueValues = array_unique(array_column($newResults, 'value')); + $optionValues = $this->getOptionValuesFromValues( + $selects, + $query, + $optionValueValues + ); + $valueField = $alias . '.value'; + + // Index by value + foreach ($optionValues as $key => $subResult) { + $optionValues[$subResult['value']] = $subResult; + unset($subResult[$key]); + + // Exclude 'value' if not in original selects + if (!in_array($valueField, $selects)) { + unset($optionValues[$subResult['value']]['value']); + } + } + + // Replace serialized with the sub-select results + foreach ($newResults as &$result) { + $result = array_merge($result, $optionValues[$result['value']]); + unset($result['value']); + } + + return $newResults; + } + + + /** + * Prepares selects for the subquery to fetch join results + * + * @param string $alias + * @param array $selects + * @param Api4SelectQuery $query + * + * @return array + */ + private function formatSelects($alias, $selects, Api4SelectQuery $query) { + $mainAlias = $query::MAIN_TABLE_ALIAS; + $selectFields = []; + + foreach ($selects as $select) { + $selectAlias = $query->getFkSelectAliases()[$select]; + $fieldAlias = substr($select, strrpos($select, '.') + 1); + $selectFields[$fieldAlias] = $selectAlias; + } + + $firstSelect = $selects[0]; + $pathParts = explode('.', $firstSelect); + $numParts = count($pathParts); + $parentAlias = $numParts > 2 ? $pathParts[$numParts - 3] : $mainAlias; + + $selectFields['id'] = sprintf('%s.id', $alias); + $selectFields['_parent_id'] = $parentAlias . '.id'; + $selectFields['_base_id'] = $mainAlias . '.id'; + + return $selectFields; + } + + /** + * @param array $selects + * @param Api4SelectQuery $query + * + * @return array + */ + private function runWithNewSelects(array $selects, Api4SelectQuery $query) { + $aliasedSelects = array_map(function ($field, $alias) { + return sprintf('%s as "%s"', $field, $alias); + }, $selects, array_keys($selects)); + + $newSelect = sprintf('SELECT DISTINCT %s', implode(", ", $aliasedSelects)); + $sql = str_replace("\n", ' ', $query->getQuery()->toSQL()); + $originalSelect = substr($sql, 0, strpos($sql, ' FROM')); + $sql = str_replace($originalSelect, $newSelect, $sql); + + $relatedResults = []; + $resultDAO = \CRM_Core_DAO::executeQuery($sql); + while ($resultDAO->fetch()) { + $relatedResult = []; + foreach ($selects as $alias => $column) { + $returnName = $alias; + $alias = str_replace('.', '_', $alias); + if (property_exists($resultDAO, $alias)) { + $relatedResult[$returnName] = $resultDAO->$alias; + } + }; + $relatedResults[] = $relatedResult; + } + + return $relatedResults; + } + + /** + * @param Api4SelectQuery $query + * @param $alias + * @param $selects + * @return array + */ + protected function getJoinResults(Api4SelectQuery $query, $alias, $selects) { + $apiFieldSpec = $query->getApiFieldSpec(); + if (!empty($apiFieldSpec[$alias]['serialize'])) { + $type = $apiFieldSpec[$alias]['serialize']; + $joinResults = $this->getResultsForSerializedField($selects, $type, $query); + } + else { + $joinResults = $this->runWithNewSelects($selects, $query); + } + + // Remove results with no matching entries + $joinResults = array_filter($joinResults, function ($result) { + return !empty($result['id']); + }); + + return $joinResults; + } + + /** + * Separates a string like 'emails.location_type.label' into an array, where + * each value in the array tells whether it is 1-1 or 1-n join type + * + * @param string $pathString + * Dot separated path to the field + * @param Api4SelectQuery $query + * + * @return array + * Index is table alias and value is boolean whether is 1-to-many join + */ + private function getJoinPathInfo($pathString, $query) { + $pathParts = explode('.', $pathString); + array_pop($pathParts); // remove field + $path = []; + $isMultipleChecker = function($alias) use ($query) { + foreach ($query->getJoinedTables() as $table) { + if ($table->getAlias() === $alias) { + return $table->getJoinType() === Joinable::JOIN_TYPE_ONE_TO_MANY; + } + } + return FALSE; + }; + + foreach ($pathParts as $part) { + $path[$part] = $isMultipleChecker($part); + } + + return $path; + } + + /** + * Get all the option_value values required in the query + * + * @param array $selects + * @param Api4SelectQuery $query + * @param array $values + * + * @return array + */ + private function getOptionValuesFromValues( + array $selects, + Api4SelectQuery $query, + array $values + ) { + $sampleField = current($selects); + $alias = strstr($sampleField, '.', TRUE); + + // Get the option value table that was joined + $relatedTable = NULL; + foreach ($query->getJoinedTables() as $joinedTable) { + if ($joinedTable->getAlias() === $alias) { + $relatedTable = $joinedTable; + } + } + + // We only want subselects related to the joined table + $subSelects = array_filter($selects, function ($select) use ($alias) { + return strpos($select, $alias) === 0; + }); + + // Fetch all related option_value entries + $valueField = $alias . '.value'; + $subSelects[] = $valueField; + $tableName = $relatedTable->getTargetTable(); + $conditions = $relatedTable->getExtraJoinConditions(); + $conditions[] = $valueField . ' IN ("' . implode('", "', $values) . '")'; + $subQuery = new \CRM_Utils_SQL_Select($tableName . ' ' . $alias); + $subQuery->where($conditions); + $subQuery->select($subSelects); + $subResults = $subQuery->execute()->fetchAll(); + + return $subResults; + } + +} |