4 +--------------------------------------------------------------------+
5 | Copyright CiviCRM LLC. All rights reserved. |
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 +--------------------------------------------------------------------+
16 * @copyright CiviCRM LLC https://civicrm.org/licensing
22 namespace Civi\Api4\Event\Subscriber
;
24 use Civi\Api4\Event\Events
;
25 use Civi\Api4\Event\PostSelectQueryEvent
;
26 use Civi\Api4\Query\Api4SelectQuery
;
27 use Civi\Api4\Utils\ArrayInsertionUtil
;
28 use Symfony\Component\EventDispatcher\EventSubscriberInterface
;
31 * Changes the results of a select query, doing 1-n joins and unserializing data
33 class PostSelectQuerySubscriber
implements EventSubscriberInterface
{
38 public static function getSubscribedEvents() {
40 Events
::POST_SELECT_QUERY
=> 'onPostQuery',
45 * @param \Civi\Api4\Event\PostSelectQueryEvent $event
47 public function onPostQuery(PostSelectQueryEvent
$event) {
48 $results = $event->getResults();
49 $event->setResults($this->postRun($results, $event->getQuery()));
53 * @param array $results
54 * @param \Civi\Api4\Query\Api4SelectQuery $query
58 protected function postRun(array $results, Api4SelectQuery
$query) {
59 if (empty($results)) {
63 $this->formatFieldValues($results, $query->getApiFieldSpec());
65 // Group the selects to avoid queries for each field
66 $groupedSelects = $this->getNtoManyJoinSelects($query);
67 foreach ($groupedSelects as $finalAlias => $selects) {
68 $joinPath = $query->getPathJoinTypes($selects[0]);
69 $selects = $this->formatSelects($finalAlias, $selects, $query);
70 $joinResults = $this->getJoinResults($query, $finalAlias, $selects);
71 $this->formatJoinResults($joinResults, $query, $finalAlias);
73 // Insert join results into original result
74 foreach ($results as &$primaryResult) {
75 $baseId = $primaryResult['id'];
76 $filtered = array_filter($joinResults, function ($res) use ($baseId) {
77 return ($res['_base_id'] == $baseId);
79 $filtered = array_values($filtered);
80 ArrayInsertionUtil
::insert($primaryResult, $joinPath, $filtered);
84 return array_values($results);
88 * @param array $joinResults
89 * @param \Civi\Api4\Query\Api4SelectQuery $query
90 * @param string $alias
92 private function formatJoinResults(&$joinResults, $query, $alias) {
93 $join = $query->getJoinedTable($alias);
95 foreach ($join->getEntityFields() as $field) {
96 $name = explode('.', $field->getName());
97 $fields[array_pop($name)] = $field->toArray();
100 $this->formatFieldValues($joinResults, $fields);
105 * Unserialize values and convert to correct type
107 * @param array $results
108 * @param array $fields
110 protected function formatFieldValues(&$results, $fields = []) {
111 foreach ($results as &$result) {
112 foreach ($result as $field => $value) {
113 $dataType = $fields[$field]['data_type'] ??
NULL;
114 if (!empty($fields[$field]['serialize'])) {
115 if (is_string($value)) {
116 $result[$field] = $value = \CRM_Core_DAO
::unSerializeField($value, $fields[$field]['serialize']);
117 foreach ($value as $key => $val) {
118 $result[$field][$key] = $this->convertDataType($val, $dataType);
123 $result[$field] = $this->convertDataType($value, $dataType);
130 * @param mixed $value
131 * @param string $dataType
134 protected function convertDataType($value, $dataType) {
138 return (bool) $value;
145 return (float) $value;
152 * Find only those joins that need to be handled by a separate query and weren't done in the main query.
154 * @param \Civi\Api4\Query\Api4SelectQuery $query
158 private function getNtoManyJoinSelects(Api4SelectQuery
$query) {
159 $fkAliases = $query->getFkSelectAliases();
160 $joinedDotSelects = array_filter(
162 function ($select) use ($fkAliases, $query) {
163 return isset($fkAliases[$select]) && array_filter($query->getPathJoinTypes($select));
168 // group related selects by alias so they can be executed in one query
169 foreach ($joinedDotSelects as $select) {
170 $parts = explode('.', $select);
171 $finalAlias = $parts[count($parts) - 2];
172 $selects[$finalAlias][] = $select;
175 // sort by depth, e.g. email selects should be done before email.location
176 uasort($selects, function ($a, $b) {
179 return substr_count($aFirst, '.') > substr_count($bFirst, '.');
186 * @param array $selects
187 * @param $serializationType
188 * @param \Civi\Api4\Query\Api4SelectQuery $query
192 private function getResultsForSerializedField(
195 Api4SelectQuery
$query
197 // Get the alias (Selects are grouped and all target the same table)
198 $sampleField = current($selects);
199 $alias = strstr($sampleField, '.', TRUE);
201 // Fetch the results with the serialized field
202 $selects['serialized'] = $query::MAIN_TABLE_ALIAS
. '.' . $alias;
203 $serializedResults = $this->runWithNewSelects($selects, $query);
206 // Create a new results array, with a separate entry for each option value
207 foreach ($serializedResults as $result) {
208 $optionValues = \CRM_Core_DAO
::unSerializeField(
209 $result['serialized'],
212 unset($result['serialized']);
213 foreach ($optionValues as $value) {
214 $newResults[] = array_merge($result, ['value' => $value]);
218 $optionValueValues = array_unique(array_column($newResults, 'value'));
219 $optionValues = $this->getOptionValuesFromValues(
224 $valueField = $alias . '.value';
227 foreach ($optionValues as $key => $subResult) {
228 $optionValues[$subResult['value']] = $subResult;
229 unset($subResult[$key]);
231 // Exclude 'value' if not in original selects
232 if (!in_array($valueField, $selects)) {
233 unset($optionValues[$subResult['value']]['value']);
237 // Replace serialized with the sub-select results
238 foreach ($newResults as &$result) {
239 $result = array_merge($result, $optionValues[$result['value']]);
240 unset($result['value']);
247 * Prepares selects for the subquery to fetch join results
249 * @param string $alias
250 * @param array $selects
251 * @param \Civi\Api4\Query\Api4SelectQuery $query
255 private function formatSelects($alias, $selects, Api4SelectQuery
$query) {
256 $mainAlias = $query::MAIN_TABLE_ALIAS
;
259 foreach ($selects as $select) {
260 $selectAlias = $query->getFkSelectAliases()[$select];
261 $fieldAlias = substr($select, strrpos($select, '.') +
1);
262 $selectFields[$fieldAlias] = $selectAlias;
265 $firstSelect = $selects[0];
266 $pathParts = explode('.', $firstSelect);
267 $numParts = count($pathParts);
268 $parentAlias = $numParts > 2 ?
$pathParts[$numParts - 3] : $mainAlias;
270 $selectFields['id'] = sprintf('%s.id', $alias);
271 $selectFields['_parent_id'] = $parentAlias . '.id';
272 $selectFields['_base_id'] = $mainAlias . '.id';
274 return $selectFields;
278 * @param array $selects
279 * @param \Civi\Api4\Query\Api4SelectQuery $query
283 private function runWithNewSelects(array $selects, Api4SelectQuery
$query) {
284 $aliasedSelects = array_map(function ($field, $alias) {
285 return sprintf('%s as "%s"', $field, $alias);
286 }, $selects, array_keys($selects));
288 $newSelect = sprintf('SELECT DISTINCT %s', implode(", ", $aliasedSelects));
289 $sql = str_replace("\n", ' ', $query->getQuery()->toSQL());
290 $originalSelect = substr($sql, 0, strpos($sql, ' FROM'));
291 $sql = str_replace($originalSelect, $newSelect, $sql);
293 $relatedResults = [];
294 $resultDAO = \CRM_Core_DAO
::executeQuery($sql);
295 while ($resultDAO->fetch()) {
297 foreach ($selects as $alias => $column) {
298 $returnName = $alias;
299 $alias = str_replace('.', '_', $alias);
300 if (property_exists($resultDAO, $alias)) {
301 $relatedResult[$returnName] = $resultDAO->$alias;
304 $relatedResults[] = $relatedResult;
307 return $relatedResults;
311 * @param \Civi\Api4\Query\Api4SelectQuery $query
316 protected function getJoinResults(Api4SelectQuery
$query, $alias, $selects) {
317 $apiFieldSpec = $query->getApiFieldSpec();
318 if (!empty($apiFieldSpec[$alias]['serialize'])) {
319 $type = $apiFieldSpec[$alias]['serialize'];
320 $joinResults = $this->getResultsForSerializedField($selects, $type, $query);
323 $joinResults = $this->runWithNewSelects($selects, $query);
326 // Remove results with no matching entries
327 $joinResults = array_filter($joinResults, function ($result) {
328 return !empty($result['id']);
335 * Get all the option_value values required in the query
337 * @param array $selects
338 * @param \Civi\Api4\Query\Api4SelectQuery $query
339 * @param array $values
343 private function getOptionValuesFromValues(
345 Api4SelectQuery
$query,
348 $sampleField = current($selects);
349 $alias = strstr($sampleField, '.', TRUE);
351 // Get the option value table that was joined
352 $relatedTable = NULL;
353 foreach ($query->getJoinedTables() as $joinedTable) {
354 if ($joinedTable->getAlias() === $alias) {
355 $relatedTable = $joinedTable;
359 // We only want subselects related to the joined table
360 $subSelects = array_filter($selects, function ($select) use ($alias) {
361 return strpos($select, $alias) === 0;
364 // Fetch all related option_value entries
365 $valueField = $alias . '.value';
366 $subSelects[] = $valueField;
367 $tableName = $relatedTable->getTargetTable();
368 $conditions = $relatedTable->getExtraJoinConditions();
369 $conditions[] = $valueField . ' IN ("' . implode('", "', $values) . '")';
370 $subQuery = new \
CRM_Utils_SQL_Select($tableName . ' ' . $alias);
371 $subQuery->where($conditions);
372 $subQuery->select($subSelects);
373 $subResults = $subQuery->execute()->fetchAll();