← All changes
|
backend/src/Repository/EntryField/EntryFieldRepository.php
+453
-0
0.6.1
→
1.5
View file →
| @@ -1,0 +1,453 @@ | ||
| 1 | +<?php | |
| 2 | + | |
| 3 | +/** | |
| 4 | + * @copyright © Melograno Venture Studio. All rights reserved. | |
| 5 | + * @licence See LICENCE.md for license details. | |
| 6 | + */ | |
| 7 | + | |
| 8 | +namespace IvyForms\Repository\EntryField; | |
| 9 | + | |
| 10 | +use Exception; | |
| 11 | +use IvyForms\Common\Exceptions\InvalidArgumentException; | |
| 12 | +use IvyForms\Common\Exceptions\QueryExecutionException; | |
| 13 | +use IvyForms\Common\Helpers\EntryQueryHelper; | |
| 14 | +use IvyForms\Factory\EntryField\EntryFieldFactory; | |
| 15 | +use IvyForms\Repository\AbstractRepository; | |
| 16 | +use IvyForms\Services\InstallActions\DB\Entry\EntriesTable; | |
| 17 | +use IvyForms\Services\InstallActions\DB\EntryField\EntryFieldsTable as EntryFieldsTable; | |
| 18 | +use IvyForms\Services\InstallActions\DB\Field\FieldsTable; | |
| 19 | +use IvyForms\Services\Translations\BackendStrings; | |
| 20 | + | |
| 21 | +class EntryFieldRepository extends AbstractRepository implements EntryFieldRepositoryInterface | |
| 22 | +{ | |
| 23 | + public const FACTORY = EntryFieldFactory::class; | |
| 24 | + | |
| 25 | + private const DUPLICATE_NORMALIZED_CHECK_BATCH_SIZE = 500; | |
| 26 | + | |
| 27 | + private const BATCH_INSERT_CHUNK_SIZE = 50; | |
| 28 | + | |
| 29 | + /** | |
| 30 | + * Check if an entry field exists in the database. | |
| 31 | + * | |
| 32 | + * @param int $entryFieldId | |
| 33 | + * @return bool | |
| 34 | + * @throws QueryExecutionException | |
| 35 | + */ | |
| 36 | + public function exists(int $entryFieldId): bool | |
| 37 | + { | |
| 38 | + $query = $this->wpdb->prepare( | |
| 39 | + "SELECT COUNT(*) FROM {$this->table} WHERE id = %d", | |
| 40 | + $entryFieldId | |
| 41 | + ); | |
| 42 | + $count = $this->wpdb->get_var($query); | |
| 43 | + if ($count === false) { | |
| 44 | + throw new QueryExecutionException( | |
| 45 | + BackendStrings::getExceptionStrings()['unable_find_by_id'] . __CLASS__ | |
| 46 | + ); | |
| 47 | + } | |
| 48 | + return (int)$count > 0; | |
| 49 | + } | |
| 50 | + | |
| 51 | + /** | |
| 52 | + * @throws Exception | |
| 53 | + */ | |
| 54 | + public function add(object $entity): int | |
| 55 | + { | |
| 56 | + $data = $entity->toArray(); | |
| 57 | + | |
| 58 | + $result = $this->wpdb->insert( | |
| 59 | + $this->table, | |
| 60 | + [ | |
| 61 | + 'id' => $data['id'] ?? null, | |
| 62 | + 'entryId' => $data['entryId'], | |
| 63 | + 'fieldId' => $data['fieldId'], | |
| 64 | + 'fieldValue' => $data['fieldValue'], | |
| 65 | + ], | |
| 66 | + [ | |
| 67 | + '%d', | |
| 68 | + '%d', | |
| 69 | + '%d', | |
| 70 | + '%s', | |
| 71 | + ], | |
| 72 | + ); | |
| 73 | + | |
| 74 | + if ($result === false) { | |
| 75 | + throw new QueryExecutionException( | |
| 76 | + BackendStrings::getExceptionStrings()['unable_to_add_data'] . __CLASS__ | |
| 77 | + ); | |
| 78 | + } | |
| 79 | + | |
| 80 | + return $this->wpdb->insert_id; | |
| 81 | + } | |
| 82 | + | |
| 83 | + /** | |
| 84 | + * @throws Exception | |
| 85 | + */ | |
| 86 | + public function update(int $id, object $entity): bool | |
| 87 | + { | |
| 88 | + $data = $entity->toArray(); | |
| 89 | + | |
| 90 | + $result = $this->wpdb->update( | |
| 91 | + $this->table, | |
| 92 | + [ | |
| 93 | + 'fieldId' => $data['fieldId'], | |
| 94 | + 'fieldValue' => $data['fieldValue'], | |
| 95 | + ], | |
| 96 | + ['id' => $id], | |
| 97 | + [ | |
| 98 | + '%d', | |
| 99 | + '%s', | |
| 100 | + ], | |
| 101 | + ['%d'] | |
| 102 | + ); | |
| 103 | + | |
| 104 | + if ($result === false) { | |
| 105 | + throw new QueryExecutionException( | |
| 106 | + BackendStrings::getExceptionStrings()['unable_update_data'] . __CLASS__ | |
| 107 | + ); | |
| 108 | + } | |
| 109 | + | |
| 110 | + return (bool) $result; | |
| 111 | + } | |
| 112 | + | |
| 113 | + /** | |
| 114 | + * Find an entry field by its ID. | |
| 115 | + * | |
| 116 | + * @param array<string, string> $array | |
| 117 | + * @return array<int, \IvyForms\Entity\EntryField\EntryField> | |
| 118 | + * @throws QueryExecutionException | |
| 119 | + */ | |
| 120 | + public function findBy(array $array): array | |
| 121 | + { | |
| 122 | + $sql = $this->selectQuery() . " {$this->table}"; | |
| 123 | + $where = []; | |
| 124 | + $values = []; | |
| 125 | + | |
| 126 | + foreach ($array as $field => $value) { | |
| 127 | + $where[] = "{$field} = %s"; | |
| 128 | + $values[] = $value; | |
| 129 | + } | |
| 130 | + | |
| 131 | + if ($where) { | |
| 132 | + $sql .= ' WHERE ' . implode(' AND ', $where); | |
| 133 | + } | |
| 134 | + | |
| 135 | + $query = $this->wpdb->prepare($sql, ...$values); | |
| 136 | + $rows = $this->wpdb->get_results($query, ARRAY_A); | |
| 137 | + if ($rows === false) { | |
| 138 | + throw new QueryExecutionException( | |
| 139 | + BackendStrings::getExceptionStrings()['unable_find_by_id'] . __CLASS__ | |
| 140 | + ); | |
| 141 | + } | |
| 142 | + $results = []; | |
| 143 | + foreach ($rows as $row) { | |
| 144 | + $results[] = call_user_func([static::FACTORY, 'create'], $row); | |
| 145 | + } | |
| 146 | + | |
| 147 | + return $results; | |
| 148 | + } | |
| 149 | + | |
| 150 | + /** | |
| 151 | + * Get all entry field IDs for the given entry IDs in a single query. | |
| 152 | + * | |
| 153 | + * @param int[] $entryIds | |
| 154 | + * @return int[] | |
| 155 | + * @throws QueryExecutionException | |
| 156 | + */ | |
| 157 | + public function findIdsByEntryIds(array $entryIds): array | |
| 158 | + { | |
| 159 | + if (empty($entryIds)) { | |
| 160 | + return []; | |
| 161 | + } | |
| 162 | + $placeholders = implode(',', array_fill(0, count($entryIds), '%d')); | |
| 163 | + $query = "SELECT id FROM {$this->table} WHERE entryId IN ($placeholders)"; | |
| 164 | + $results = $this->wpdb->get_results($this->wpdb->prepare($query, ...$entryIds), ARRAY_A); | |
| 165 | + if ($results === false) { | |
| 166 | + throw new QueryExecutionException( | |
| 167 | + BackendStrings::getExceptionStrings()['unable_find_by_id'] . __CLASS__ | |
| 168 | + ); | |
| 169 | + } | |
| 170 | + return array_map(fn($row) => (int)$row['id'], $results); | |
| 171 | + } | |
| 172 | + | |
| 173 | + /** | |
| 174 | + * Delete entry fields by form IDs using a subquery for fieldId. | |
| 175 | + * | |
| 176 | + * @param int[] $formIds | |
| 177 | + * @return int Number of deleted entry_fields | |
| 178 | + * @throws QueryExecutionException | |
| 179 | + * @throws InvalidArgumentException | |
| 180 | + */ | |
| 181 | + public function deleteByFormIds(array $formIds): int | |
| 182 | + { | |
| 183 | + if (empty($formIds)) { | |
| 184 | + return 0; | |
| 185 | + } | |
| 186 | + $formIds = array_map('intval', $formIds); | |
| 187 | + $placeholders = implode(',', array_fill(0, count($formIds), '%d')); | |
| 188 | + $fieldsTable = FieldsTable::getTableName(); | |
| 189 | + $query = "DELETE FROM {$this->table} WHERE fieldId IN | |
| 190 | + (SELECT id FROM {$fieldsTable} WHERE formId IN ($placeholders))"; | |
| 191 | + $result = $this->wpdb->query($this->wpdb->prepare($query, ...$formIds)); | |
| 192 | + if ($result === false) { | |
| 193 | + throw new QueryExecutionException( | |
| 194 | + BackendStrings::getExceptionStrings()['failed_delete_by_foreign_keys'] | |
| 195 | + ); | |
| 196 | + } | |
| 197 | + return $result; | |
| 198 | + } | |
| 199 | + | |
| 200 | + /** | |
| 201 | + * Check if a field value already exists in the database | |
| 202 | + * | |
| 203 | + * @param int $formId | |
| 204 | + * @param int $fieldId | |
| 205 | + * @param string $value | |
| 206 | + * @return bool | |
| 207 | + * @throws QueryExecutionException | |
| 208 | + */ | |
| 209 | + public function checkDuplicateValue( | |
| 210 | + int $formId, | |
| 211 | + int $fieldId, | |
| 212 | + string $value, | |
| 213 | + ?int $excludeEntryId = null | |
| 214 | + ): bool { | |
| 215 | + global $wpdb; | |
| 216 | + | |
| 217 | + $entriesTable = EntriesTable::getTableName(); | |
| 218 | + | |
| 219 | + $sql = "SELECT 1 | |
| 220 | + FROM {$entriesTable} e | |
| 221 | + INNER JOIN {$this->table} ef ON e.id = ef.entryId | |
| 222 | + WHERE e.formId = %d | |
| 223 | + AND ef.fieldId = %d | |
| 224 | + AND ef.fieldValue = %s"; | |
| 225 | + | |
| 226 | + $params = [$formId, $fieldId, $value]; | |
| 227 | + | |
| 228 | + if ($excludeEntryId !== null && $excludeEntryId > 0) { | |
| 229 | + $sql .= ' AND e.id != %d'; | |
| 230 | + $params[] = $excludeEntryId; | |
| 231 | + } | |
| 232 | + | |
| 233 | + $sql .= ' LIMIT 1'; | |
| 234 | + | |
| 235 | + $exists = $wpdb->get_var($wpdb->prepare($sql, $params)); | |
| 236 | + | |
| 237 | + if ($exists === false) { | |
| 238 | + throw new QueryExecutionException( | |
| 239 | + sprintf( | |
| 240 | + BackendStrings::getExceptionStrings()['failed_check_duplicates'], | |
| 241 | + $wpdb->last_error ?: BackendStrings::getExceptionStrings()['unknown_error'] | |
| 242 | + ) | |
| 243 | + ); | |
| 244 | + } | |
| 245 | + | |
| 246 | + return !empty($exists); | |
| 247 | + } | |
| 248 | + | |
| 249 | + /** | |
| 250 | + * Check if a normalized value matches any stored value after applying a normalizer. | |
| 251 | + * | |
| 252 | + * @param int $formId | |
| 253 | + * @param int $fieldId | |
| 254 | + * @param string $normalizedValue | |
| 255 | + * @param callable(string): string $normalizeStoredValue | |
| 256 | + * @return bool | |
| 257 | + * @throws QueryExecutionException | |
| 258 | + */ | |
| 259 | + public function checkDuplicateValueNormalized( | |
| 260 | + int $formId, | |
| 261 | + int $fieldId, | |
| 262 | + string $normalizedValue, | |
| 263 | + callable $normalizeStoredValue, | |
| 264 | + ?int $excludeEntryId = null | |
| 265 | + ): bool { | |
| 266 | + global $wpdb; | |
| 267 | + | |
| 268 | + $query = $this->buildNormalizedDuplicateCheckQuery( | |
| 269 | + EntriesTable::getTableName(), | |
| 270 | + $formId, | |
| 271 | + $fieldId, | |
| 272 | + $excludeEntryId | |
| 273 | + ); | |
| 274 | + $offset = 0; | |
| 275 | + $limit = self::DUPLICATE_NORMALIZED_CHECK_BATCH_SIZE; | |
| 276 | + | |
| 277 | + while (true) { | |
| 278 | + $rows = $wpdb->get_col($wpdb->prepare($query['sql'], ...array_merge($query['params'], [$limit, $offset]))); | |
| 279 | + | |
| 280 | + if ($rows === false) { | |
| 281 | + throw new QueryExecutionException( | |
| 282 | + sprintf( | |
| 283 | + BackendStrings::getExceptionStrings()['failed_check_duplicates'], | |
| 284 | + $wpdb->last_error ?: BackendStrings::getExceptionStrings()['unknown_error'] | |
| 285 | + ) | |
| 286 | + ); | |
| 287 | + } | |
| 288 | + | |
| 289 | + if ($rows === []) { | |
| 290 | + break; | |
| 291 | + } | |
| 292 | + | |
| 293 | + if ($this->normalizedValueExistsInRows($rows, $normalizedValue, $normalizeStoredValue)) { | |
| 294 | + return true; | |
| 295 | + } | |
| 296 | + | |
| 297 | + if (count($rows) < $limit) { | |
| 298 | + break; | |
| 299 | + } | |
| 300 | + | |
| 301 | + $offset += $limit; | |
| 302 | + } | |
| 303 | + | |
| 304 | + return false; | |
| 305 | + } | |
| 306 | + | |
| 307 | + /** | |
| 308 | + * @return array{sql: string, params: array<int, int>} | |
| 309 | + */ | |
| 310 | + private function buildNormalizedDuplicateCheckQuery( | |
| 311 | + string $entriesTable, | |
| 312 | + int $formId, | |
| 313 | + int $fieldId, | |
| 314 | + ?int $excludeEntryId | |
| 315 | + ): array { | |
| 316 | + $sql = "SELECT ef.fieldValue | |
| 317 | + FROM {$entriesTable} e | |
| 318 | + INNER JOIN {$this->table} ef ON e.id = ef.entryId | |
| 319 | + WHERE e.formId = %d | |
| 320 | + AND ef.fieldId = %d"; | |
| 321 | + | |
| 322 | + $params = [$formId, $fieldId]; | |
| 323 | + | |
| 324 | + if ($excludeEntryId !== null && $excludeEntryId > 0) { | |
| 325 | + $sql .= ' AND e.id != %d'; | |
| 326 | + $params[] = $excludeEntryId; | |
| 327 | + } | |
| 328 | + | |
| 329 | + $sql .= ' LIMIT %d OFFSET %d'; | |
| 330 | + | |
| 331 | + return ['sql' => $sql, 'params' => $params]; | |
| 332 | + } | |
| 333 | + | |
| 334 | + /** | |
| 335 | + * @param string[] $rows | |
| 336 | + */ | |
| 337 | + private function normalizedValueExistsInRows( | |
| 338 | + array $rows, | |
| 339 | + string $normalizedValue, | |
| 340 | + callable $normalizeStoredValue | |
| 341 | + ): bool { | |
| 342 | + foreach ($rows as $stored) { | |
| 343 | + if ($normalizeStoredValue((string) $stored) === $normalizedValue) { | |
| 344 | + return true; | |
| 345 | + } | |
| 346 | + } | |
| 347 | + | |
| 348 | + return false; | |
| 349 | + } | |
| 350 | + | |
| 351 | + /** | |
| 352 | + * Batch-update fieldValue for existing entry_field rows of one entry. | |
| 353 | + * | |
| 354 | + * @param int $entryId | |
| 355 | + * @param list<array{id: int, fieldValue: string}> $updates | |
| 356 | + * @throws QueryExecutionException | |
| 357 | + */ | |
| 358 | + public function batchUpdateValues(int $entryId, array $updates): void | |
| 359 | + { | |
| 360 | + if ($updates === []) { | |
| 361 | + return; | |
| 362 | + } | |
| 363 | + | |
| 364 | + $caseFragments = []; | |
| 365 | + $caseValues = []; | |
| 366 | + $ids = []; | |
| 367 | + | |
| 368 | + foreach ($updates as $update) { | |
| 369 | + $id = (int) $update['id']; | |
| 370 | + if ($id <= 0) { | |
| 371 | + continue; | |
| 372 | + } | |
| 373 | + $caseFragments[] = 'WHEN %d THEN %s'; | |
| 374 | + $caseValues[] = $id; | |
| 375 | + $caseValues[] = (string) $update['fieldValue']; | |
| 376 | + $ids[] = $id; | |
| 377 | + } | |
| 378 | + | |
| 379 | + if ($ids === []) { | |
| 380 | + return; | |
| 381 | + } | |
| 382 | + | |
| 383 | + $idPlaceholders = implode(',', array_fill(0, count($ids), '%d')); | |
| 384 | + $sql = "UPDATE {$this->table} | |
| 385 | + SET fieldValue = CASE id " . implode(' ', $caseFragments) . " END | |
| 386 | + WHERE entryId = %d AND id IN ({$idPlaceholders})"; | |
| 387 | + | |
| 388 | + $params = array_merge($caseValues, [$entryId], $ids); | |
| 389 | + $result = $this->wpdb->query($this->wpdb->prepare($sql, ...$params)); | |
| 390 | + | |
| 391 | + if ($result === false) { | |
| 392 | + throw new QueryExecutionException( | |
| 393 | + BackendStrings::getExceptionStrings()['unable_update_data'] . __CLASS__ | |
| 394 | + ); | |
| 395 | + } | |
| 396 | + } | |
| 397 | + | |
| 398 | + /** | |
| 399 | + * Multi-row insert of entry_field rows. Sets generated IDs on the entities. | |
| 400 | + * Inserts in chunks to stay under database packet / query-size limits. | |
| 401 | + * | |
| 402 | + * @param list<\IvyForms\Entity\EntryField\EntryField> $entryFields | |
| 403 | + * @throws QueryExecutionException | |
| 404 | + */ | |
| 405 | + public function batchInsert(array $entryFields): void | |
| 406 | + { | |
| 407 | + if ($entryFields === []) { | |
| 408 | + return; | |
| 409 | + } | |
| 410 | + | |
| 411 | + foreach (array_chunk($entryFields, self::BATCH_INSERT_CHUNK_SIZE) as $chunk) { | |
| 412 | + $this->insertEntryFieldChunk($chunk); | |
| 413 | + } | |
| 414 | + } | |
| 415 | + | |
| 416 | + /** | |
| 417 | + * @param list<\IvyForms\Entity\EntryField\EntryField> $entryFields | |
| 418 | + * @throws QueryExecutionException | |
| 419 | + */ | |
| 420 | + private function insertEntryFieldChunk(array $entryFields): void | |
| 421 | + { | |
| 422 | + $placeholders = []; | |
| 423 | + $values = []; | |
| 424 | + | |
| 425 | + foreach ($entryFields as $entryField) { | |
| 426 | + $data = $entryField->toArray(); | |
| 427 | + $placeholders[] = '(%d, %d, %s)'; | |
| 428 | + $values[] = (int) $data['entryId']; | |
| 429 | + $values[] = (int) $data['fieldId']; | |
| 430 | + $values[] = (string) $data['fieldValue']; | |
| 431 | + } | |
| 432 | + | |
| 433 | + $sql = "INSERT INTO {$this->table} (entryId, fieldId, fieldValue) VALUES " | |
| 434 | + . implode(', ', $placeholders); | |
| 435 | + $result = $this->wpdb->query($this->wpdb->prepare($sql, ...$values)); | |
| 436 | + | |
| 437 | + if ($result === false) { | |
| 438 | + throw new QueryExecutionException( | |
| 439 | + BackendStrings::getExceptionStrings()['unable_to_add_data'] . __CLASS__ | |
| 440 | + ); | |
| 441 | + } | |
| 442 | + | |
| 443 | + $firstId = (int) $this->wpdb->insert_id; | |
| 444 | + if ($firstId <= 0) { | |
| 445 | + return; | |
| 446 | + } | |
| 447 | + | |
| 448 | + $step = EntryQueryHelper::getAutoIncrementStep($this->wpdb); | |
| 449 | + foreach ($entryFields as $index => $entryField) { | |
| 450 | + $entryField->setId($firstId + $index * $step); | |
| 451 | + } | |
| 452 | + } | |
| 453 | +} | |