ivyforms / backend / src / Repository / EntryField / EntryFieldRepository.php

EntryFieldRepository.php in The Innovative Form Builder – IvyForms 1.5, at backend/src/Repository/EntryField/EntryFieldRepository.php

454 lines 13.2 KB

Raw Download Zip

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	}
454