← All changes | backend/src/Repository/Entry/EntryRepository.php +84 -64 0.4 → 1.5 View file →

		@@ -1,23 +1,21 @@
1	1	<?php
2	2
3	3	/**
4		- * @copyright © TMS-Plugins. All rights reserved.
	4	+ * @copyright © Melograno Venture Studio. All rights reserved.
5	5	* @licence See LICENCE.md for license details.
6	6	*/
7	7
8	8	namespace IvyForms\Repository\Entry;
9	9
10		-// phpcs:disable PSR1.Files.SideEffects
11		-if (!defined('ABSPATH')) {
12		- exit; // Exit if accessed directly
13		-}
14		-
15	10	use IvyForms\Common\Exceptions\InvalidArgumentException;
16	11	use IvyForms\Common\Exceptions\QueryExecutionException;
	12	+use IvyForms\Common\Helpers\EntryBatchUpdater;
	13	+use IvyForms\Common\Helpers\EntryQueryHelper;
17	14	use IvyForms\Entity\Entry\Entry;
18	15	use IvyForms\Factory\Entry\EntryFactory;
19	16	use IvyForms\Repository\AbstractRepository;
	17	+use IvyForms\Repository\VisibleFormIdsQueryFilter;
20	18	use IvyForms\Services\InstallActions\DB\Entry\EntriesTable;
21	19	use IvyForms\Services\InstallActions\DB\EntryField\EntryFieldsTable as EntryFieldsTable;
22	20	use IvyForms\Services\InstallActions\DB\Form\FormsTable;
23	21	use IvyForms\Services\Translations\BackendStrings;
		@@ -25,8 +23,12 @@
25	23	class EntryRepository extends AbstractRepository implements EntryRepositoryInterface
26	24	{
27	25	public const FACTORY = EntryFactory::class;
28	26
	27	+ // Column constants
	28	+ public const COLUMN_STATUS = 'status';
	29	+ public const COLUMN_STARRED = 'starred';
	30	+
29	31	/**
30	32	* Add entry to the database
31	33	*
32	34	* @param Entry $entity
		@@ -38,15 +40,18 @@
38	40	public function add($entity): int
39	41	{
40	42	$data = $entity->toArray(true);
41	43
	44	+ $dateCreated = !empty($data['dateCreated']) ? (string)$data['dateCreated'] : current_time('mysql');
	45	+ $dateEdited = !empty($data['dateEdited']) ? (string)$data['dateEdited'] : $dateCreated;
	46	+
42	47	$result = $this->wpdb->insert(
43	48	$this->table,
44	49	[
45	50	'formId' => $data['formId'],
46	51	'userId' => $data['userId'],
47		- 'dateCreated' => current_time('mysql'),
48		- 'dateEdited' => current_time('mysql'),
	52	+ 'dateCreated' => $dateCreated,
	53	+ 'dateEdited' => $dateEdited,
49	54	'status' => $data['status'],
50	55	'ipAddress' => $data['ipAddress'],
51	56	'userAgent' => $data['userAgent'],
52	57	'sourceURL' => $data['sourceURL'],
		@@ -166,12 +171,10 @@
166	171	$whereClauses = $this->buildWhereClauses($params, $queryParams);
167	172
168	173	// Always join entry fields and forms for search/filter/sort
169	174	$entriesTable = EntriesTable::getTableName();
170		- $entryFieldsTable = EntryFieldsTable::getTableName();
	175	+ $join = EntryQueryHelper::getEntryJoinClause();
171	176	$formsTable = FormsTable::getTableName();
172		- $join = " LEFT JOIN {$entryFieldsTable} eft ON e.id = eft.entryId ";
173		- $join .= " LEFT JOIN {$formsTable} f ON e.formId = f.id ";
174	177
175	178	// Sorting
176	179	$sortableColumns = $this->getSortableColumns();
177	180	$sortBy = $params['orderBy'] ?? 'id';
		@@ -185,18 +188,22 @@
185	188	}
186	189
187	190	// Pagination
188	191	$page = max(($params['page'] ?? 1), 1);
189		- $perPage = max(($params['perPage'] ?? 10), 1);
190		- $offset = ($page - 1) * $perPage;
191		- $queryParams[] = $perPage;
192		- $queryParams[] = $offset;
193		-
194		- // Main query
195		- $sql = "SELECT DISTINCT e.* FROM {$entriesTable} e $join
196		- WHERE {$whereClauses}
197		- ORDER BY {$sortColumn} {$order} LIMIT %d OFFSET %d";
198		- $results = $this->wpdb->get_results($this->wpdb->prepare($sql, $queryParams), ARRAY_A);
	192	+ $perPage = $params['perPage'] ?? 10;
	193	+ $queryBuild = EntryQueryHelper::buildEntrySelectQuery(
	194	+ $entriesTable,
	195	+ $join,
	196	+ $whereClauses,
	197	+ $sortColumn,
	198	+ $order,
	199	+ $queryParams,
	200	+ $perPage,
	201	+ $page
	202	+ );
	203	+ $sql = $queryBuild['sql'];
	204	+ $mainParams = $queryBuild['params'];
	205	+ $results = $this->wpdb->get_results($this->wpdb->prepare($sql, $mainParams), ARRAY_A);
199	206	if ($results === false) {
200	207	throw new QueryExecutionException(
201	208	BackendStrings::getExceptionStrings()['unable_search_entries'] . __CLASS__
202	209	);
		@@ -202,31 +209,27 @@
202	209	);
203	210	}
204	211
205	212	// Total count for pagination meta (exclude LIMIT/OFFSET)
206		- $countParams = $queryParams;
207		- array_pop($countParams); // offset
208		- array_pop($countParams); // perPage
209	213	$countSql = "SELECT COUNT(DISTINCT e.id) FROM {$entriesTable} e $join WHERE {$whereClauses}";
210		- $total = (int)$this->wpdb->get_var($this->wpdb->prepare($countSql, $countParams));
	214	+ $total = (int)$this->wpdb->get_var($this->wpdb->prepare($countSql, $queryParams));
211	215
212	216	// Fetch form names for all unique formIds in the results
213	217	$formIdToName = [];
214		- if (!empty($results)) {
215		- $formIds = array_unique(array_column($results, 'formId'));
216		- if (!empty($formIds)) {
217		- $placeholders = implode(',', array_fill(0, count($formIds), '%d'));
218		- $formSql = "SELECT id, name FROM {$formsTable} WHERE id IN ($placeholders)";
219		- $formRows = $this->wpdb->get_results($this->wpdb->prepare($formSql, $formIds), ARRAY_A);
220		- if ($formRows === false) {
221		- throw new QueryExecutionException(
222		- BackendStrings::getExceptionStrings()['unable_get_form_names'] . __CLASS__
223		- );
224		- }
225		- foreach ($formRows as $row) {
226		- $formIdToName[$row['id']] = $row['name'];
227		- }
	218	+ $formNamesQuery = EntryQueryHelper::getFormNamesByIdsQuery($results, $formsTable);
	219	+ if (!empty($formNamesQuery['sql'])) {
	220	+ $formRows = $this->wpdb->get_results(
	221	+ $this->wpdb->prepare($formNamesQuery['sql'], $formNamesQuery['params']),
	222	+ ARRAY_A
	223	+ );
	224	+ if ($formRows === null) {
	225	+ throw new QueryExecutionException(
	226	+ BackendStrings::getExceptionStrings()['unable_get_form_names'] . __CLASS__
	227	+ );
228	228	}
	229	+ foreach ($formRows as $row) {
	230	+ $formIdToName[$row['id']] = $row['name'];
	231	+ }
229	232	}
230	233
231	234	return [
232	235	'data' => $results,
		@@ -239,43 +242,34 @@
239	242	];
240	243	}
241	244
242	245	/**
243		- * Override addSearchClauses to support searching fieldValue and form name.
244		- * Extracted search conditions to a helper for complexity reduction.
245		- *
246		- * @param array<string, mixed>|null $params
247		- * @param array<string> $whereClauses
248		- * @param array<int|string, mixed> $queryParams
249		- *
250		- * @return void
	246	+ * Override addSearchClauses to use EntryQueryHelper for search WHERE clause construction.
251	247	*/
252	248	protected function addSearchClauses(?array $params, array &$whereClauses, array &$queryParams): void
253	249	{
254		- if (!empty($params['search'])) {
255		- $searchEscaped = '%' . $this->wpdb->esc_like($params['search']) . '%';
256		- $searchConditions = EntryQueryHelper::getEntrySearchConditions($params);
257		- $whereClauses[] = '(' . implode(' OR ', $searchConditions) . ')';
258		- $queryParams = array_merge($queryParams, array_fill(0, count($searchConditions), $searchEscaped));
	250	+ $searchResult = EntryQueryHelper::buildSearchWhereClause($params);
	251	+ if ($searchResult['where']) {
	252	+ $whereClauses[] = $searchResult['where'];
	253	+ $queryParams = array_merge($queryParams, $searchResult['params']);
259	254	}
260	255	}
261	256
262	257	/**
263		- * Override addFilterClauses to prefix columns with the correct alias (e.g., e.)
264		- * so that dynamic filtering works with JOINs and aliases.
	258	+ * Override addFilterClauses to use EntryQueryHelper for filter WHERE clause construction.
	259	+ * @param array<string, mixed>|null $params
265	260	*/
266	261	protected function addFilterClauses(?array $params, array &$whereClauses, array &$queryParams): void
267	262	{
268		- if (!empty($params['filters'])) {
269		- $filterableColumns = $this->getFilterableColumns();
270		- foreach ($params['filters'] as $key => $value) {
271		- if (in_array($key, $filterableColumns, true) && $value !== null && $value !== '') {
272		- // Prefix with e. for the entries table
273		- $whereClauses[] = "e.{$key} = %s";
274		- $queryParams[] = sanitize_text_field($value);
275		- }
276		- }
	263	+ $filterableColumns = $this->getFilterableColumns();
	264	+ $filterResult = EntryQueryHelper::buildFilterWhereClause($params, $filterableColumns);
	265	+ if (!empty($filterResult['where'])) {
	266	+ $whereClauses = array_merge($whereClauses, $filterResult['where']);
	267	+ // Ensure all values are strings
	268	+ $queryParams = array_merge($queryParams, array_map('strval', $filterResult['params']));
277	269	}
	270	+
	271	+ VisibleFormIdsQueryFilter::apply($params, $whereClauses, $queryParams, 'e.formId');
278	272	}
279	273
280	274	/**
281	275	* Get the columns that can be searched in the database (including virtual columns for search).
		@@ -298,9 +292,9 @@
298	292	* @return array<string>
299	293	*/
300	294	protected function getFilterableColumns(): array
301	295	{
302		- return ['starred', 'status', 'formId'];
	296	+ return ['starred', 'status', 'formId', 'userId'];
303	297	}
304	298
305	299	/**
306	300	* Get sortable columns
		@@ -428,8 +422,9 @@
428	422	*
429	423	* @param int[] $formIds
430	424	* @return array<int, int> formId => count
431	425	* @throws QueryExecutionException
	426	+ * @throws InvalidArgumentException
432	427	*/
433	428	public function getEntryCountByFormIds(array $formIds): array
434	429	{
435	430	$queryData = EntryQueryHelper::getEntryCountByFormIdsQuery($formIds);
		@@ -454,6 +449,31 @@
454	449	$counts[$id] = 0;
455	450	}
456	451	}
457	452	return $counts;
	453	+ }
	454	+
	455	+ /**
	456	+ * Resolve owning form IDs for the given entry IDs in a single query.
	457	+ *
	458	+ * @param int[] $entryIds
	459	+ * @return array<int, int> entryId => formId
	460	+ * @throws QueryExecutionException
	461	+ */
	462	+ public function getFormIdsByEntryIds(array $entryIds): array
	463	+ {
	464	+ return EntryQueryHelper::fetchFormIdsByEntryIds($this->wpdb, $entryIds);
	465	+ }
	466	+
	467	+ /**
	468	+ * Get allowed columns for bulk update operations
	469	+ *
	470	+ * @return string[]
	471	+ */
	472	+ protected function getAllowedBulkUpdateColumns(): array
	473	+ {
	474	+ return [
	475	+ self::COLUMN_STARRED,
	476	+ self::COLUMN_STATUS,
	477	+ ];
458	478	}
459	479	}