ivyforms / backend / src / Repository / Entry / EntryQueryHelper.php

EntryQueryHelper.php in The Innovative Form Builder – IvyForms 0.4, at backend/src/Repository/Entry/EntryQueryHelper.php

110 lines 3.9 KB

Raw Download Zip

1	<?php
2
3	namespace IvyForms\Repository\Entry;
4
5	use IvyForms\Common\Exceptions\QueryExecutionException;
6	use IvyForms\Common\Exceptions\InvalidArgumentException;
7	use IvyForms\Services\Translations\BackendStrings;
8	use IvyForms\Services\InstallActions\DB\EntryField\EntryFieldsTable;
9	use IvyForms\Services\InstallActions\DB\Entry\EntriesTable;
10	use IvyForms\Services\InstallActions\DB\Form\FormsTable;
11	use wpdb;
12
13	class EntryQueryHelper
14	{
15	/**
16	* Helper to return search conditions for entries (for complexity reduction)
17	*
18	* @param array<string, mixed>\|null $params
19	* @return array<string>
20	*/
21	public static function getEntrySearchConditions(?array $params = null): array
22	{
23	$conditions = [
24	'e.id LIKE %s',
25	'e.formId LIKE %s',
26	'f.name LIKE %s',
27	];
28	// Only include fieldValue if searchFieldValue is true (default true for BC)
29	if (!isset($params['searchFieldValue']) \|\| $params['searchFieldValue']) {
30	$conditions[] = 'eft.fieldValue LIKE %s';
31	}
32	return $conditions;
33	}
34
35	/**
36	* Get entry fields SQL and params for the given entries.
37	*
38	* @param array<int, array<string, mixed>> $entries
39	* @return array{sql: string, params: array<int, int>}
40	* @throws InvalidArgumentException
41	*/
42	public static function getEntryFieldsQuery(array $entries): array
43	{
44	if (empty($entries)) {
45	return ['sql' => '', 'params' => []];
46	}
47	$entryIds = array_column($entries, 'id');
48	$placeholders = implode(',', array_fill(0, count($entryIds), '%d'));
49	$entryFieldsTable = EntryFieldsTable::getTableName();
50	$sql = "SELECT * FROM {$entryFieldsTable} WHERE entryId IN ($placeholders)";
51	return ['sql' => $sql, 'params' => $entryIds];
52	}
53
54	/**
55	* Get the count of entries for a specific form (SQL and params).
56	*
57	* @param string $table
58	* @param int $formId
59	* @return array{sql: string, params: array<int, int>}
60	*/
61	public static function getCountByFormIdQuery(string $table, int $formId): array
62	{
63	$sql = "SELECT COUNT(*) FROM {$table} WHERE formId = %d";
64	return ['sql' => $sql, 'params' => [$formId]];
65	}
66
67	/**
68	* Get the count of entries for the filter dropdown (SQL and params).
69	*
70	* @param string $table
71	* @param array<string, mixed>\|null $params
72	* @return array{sql: string, params: array<int, int>}
73	*/
74	public static function getFilterCountQuery(string $table, ?array $params = null): array
75	{
76	$where = '';
77	$queryParams = [];
78	if (!empty($params['filters']['formId'])) {
79	$where = 'WHERE formId = %d';
80	$queryParams[] = (int)$params['filters']['formId'];
81	}
82	$sql = "SELECT
83	SUM(status = 'read') as readTrueCount,
84	SUM(status = 'unread') as readFalseCount,
85	SUM(starred = 1) as starredTrueCount,
86	SUM(starred = 0) as starredFalseCount
87	FROM {$table} $where";
88	return ['sql' => $sql, 'params' => $queryParams];
89	}
90
91	/**
92	* Get the count of entries for multiple form IDs (SQL and params).
93	*
94	* @param int[] $formIds
95	* @return array{sql: string, params: array<int, int>, formIds: array<int, int>}
96	* @throws InvalidArgumentException
97	*/
98	public static function getEntryCountByFormIdsQuery(array $formIds): array
99	{
100	if (empty($formIds)) {
101	return ['sql' => '', 'params' => [], 'formIds' => []];
102	}
103	$formIds = array_map('intval', $formIds);
104	$placeholders = implode(',', array_fill(0, count($formIds), '%d'));
105	$tableName = EntriesTable::getTableName();
106	$sql = "SELECT formId, COUNT(*) as count FROM {$tableName} WHERE formId IN ($placeholders) GROUP BY formId";
107	return ['sql' => $sql, 'params' => $formIds, 'formIds' => $formIds];
108	}
109	}
110