125 lines
4.0 KiB
PHP
125 lines
4.0 KiB
PHP
<?php
|
|
|
|
namespace App\Libraries;
|
|
|
|
class QueryBuilder
|
|
{
|
|
/**
|
|
* @param array<string, mixed> $visualConfig
|
|
* @return array{sql:string, bindings:array<int, mixed>}
|
|
*/
|
|
public function toSQL(array $visualConfig): array
|
|
{
|
|
$table = trim((string) ($visualConfig['table'] ?? ''));
|
|
if ($table === '') {
|
|
throw new \InvalidArgumentException('Table is required.');
|
|
}
|
|
|
|
$columns = $visualConfig['columns'] ?? ['*'];
|
|
if (! is_array($columns) || $columns === []) {
|
|
$columns = ['*'];
|
|
}
|
|
|
|
$safeColumns = [];
|
|
foreach ($columns as $column) {
|
|
$safeColumns[] = $this->safeIdentifier((string) $column);
|
|
}
|
|
$selectClause = implode(', ', $safeColumns);
|
|
|
|
$sql = 'SELECT ' . $selectClause . ' FROM ' . $this->safeIdentifier($table);
|
|
$bindings = [];
|
|
|
|
$filters = $visualConfig['filters'] ?? [];
|
|
if (is_array($filters) && $filters !== []) {
|
|
$whereParts = [];
|
|
foreach ($filters as $filter) {
|
|
if (! is_array($filter)) {
|
|
continue;
|
|
}
|
|
|
|
$field = $this->safeIdentifier((string) ($filter['field'] ?? ''));
|
|
$operator = strtoupper(trim((string) ($filter['operator'] ?? '=')));
|
|
$value = $filter['value'] ?? null;
|
|
|
|
if ($field === '' || $operator === '') {
|
|
continue;
|
|
}
|
|
|
|
if (in_array($operator, ['IS NULL', 'IS NOT NULL'], true)) {
|
|
$whereParts[] = $field . ' ' . $operator;
|
|
continue;
|
|
}
|
|
|
|
if (! in_array($operator, ['=', '!=', '>', '<', '>=', '<=', 'LIKE'], true)) {
|
|
throw new \InvalidArgumentException('Unsupported filter operator.');
|
|
}
|
|
|
|
$whereParts[] = $field . ' ' . $operator . ' ?';
|
|
$bindings[] = $value;
|
|
}
|
|
|
|
if ($whereParts !== []) {
|
|
$sql .= ' WHERE ' . implode(' AND ', $whereParts);
|
|
}
|
|
}
|
|
|
|
$groupBy = $visualConfig['group_by'] ?? [];
|
|
if (is_array($groupBy) && $groupBy !== []) {
|
|
$groupColumns = [];
|
|
foreach ($groupBy as $groupField) {
|
|
$groupColumns[] = $this->safeIdentifier((string) $groupField);
|
|
}
|
|
$groupColumns = array_filter($groupColumns);
|
|
if ($groupColumns !== []) {
|
|
$sql .= ' GROUP BY ' . implode(', ', $groupColumns);
|
|
}
|
|
}
|
|
|
|
$orderBy = $visualConfig['order_by'] ?? [];
|
|
$orderClauses = [];
|
|
if (is_array($orderBy) && array_key_exists('column', $orderBy)) {
|
|
$orderBy = [$orderBy];
|
|
}
|
|
if (is_array($orderBy)) {
|
|
foreach ($orderBy as $clause) {
|
|
if (! is_array($clause)) {
|
|
continue;
|
|
}
|
|
$col = trim((string) ($clause['column'] ?? ''));
|
|
if ($col === '') {
|
|
continue;
|
|
}
|
|
$direction = strtoupper((string) ($clause['direction'] ?? 'ASC'));
|
|
$direction = $direction === 'DESC' ? 'DESC' : 'ASC';
|
|
$orderClauses[] = $this->safeIdentifier($col) . ' ' . $direction;
|
|
}
|
|
}
|
|
if ($orderClauses !== []) {
|
|
$sql .= ' ORDER BY ' . implode(', ', $orderClauses);
|
|
}
|
|
|
|
$limitRaw = $visualConfig['limit'] ?? 500;
|
|
$limit = is_int($limitRaw) ? $limitRaw : (int) (string) $limitRaw;
|
|
if ($limit <= 0) {
|
|
$limit = 500;
|
|
}
|
|
$sql .= ' LIMIT ' . $limit;
|
|
|
|
return ['sql' => $sql, 'bindings' => $bindings];
|
|
}
|
|
|
|
private function safeIdentifier(string $value): string
|
|
{
|
|
$value = trim($value);
|
|
if ($value === '*') {
|
|
return $value;
|
|
}
|
|
|
|
if (! preg_match('/^[a-zA-Z0-9_.]+$/', $value)) {
|
|
throw new \InvalidArgumentException('Invalid identifier in visual query.');
|
|
}
|
|
|
|
return $value;
|
|
}
|
|
}
|