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 Civi\Api4\Utils\FormattingUtil
;
29 use Symfony\Component\EventDispatcher\EventSubscriberInterface
;
32 * Changes the results of a select query, doing 1-n joins and unserializing data
34 class PostSelectQuerySubscriber
implements EventSubscriberInterface
{
39 public static function getSubscribedEvents() {
41 Events
::POST_SELECT_QUERY
=> 'onPostQuery',
46 * @param \Civi\Api4\Event\PostSelectQueryEvent $event
48 public function onPostQuery(PostSelectQueryEvent
$event) {
49 $results = $event->getResults();
50 $event->setResults($this->postRun($results, $event->getQuery()));
54 * @param array $results
55 * @param \Civi\Api4\Query\Api4SelectQuery $query
59 protected function postRun(array $results, Api4SelectQuery
$query) {
60 if (empty($results)) {
64 FormattingUtil
::formatOutputValues($results, $query->getApiFieldSpec(), $query->getEntity());
66 // Group the selects to avoid queries for each field
67 $groupedSelects = $this->getNtoManyJoinSelects($query);
68 foreach ($groupedSelects as $finalAlias => $selects) {
69 $joinPath = $query->getPathJoinTypes($selects[0]);
70 $selects = $this->formatSelects($finalAlias, $selects, $query);
71 $joinResults = $this->getJoinResults($query, $finalAlias, $selects);
72 $this->formatJoinResults($joinResults, $query, $finalAlias);
74 // Insert join results into original result
75 foreach ($results as &$primaryResult) {
76 $baseId = $primaryResult['id'];
77 $filtered = array_filter($joinResults, function ($res) use ($baseId) {
78 return ($res['_base_id'] == $baseId);
80 $filtered = array_values($filtered);
81 ArrayInsertionUtil
::insert($primaryResult, $joinPath, $filtered);
85 return array_values($results);
89 * @param array $joinResults
90 * @param \Civi\Api4\Query\Api4SelectQuery $query
91 * @param string $alias
93 private function formatJoinResults(&$joinResults, $query, $alias) {
94 $join = $query->getJoinedTable($alias);
96 foreach ($join->getEntityFields() as $field) {
97 $name = explode('.', $field->getName());
98 $fields[array_pop($name)] = $field->toArray();
101 FormattingUtil
::formatOutputValues($joinResults, $fields, $join->getEntity());
106 * Find only those joins that need to be handled by a separate query and weren't done in the main query.
108 * @param \Civi\Api4\Query\Api4SelectQuery $query
112 private function getNtoManyJoinSelects(Api4SelectQuery
$query) {
113 $fkAliases = $query->getFkSelectAliases();
114 $joinedDotSelects = array_filter(
116 function ($select) use ($fkAliases, $query) {
117 return isset($fkAliases[$select]) && array_filter($query->getPathJoinTypes($select));
122 // group related selects by alias so they can be executed in one query
123 foreach ($joinedDotSelects as $select) {
124 $parts = explode('.', $select);
125 $finalAlias = $parts[count($parts) - 2];
126 $selects[$finalAlias][] = $select;
129 // sort by depth, e.g. email selects should be done before email.location
130 uasort($selects, function ($a, $b) {
133 return substr_count($aFirst, '.') > substr_count($bFirst, '.');
140 * @param array $selects
141 * @param $serializationType
142 * @param \Civi\Api4\Query\Api4SelectQuery $query
146 private function getResultsForSerializedField(
149 Api4SelectQuery
$query
151 // Get the alias (Selects are grouped and all target the same table)
152 $sampleField = current($selects);
153 $alias = strstr($sampleField, '.', TRUE);
155 // Fetch the results with the serialized field
156 $selects['serialized'] = $query::MAIN_TABLE_ALIAS
. '.' . $alias;
157 $serializedResults = $this->runWithNewSelects($selects, $query);
160 // Create a new results array, with a separate entry for each option value
161 foreach ($serializedResults as $result) {
162 $optionValues = \CRM_Core_DAO
::unSerializeField(
163 $result['serialized'],
166 unset($result['serialized']);
167 foreach ($optionValues as $value) {
168 $newResults[] = array_merge($result, ['value' => $value]);
172 $optionValueValues = array_unique(array_column($newResults, 'value'));
173 $optionValues = $this->getOptionValuesFromValues(
178 $valueField = $alias . '.value';
181 foreach ($optionValues as $key => $subResult) {
182 $optionValues[$subResult['value']] = $subResult;
183 unset($subResult[$key]);
185 // Exclude 'value' if not in original selects
186 if (!in_array($valueField, $selects)) {
187 unset($optionValues[$subResult['value']]['value']);
191 // Replace serialized with the sub-select results
192 foreach ($newResults as &$result) {
193 $result = array_merge($result, $optionValues[$result['value']]);
194 unset($result['value']);
201 * Prepares selects for the subquery to fetch join results
203 * @param string $alias
204 * @param array $selects
205 * @param \Civi\Api4\Query\Api4SelectQuery $query
209 private function formatSelects($alias, $selects, Api4SelectQuery
$query) {
210 $mainAlias = $query::MAIN_TABLE_ALIAS
;
213 foreach ($selects as $select) {
214 $selectAlias = $query->getFkSelectAliases()[$select];
215 $fieldAlias = substr($select, strrpos($select, '.') +
1);
216 $selectFields[$fieldAlias] = $selectAlias;
219 $firstSelect = $selects[0];
220 $pathParts = explode('.', $firstSelect);
221 $numParts = count($pathParts);
222 $parentAlias = $numParts > 2 ?
$pathParts[$numParts - 3] : $mainAlias;
224 $selectFields['id'] = sprintf('%s.id', $alias);
225 $selectFields['_parent_id'] = $parentAlias . '.id';
226 $selectFields['_base_id'] = $mainAlias . '.id';
228 return $selectFields;
232 * @param array $selects
233 * @param \Civi\Api4\Query\Api4SelectQuery $query
237 private function runWithNewSelects(array $selects, Api4SelectQuery
$query) {
238 $aliasedSelects = array_map(function ($field, $alias) {
239 return sprintf('%s as "%s"', $field, $alias);
240 }, $selects, array_keys($selects));
242 $newSelect = sprintf('SELECT DISTINCT %s', implode(", ", $aliasedSelects));
243 $sql = $query->getQuery()->toSQL();
244 // Replace the "SELECT" clause
245 $sql = $newSelect . substr($sql, strpos($sql, "\nFROM"));
247 if (is_array($query->debugOutput
)) {
248 $query->debugOutput
['sql'][] = $sql;
251 $relatedResults = [];
252 $resultDAO = \CRM_Core_DAO
::executeQuery($sql);
253 while ($resultDAO->fetch()) {
255 foreach ($selects as $alias => $column) {
256 $returnName = $alias;
257 $alias = str_replace('.', '_', $alias);
258 if (property_exists($resultDAO, $alias)) {
259 $relatedResult[$returnName] = $resultDAO->$alias;
262 $relatedResults[] = $relatedResult;
265 return $relatedResults;
269 * @param \Civi\Api4\Query\Api4SelectQuery $query
274 protected function getJoinResults(Api4SelectQuery
$query, $alias, $selects) {
275 $apiFieldSpec = $query->getApiFieldSpec();
276 if (!empty($apiFieldSpec[$alias]['serialize'])) {
277 $type = $apiFieldSpec[$alias]['serialize'];
278 $joinResults = $this->getResultsForSerializedField($selects, $type, $query);
281 $joinResults = $this->runWithNewSelects($selects, $query);
284 // Remove results with no matching entries
285 $joinResults = array_filter($joinResults, function ($result) {
286 return !empty($result['id']);
293 * Get all the option_value values required in the query
295 * @param array $selects
296 * @param \Civi\Api4\Query\Api4SelectQuery $query
297 * @param array $values
301 private function getOptionValuesFromValues(
303 Api4SelectQuery
$query,
306 $sampleField = current($selects);
307 $alias = strstr($sampleField, '.', TRUE);
309 // Get the option value table that was joined
310 $relatedTable = NULL;
311 foreach ($query->getJoinedTables() as $joinedTable) {
312 if ($joinedTable->getAlias() === $alias) {
313 $relatedTable = $joinedTable;
317 // We only want subselects related to the joined table
318 $subSelects = array_filter($selects, function ($select) use ($alias) {
319 return strpos($select, $alias) === 0;
322 // Fetch all related option_value entries
323 $valueField = $alias . '.value';
324 $subSelects[] = $valueField;
325 $tableName = $relatedTable->getTargetTable();
326 $conditions = $relatedTable->getExtraJoinConditions();
327 $conditions[] = $valueField . ' IN ("' . implode('", "', $values) . '")';
328 $subQuery = new \
CRM_Utils_SQL_Select($tableName . ' ' . $alias);
329 $subQuery->where($conditions);
330 $subQuery->select($subSelects);
331 $subResults = $subQuery->execute()->fetchAll();