Commit | Line | Data |
---|---|---|
19b53e5b C |
1 | <?php |
2 | ||
380f3545 TO |
3 | /* |
4 | +--------------------------------------------------------------------+ | |
41498ac5 | 5 | | Copyright CiviCRM LLC. All rights reserved. | |
380f3545 | 6 | | | |
41498ac5 TO |
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 | | |
380f3545 TO |
10 | +--------------------------------------------------------------------+ |
11 | */ | |
12 | ||
13 | /** | |
14 | * | |
15 | * @package CRM | |
ca5cec67 | 16 | * @copyright CiviCRM LLC https://civicrm.org/licensing |
380f3545 TO |
17 | * $Id$ |
18 | * | |
19 | */ | |
20 | ||
21 | ||
19b53e5b C |
22 | namespace Civi\Api4\Event\Subscriber; |
23 | ||
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; | |
29 | ||
30 | /** | |
31 | * Changes the results of a select query, doing 1-n joins and unserializing data | |
32 | */ | |
33 | class PostSelectQuerySubscriber implements EventSubscriberInterface { | |
34 | ||
35 | /** | |
36 | * @inheritdoc | |
37 | */ | |
38 | public static function getSubscribedEvents() { | |
39 | return [ | |
40 | Events::POST_SELECT_QUERY => 'onPostQuery', | |
41 | ]; | |
42 | } | |
43 | ||
44 | /** | |
45 | * @param \Civi\Api4\Event\PostSelectQueryEvent $event | |
46 | */ | |
47 | public function onPostQuery(PostSelectQueryEvent $event) { | |
48 | $results = $event->getResults(); | |
49 | $event->setResults($this->postRun($results, $event->getQuery())); | |
50 | } | |
51 | ||
52 | /** | |
53 | * @param array $results | |
54 | * @param \Civi\Api4\Query\Api4SelectQuery $query | |
55 | * | |
56 | * @return array | |
57 | */ | |
58 | protected function postRun(array $results, Api4SelectQuery $query) { | |
59 | if (empty($results)) { | |
60 | return $results; | |
61 | } | |
62 | ||
aec2fb31 | 63 | $this->formatFieldValues($results, $query->getApiFieldSpec()); |
19b53e5b C |
64 | |
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); | |
72 | ||
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) { | |
aec2fb31 | 77 | return ($res['_base_id'] == $baseId); |
19b53e5b C |
78 | }); |
79 | $filtered = array_values($filtered); | |
80 | ArrayInsertionUtil::insert($primaryResult, $joinPath, $filtered); | |
81 | } | |
82 | } | |
83 | ||
84 | return array_values($results); | |
85 | } | |
86 | ||
87 | /** | |
88 | * @param array $joinResults | |
89 | * @param \Civi\Api4\Query\Api4SelectQuery $query | |
90 | * @param string $alias | |
91 | */ | |
92 | private function formatJoinResults(&$joinResults, $query, $alias) { | |
93 | $join = $query->getJoinedTable($alias); | |
94 | $fields = []; | |
95 | foreach ($join->getEntityFields() as $field) { | |
96 | $name = explode('.', $field->getName()); | |
97 | $fields[array_pop($name)] = $field->toArray(); | |
98 | } | |
99 | if ($fields) { | |
aec2fb31 | 100 | $this->formatFieldValues($joinResults, $fields); |
19b53e5b C |
101 | } |
102 | } | |
103 | ||
104 | /** | |
aec2fb31 | 105 | * Unserialize values and convert to correct type |
19b53e5b C |
106 | * |
107 | * @param array $results | |
19b53e5b C |
108 | * @param array $fields |
109 | */ | |
aec2fb31 | 110 | protected function formatFieldValues(&$results, $fields = []) { |
19b53e5b | 111 | foreach ($results as &$result) { |
aec2fb31 CW |
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); | |
119 | } | |
120 | } | |
19b53e5b | 121 | } |
aec2fb31 CW |
122 | else { |
123 | $result[$field] = $this->convertDataType($value, $dataType); | |
124 | } | |
125 | } | |
126 | } | |
127 | } | |
128 | ||
129 | /** | |
130 | * @param mixed $value | |
131 | * @param string $dataType | |
132 | * @return mixed | |
133 | */ | |
134 | protected function convertDataType($value, $dataType) { | |
135 | if (isset($value)) { | |
136 | switch ($dataType) { | |
137 | case 'Boolean': | |
138 | return (bool) $value; | |
139 | ||
140 | case 'Integer': | |
141 | return (int) $value; | |
142 | ||
143 | case 'Money': | |
144 | case 'Float': | |
145 | return (float) $value; | |
19b53e5b C |
146 | } |
147 | } | |
aec2fb31 | 148 | return $value; |
19b53e5b C |
149 | } |
150 | ||
151 | /** | |
152 | * Find only those joins that need to be handled by a separate query and weren't done in the main query. | |
153 | * | |
154 | * @param \Civi\Api4\Query\Api4SelectQuery $query | |
155 | * | |
156 | * @return array | |
157 | */ | |
158 | private function getNtoManyJoinSelects(Api4SelectQuery $query) { | |
159 | $fkAliases = $query->getFkSelectAliases(); | |
160 | $joinedDotSelects = array_filter( | |
161 | $query->getSelect(), | |
162 | function ($select) use ($fkAliases, $query) { | |
163 | return isset($fkAliases[$select]) && array_filter($query->getPathJoinTypes($select)); | |
164 | } | |
165 | ); | |
166 | ||
167 | $selects = []; | |
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; | |
173 | } | |
174 | ||
175 | // sort by depth, e.g. email selects should be done before email.location | |
176 | uasort($selects, function ($a, $b) { | |
177 | $aFirst = $a[0]; | |
178 | $bFirst = $b[0]; | |
179 | return substr_count($aFirst, '.') > substr_count($bFirst, '.'); | |
180 | }); | |
181 | ||
182 | return $selects; | |
183 | } | |
184 | ||
185 | /** | |
186 | * @param array $selects | |
187 | * @param $serializationType | |
188 | * @param \Civi\Api4\Query\Api4SelectQuery $query | |
189 | * | |
190 | * @return array | |
191 | */ | |
192 | private function getResultsForSerializedField( | |
193 | array $selects, | |
194 | $serializationType, | |
195 | Api4SelectQuery $query | |
196 | ) { | |
197 | // Get the alias (Selects are grouped and all target the same table) | |
198 | $sampleField = current($selects); | |
199 | $alias = strstr($sampleField, '.', TRUE); | |
200 | ||
201 | // Fetch the results with the serialized field | |
202 | $selects['serialized'] = $query::MAIN_TABLE_ALIAS . '.' . $alias; | |
203 | $serializedResults = $this->runWithNewSelects($selects, $query); | |
204 | $newResults = []; | |
205 | ||
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'], | |
210 | $serializationType | |
211 | ); | |
212 | unset($result['serialized']); | |
213 | foreach ($optionValues as $value) { | |
214 | $newResults[] = array_merge($result, ['value' => $value]); | |
215 | } | |
216 | } | |
217 | ||
218 | $optionValueValues = array_unique(array_column($newResults, 'value')); | |
219 | $optionValues = $this->getOptionValuesFromValues( | |
220 | $selects, | |
221 | $query, | |
222 | $optionValueValues | |
223 | ); | |
224 | $valueField = $alias . '.value'; | |
225 | ||
226 | // Index by value | |
227 | foreach ($optionValues as $key => $subResult) { | |
228 | $optionValues[$subResult['value']] = $subResult; | |
229 | unset($subResult[$key]); | |
230 | ||
231 | // Exclude 'value' if not in original selects | |
232 | if (!in_array($valueField, $selects)) { | |
233 | unset($optionValues[$subResult['value']]['value']); | |
234 | } | |
235 | } | |
236 | ||
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']); | |
241 | } | |
242 | ||
243 | return $newResults; | |
244 | } | |
245 | ||
246 | /** | |
247 | * Prepares selects for the subquery to fetch join results | |
248 | * | |
249 | * @param string $alias | |
250 | * @param array $selects | |
251 | * @param \Civi\Api4\Query\Api4SelectQuery $query | |
252 | * | |
253 | * @return array | |
254 | */ | |
255 | private function formatSelects($alias, $selects, Api4SelectQuery $query) { | |
256 | $mainAlias = $query::MAIN_TABLE_ALIAS; | |
257 | $selectFields = []; | |
258 | ||
259 | foreach ($selects as $select) { | |
260 | $selectAlias = $query->getFkSelectAliases()[$select]; | |
261 | $fieldAlias = substr($select, strrpos($select, '.') + 1); | |
262 | $selectFields[$fieldAlias] = $selectAlias; | |
263 | } | |
264 | ||
265 | $firstSelect = $selects[0]; | |
266 | $pathParts = explode('.', $firstSelect); | |
267 | $numParts = count($pathParts); | |
268 | $parentAlias = $numParts > 2 ? $pathParts[$numParts - 3] : $mainAlias; | |
269 | ||
270 | $selectFields['id'] = sprintf('%s.id', $alias); | |
271 | $selectFields['_parent_id'] = $parentAlias . '.id'; | |
272 | $selectFields['_base_id'] = $mainAlias . '.id'; | |
273 | ||
274 | return $selectFields; | |
275 | } | |
276 | ||
277 | /** | |
278 | * @param array $selects | |
279 | * @param \Civi\Api4\Query\Api4SelectQuery $query | |
280 | * | |
281 | * @return array | |
282 | */ | |
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)); | |
287 | ||
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); | |
292 | ||
293 | $relatedResults = []; | |
294 | $resultDAO = \CRM_Core_DAO::executeQuery($sql); | |
295 | while ($resultDAO->fetch()) { | |
296 | $relatedResult = []; | |
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; | |
302 | } | |
303 | }; | |
304 | $relatedResults[] = $relatedResult; | |
305 | } | |
306 | ||
307 | return $relatedResults; | |
308 | } | |
309 | ||
310 | /** | |
311 | * @param \Civi\Api4\Query\Api4SelectQuery $query | |
312 | * @param $alias | |
313 | * @param $selects | |
314 | * @return array | |
315 | */ | |
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); | |
321 | } | |
322 | else { | |
323 | $joinResults = $this->runWithNewSelects($selects, $query); | |
324 | } | |
325 | ||
326 | // Remove results with no matching entries | |
327 | $joinResults = array_filter($joinResults, function ($result) { | |
328 | return !empty($result['id']); | |
329 | }); | |
330 | ||
331 | return $joinResults; | |
332 | } | |
333 | ||
334 | /** | |
335 | * Get all the option_value values required in the query | |
336 | * | |
337 | * @param array $selects | |
338 | * @param \Civi\Api4\Query\Api4SelectQuery $query | |
339 | * @param array $values | |
340 | * | |
341 | * @return array | |
342 | */ | |
343 | private function getOptionValuesFromValues( | |
344 | array $selects, | |
345 | Api4SelectQuery $query, | |
346 | array $values | |
347 | ) { | |
348 | $sampleField = current($selects); | |
349 | $alias = strstr($sampleField, '.', TRUE); | |
350 | ||
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; | |
356 | } | |
357 | } | |
358 | ||
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; | |
362 | }); | |
363 | ||
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(); | |
374 | ||
375 | return $subResults; | |
376 | } | |
377 | ||
378 | } |