| @@ -1,0 +1,323 @@ | ||
| 1 | +<?php | |
| 2 | + | |
| 3 | +namespace IvyForms\Common\Helpers; | |
| 4 | + | |
| 5 | +use IvyForms\Common\Exceptions\InvalidArgumentException; | |
| 6 | +use IvyForms\Common\Exceptions\QueryExecutionException; | |
| 7 | +use IvyForms\Services\InstallActions\DB\Entry\EntriesTable; | |
| 8 | +use IvyForms\Services\InstallActions\DB\EntryField\EntryFieldsTable; | |
| 9 | +use IvyForms\Services\InstallActions\DB\Form\FormsTable; | |
| 10 | +use IvyForms\Services\Translations\BackendStrings; | |
| 11 | + | |
| 12 | +class EntryQueryHelper | |
| 13 | +{ | |
| 14 | + /** | |
| 15 | + * Helper to return search conditions for entries (for complexity reduction) | |
| 16 | + * | |
| 17 | + * @param array<string, mixed>|null $params | |
| 18 | + * @return array<string> | |
| 19 | + */ | |
| 20 | + public static function getEntrySearchConditions(?array $params = null): array | |
| 21 | + { | |
| 22 | + $conditions = [ | |
| 23 | + 'e.id LIKE %s', | |
| 24 | + 'e.formId LIKE %s', | |
| 25 | + 'f.name LIKE %s', | |
| 26 | + ]; | |
| 27 | + // Only include fieldValue if searchFieldValue is true (default true for BC) | |
| 28 | + if (!isset($params['searchFieldValue']) || $params['searchFieldValue']) { | |
| 29 | + $conditions[] = 'eft.fieldValue LIKE %s'; | |
| 30 | + } | |
| 31 | + return $conditions; | |
| 32 | + } | |
| 33 | + | |
| 34 | + /** | |
| 35 | + * Get entry fields SQL and params for the given entries. | |
| 36 | + * | |
| 37 | + * @param array<int, array<string, mixed>> $entries | |
| 38 | + * @return array{sql: string, params: array<int, int>} | |
| 39 | + * @throws InvalidArgumentException | |
| 40 | + */ | |
| 41 | + public static function getEntryFieldsQuery(array $entries): array | |
| 42 | + { | |
| 43 | + if (empty($entries)) { | |
| 44 | + return ['sql' => '', 'params' => []]; | |
| 45 | + } | |
| 46 | + $entryIds = array_column($entries, 'id'); | |
| 47 | + $placeholders = implode(',', array_fill(0, count($entryIds), '%d')); | |
| 48 | + $entryFieldsTable = EntryFieldsTable::getTableName(); | |
| 49 | + $sql = "SELECT * FROM {$entryFieldsTable} WHERE entryId IN ($placeholders)"; | |
| 50 | + return ['sql' => $sql, 'params' => $entryIds]; | |
| 51 | + } | |
| 52 | + | |
| 53 | + /** | |
| 54 | + * Get the count of entries for a specific form (SQL and params). | |
| 55 | + * | |
| 56 | + * @param string $table | |
| 57 | + * @param int $formId | |
| 58 | + * @return array{sql: string, params: array<int, int>} | |
| 59 | + */ | |
| 60 | + public static function getCountByFormIdQuery(string $table, int $formId): array | |
| 61 | + { | |
| 62 | + $sql = "SELECT COUNT(*) FROM {$table} WHERE formId = %d"; | |
| 63 | + return ['sql' => $sql, 'params' => [$formId]]; | |
| 64 | + } | |
| 65 | + | |
| 66 | + /** | |
| 67 | + * Get the count of entries for the filter dropdown (SQL and params). | |
| 68 | + * | |
| 69 | + * @param string $table | |
| 70 | + * @param array<string, mixed>|null $params | |
| 71 | + * @return array{sql: string, params: array<int, int>} | |
| 72 | + */ | |
| 73 | + public static function getFilterCountQuery(string $table, ?array $params = null): array | |
| 74 | + { | |
| 75 | + $where = ''; | |
| 76 | + $queryParams = []; | |
| 77 | + if (!empty($params['filters']['formId'])) { | |
| 78 | + $where = 'WHERE formId = %d'; | |
| 79 | + $queryParams[] = (int)$params['filters']['formId']; | |
| 80 | + } | |
| 81 | + $sql = "SELECT | |
| 82 | + SUM(status = 'read') as readTrueCount, | |
| 83 | + SUM(status = 'unread') as readFalseCount, | |
| 84 | + SUM(starred = 1) as starredTrueCount, | |
| 85 | + SUM(starred = 0) as starredFalseCount | |
| 86 | + FROM {$table} $where"; | |
| 87 | + return ['sql' => $sql, 'params' => $queryParams]; | |
| 88 | + } | |
| 89 | + | |
| 90 | + /** | |
| 91 | + * Get the count of entries for multiple form IDs (SQL and params). | |
| 92 | + * | |
| 93 | + * @param int[] $formIds | |
| 94 | + * @return array{sql: string, params: array<int, int>, formIds: array<int, int>} | |
| 95 | + * @throws InvalidArgumentException | |
| 96 | + */ | |
| 97 | + public static function getEntryCountByFormIdsQuery(array $formIds): array | |
| 98 | + { | |
| 99 | + if (empty($formIds)) { | |
| 100 | + return ['sql' => '', 'params' => [], 'formIds' => []]; | |
| 101 | + } | |
| 102 | + $formIds = array_map('intval', $formIds); | |
| 103 | + $placeholders = implode(',', array_fill(0, count($formIds), '%d')); | |
| 104 | + $tableName = EntriesTable::getTableName(); | |
| 105 | + $sql = "SELECT formId, COUNT(*) as count FROM {$tableName} WHERE formId IN ($placeholders) GROUP BY formId"; | |
| 106 | + return ['sql' => $sql, 'params' => $formIds, 'formIds' => $formIds]; | |
| 107 | + } | |
| 108 | + | |
| 109 | + /** | |
| 110 | + * Build the SELECT query and parameters for entries, with or without pagination. | |
| 111 | + * @param string $entriesTable | |
| 112 | + * @param string $join | |
| 113 | + * @param string $whereClauses | |
| 114 | + * @param string $sortColumn | |
| 115 | + * @param string $order | |
| 116 | + * @param array<int|string, mixed> $queryParams | |
| 117 | + * @param int|string $perPage | |
| 118 | + * @param int $page | |
| 119 | + * @return array{sql: string, params: array<int|string, mixed>} | |
| 120 | + */ | |
| 121 | + public static function buildEntrySelectQuery( | |
| 122 | + string $entriesTable, | |
| 123 | + string $join, | |
| 124 | + string $whereClauses, | |
| 125 | + string $sortColumn, | |
| 126 | + string $order, | |
| 127 | + array $queryParams, | |
| 128 | + $perPage, | |
| 129 | + int $page | |
| 130 | + ): array { | |
| 131 | + $isAll = ($perPage === 'all' || $perPage === 0); | |
| 132 | + if ($isAll) { | |
| 133 | + $sql = "SELECT DISTINCT e.* FROM {$entriesTable} e $join | |
| 134 | + WHERE {$whereClauses} ORDER BY {$sortColumn} {$order}"; | |
| 135 | + return ['sql' => $sql, 'params' => $queryParams]; | |
| 136 | + } | |
| 137 | + $perPage = max((int)$perPage, 1); | |
| 138 | + $offset = ($page - 1) * $perPage; | |
| 139 | + $params = $queryParams; | |
| 140 | + $params[] = $perPage; | |
| 141 | + $params[] = $offset; | |
| 142 | + $sql = "SELECT DISTINCT e.* FROM {$entriesTable} e $join | |
| 143 | + WHERE {$whereClauses} ORDER BY {$sortColumn} {$order} LIMIT %d OFFSET %d"; | |
| 144 | + return ['sql' => $sql, 'params' => $params]; | |
| 145 | + } | |
| 146 | + | |
| 147 | + /** | |
| 148 | + * Get the JOIN clause for entries, entry fields, and forms. | |
| 149 | + * | |
| 150 | + * @return string | |
| 151 | + * @throws InvalidArgumentException | |
| 152 | + */ | |
| 153 | + public static function getEntryJoinClause(): string | |
| 154 | + { | |
| 155 | + $entryFieldsTable = EntryFieldsTable::getTableName(); | |
| 156 | + $formsTable = FormsTable::getTableName(); | |
| 157 | + $join = " LEFT JOIN {$entryFieldsTable} eft ON e.id = eft.entryId "; | |
| 158 | + $join .= " LEFT JOIN {$formsTable} f ON e.formId = f.id "; | |
| 159 | + return $join; | |
| 160 | + } | |
| 161 | + | |
| 162 | + /** | |
| 163 | + * Build search WHERE clause and query parameters for entry search. | |
| 164 | + * @param array<string, mixed>|null $params | |
| 165 | + * @return array{where: string|null, params: array<int, string>} | |
| 166 | + */ | |
| 167 | + public static function buildSearchWhereClause(?array $params): array | |
| 168 | + { | |
| 169 | + if (!empty($params['search'])) { | |
| 170 | + global $wpdb; | |
| 171 | + $searchEscaped = '%' . $wpdb->esc_like($params['search']) . '%'; | |
| 172 | + $searchConditions = self::getEntrySearchConditions($params); | |
| 173 | + $where = '(' . implode(' OR ', $searchConditions) . ')'; | |
| 174 | + $queryParams = array_fill(0, count($searchConditions), $searchEscaped); | |
| 175 | + return ['where' => $where, 'params' => $queryParams]; | |
| 176 | + } | |
| 177 | + return ['where' => null, 'params' => []]; | |
| 178 | + } | |
| 179 | + | |
| 180 | + /** | |
| 181 | + * Build filter WHERE clause and query parameters for entry filter. | |
| 182 | + * @param array<string, mixed>|null $params | |
| 183 | + * @param array<int, string> $filterableColumns | |
| 184 | + * @return array{where: array<int, string>, params: array<int, string>} | |
| 185 | + */ | |
| 186 | + public static function buildFilterWhereClause(?array $params, array $filterableColumns): array | |
| 187 | + { | |
| 188 | + $whereClauses = []; | |
| 189 | + $queryParams = []; | |
| 190 | + if (!empty($params['filters'])) { | |
| 191 | + foreach ($params['filters'] as $key => $value) { | |
| 192 | + if (in_array($key, $filterableColumns, true) && $value !== null && $value !== '') { | |
| 193 | + $whereClauses[] = "e.{$key} = %s"; | |
| 194 | + $queryParams[] = sanitize_text_field($value); | |
| 195 | + } | |
| 196 | + } | |
| 197 | + } | |
| 198 | + return ['where' => $whereClauses, 'params' => $queryParams]; | |
| 199 | + } | |
| 200 | + | |
| 201 | + /** | |
| 202 | + * Build form name lookup SQL and params for the given entry rows. | |
| 203 | + * | |
| 204 | + * @param array<int, array<string, mixed>> $results | |
| 205 | + * @return array{sql: string, params: array<int, int>} | |
| 206 | + */ | |
| 207 | + public static function getFormNamesByIdsQuery(array $results, string $formsTable): array | |
| 208 | + { | |
| 209 | + if ($results === []) { | |
| 210 | + return ['sql' => '', 'params' => []]; | |
| 211 | + } | |
| 212 | + | |
| 213 | + $formIds = array_values(array_unique(array_map('intval', array_column($results, 'formId')))); | |
| 214 | + $formIds = array_values(array_filter($formIds)); | |
| 215 | + if ($formIds === []) { | |
| 216 | + return ['sql' => '', 'params' => []]; | |
| 217 | + } | |
| 218 | + | |
| 219 | + $placeholders = implode(',', array_fill(0, count($formIds), '%d')); | |
| 220 | + $sql = "SELECT id, name FROM {$formsTable} WHERE id IN ($placeholders)"; | |
| 221 | + | |
| 222 | + return ['sql' => $sql, 'params' => $formIds]; | |
| 223 | + } | |
| 224 | + | |
| 225 | + /** | |
| 226 | + * @param list<int|string> $ids | |
| 227 | + * @return list<int> | |
| 228 | + */ | |
| 229 | + public static function normalizePositiveIds(array $ids): array | |
| 230 | + { | |
| 231 | + return array_values(array_unique(array_filter( | |
| 232 | + array_map('intval', $ids), | |
| 233 | + static function (int $id): bool { | |
| 234 | + return $id > 0; | |
| 235 | + } | |
| 236 | + ))); | |
| 237 | + } | |
| 238 | + | |
| 239 | + /** | |
| 240 | + * @param int[] $entryIds | |
| 241 | + * @return array{sql: string, params: list<int>} | |
| 242 | + */ | |
| 243 | + public static function getFormIdsByEntryIdsQuery(array $entryIds): array | |
| 244 | + { | |
| 245 | + $entryIds = self::normalizePositiveIds($entryIds); | |
| 246 | + $table = EntriesTable::getTableName(); | |
| 247 | + | |
| 248 | + if ($entryIds === []) { | |
| 249 | + return [ | |
| 250 | + 'sql' => "SELECT id, formId FROM {$table} WHERE 0 = 1", | |
| 251 | + 'params' => [], | |
| 252 | + ]; | |
| 253 | + } | |
| 254 | + | |
| 255 | + $placeholders = implode(',', array_fill(0, count($entryIds), '%d')); | |
| 256 | + | |
| 257 | + return [ | |
| 258 | + 'sql' => "SELECT id, formId FROM {$table} WHERE id IN ({$placeholders})", | |
| 259 | + 'params' => $entryIds, | |
| 260 | + ]; | |
| 261 | + } | |
| 262 | + | |
| 263 | + /** | |
| 264 | + * @param array<int, array<string, mixed>> $rows | |
| 265 | + * @return array<int, int> entryId => formId | |
| 266 | + */ | |
| 267 | + public static function mapEntryIdToFormIdRows(array $rows): array | |
| 268 | + { | |
| 269 | + $map = []; | |
| 270 | + foreach ($rows as $row) { | |
| 271 | + $formId = (int) ($row['formId'] ?? 0); | |
| 272 | + if ($formId <= 0) { | |
| 273 | + continue; | |
| 274 | + } | |
| 275 | + $map[(int) $row['id']] = $formId; | |
| 276 | + } | |
| 277 | + | |
| 278 | + return $map; | |
| 279 | + } | |
| 280 | + | |
| 281 | + /** | |
| 282 | + * Multi-row inserts generate IDs spaced by auto_increment_increment, | |
| 283 | + * which is > 1 on replication-style setups. | |
| 284 | + * | |
| 285 | + * @param \wpdb $wpdb | |
| 286 | + */ | |
| 287 | + public static function getAutoIncrementStep($wpdb): int | |
| 288 | + { | |
| 289 | + $step = (int) $wpdb->get_var('SELECT @@auto_increment_increment'); | |
| 290 | + | |
| 291 | + return $step > 0 ? $step : 1; | |
| 292 | + } | |
| 293 | + | |
| 294 | + /** | |
| 295 | + * @param \wpdb $wpdb | |
| 296 | + * @param int[] $entryIds | |
| 297 | + * @return array<int, int> entryId => formId | |
| 298 | + * @throws QueryExecutionException | |
| 299 | + */ | |
| 300 | + public static function fetchFormIdsByEntryIds($wpdb, array $entryIds): array | |
| 301 | + { | |
| 302 | + $entryIds = self::normalizePositiveIds($entryIds); | |
| 303 | + if ($entryIds === []) { | |
| 304 | + return []; | |
| 305 | + } | |
| 306 | + | |
| 307 | + $queryParts = self::getFormIdsByEntryIdsQuery($entryIds); | |
| 308 | + if ($queryParts['params'] === []) { | |
| 309 | + return []; | |
| 310 | + } | |
| 311 | + | |
| 312 | + $query = $wpdb->prepare($queryParts['sql'], ...$queryParts['params']); | |
| 313 | + $rows = $wpdb->get_results($query, ARRAY_A); | |
| 314 | + | |
| 315 | + if (!is_array($rows)) { | |
| 316 | + throw new QueryExecutionException( | |
| 317 | + BackendStrings::getExceptionStrings()['unable_find_by_id'] . __CLASS__ | |
| 318 | + ); | |
| 319 | + } | |
| 320 | + | |
| 321 | + return self::mapEntryIdToFormIdRows($rows); | |
| 322 | + } | |
| 323 | +} | |
