|null $params * @return array */ public static function getEntrySearchConditions(?array $params = null): array { $conditions = [ 'e.id LIKE %s', 'e.formId LIKE %s', 'f.name LIKE %s', ]; // Only include fieldValue if searchFieldValue is true (default true for BC) if (!isset($params['searchFieldValue']) || $params['searchFieldValue']) { $conditions[] = 'eft.fieldValue LIKE %s'; } return $conditions; } /** * Get entry fields SQL and params for the given entries. * * @param array> $entries * @return array{sql: string, params: array} * @throws InvalidArgumentException */ public static function getEntryFieldsQuery(array $entries): array { if (empty($entries)) { return ['sql' => '', 'params' => []]; } $entryIds = array_column($entries, 'id'); $placeholders = implode(',', array_fill(0, count($entryIds), '%d')); $entryFieldsTable = EntryFieldsTable::getTableName(); $sql = "SELECT * FROM {$entryFieldsTable} WHERE entryId IN ($placeholders)"; return ['sql' => $sql, 'params' => $entryIds]; } /** * Get the count of entries for a specific form (SQL and params). * * @param string $table * @param int $formId * @return array{sql: string, params: array} */ public static function getCountByFormIdQuery(string $table, int $formId): array { $sql = "SELECT COUNT(*) FROM {$table} WHERE formId = %d"; return ['sql' => $sql, 'params' => [$formId]]; } /** * Get the count of entries for the filter dropdown (SQL and params). * * @param string $table * @param array|null $params * @return array{sql: string, params: array} */ public static function getFilterCountQuery(string $table, ?array $params = null): array { $where = ''; $queryParams = []; if (!empty($params['filters']['formId'])) { $where = 'WHERE formId = %d'; $queryParams[] = (int)$params['filters']['formId']; } $sql = "SELECT SUM(status = 'read') as readTrueCount, SUM(status = 'unread') as readFalseCount, SUM(starred = 1) as starredTrueCount, SUM(starred = 0) as starredFalseCount FROM {$table} $where"; return ['sql' => $sql, 'params' => $queryParams]; } /** * Get the count of entries for multiple form IDs (SQL and params). * * @param int[] $formIds * @return array{sql: string, params: array, formIds: array} * @throws InvalidArgumentException */ public static function getEntryCountByFormIdsQuery(array $formIds): array { if (empty($formIds)) { return ['sql' => '', 'params' => [], 'formIds' => []]; } $formIds = array_map('intval', $formIds); $placeholders = implode(',', array_fill(0, count($formIds), '%d')); $tableName = EntriesTable::getTableName(); $sql = "SELECT formId, COUNT(*) as count FROM {$tableName} WHERE formId IN ($placeholders) GROUP BY formId"; return ['sql' => $sql, 'params' => $formIds, 'formIds' => $formIds]; } /** * Build the SELECT query and parameters for entries, with or without pagination. * @param string $entriesTable * @param string $join * @param string $whereClauses * @param string $sortColumn * @param string $order * @param array $queryParams * @param int|string $perPage * @param int $page * @return array{sql: string, params: array} */ public static function buildEntrySelectQuery( string $entriesTable, string $join, string $whereClauses, string $sortColumn, string $order, array $queryParams, $perPage, int $page ): array { $isAll = ($perPage === 'all' || $perPage === 0); if ($isAll) { $sql = "SELECT DISTINCT e.* FROM {$entriesTable} e $join WHERE {$whereClauses} ORDER BY {$sortColumn} {$order}"; return ['sql' => $sql, 'params' => $queryParams]; } $perPage = max((int)$perPage, 1); $offset = ($page - 1) * $perPage; $params = $queryParams; $params[] = $perPage; $params[] = $offset; $sql = "SELECT DISTINCT e.* FROM {$entriesTable} e $join WHERE {$whereClauses} ORDER BY {$sortColumn} {$order} LIMIT %d OFFSET %d"; return ['sql' => $sql, 'params' => $params]; } /** * Get the JOIN clause for entries, entry fields, and forms. * * @return string * @throws InvalidArgumentException */ public static function getEntryJoinClause(): string { $entryFieldsTable = EntryFieldsTable::getTableName(); $formsTable = FormsTable::getTableName(); $join = " LEFT JOIN {$entryFieldsTable} eft ON e.id = eft.entryId "; $join .= " LEFT JOIN {$formsTable} f ON e.formId = f.id "; return $join; } /** * Build search WHERE clause and query parameters for entry search. * @param array|null $params * @return array{where: string|null, params: array} */ public static function buildSearchWhereClause(?array $params): array { if (!empty($params['search'])) { global $wpdb; $searchEscaped = '%' . $wpdb->esc_like($params['search']) . '%'; $searchConditions = self::getEntrySearchConditions($params); $where = '(' . implode(' OR ', $searchConditions) . ')'; $queryParams = array_fill(0, count($searchConditions), $searchEscaped); return ['where' => $where, 'params' => $queryParams]; } return ['where' => null, 'params' => []]; } /** * Build filter WHERE clause and query parameters for entry filter. * @param array|null $params * @param array $filterableColumns * @return array{where: array, params: array} */ public static function buildFilterWhereClause(?array $params, array $filterableColumns): array { $whereClauses = []; $queryParams = []; if (!empty($params['filters'])) { foreach ($params['filters'] as $key => $value) { if (in_array($key, $filterableColumns, true) && $value !== null && $value !== '') { $whereClauses[] = "e.{$key} = %s"; $queryParams[] = sanitize_text_field($value); } } } return ['where' => $whereClauses, 'params' => $queryParams]; } }