README

Essentio\Query gives you fluent, precise control over SQL without pulling in layers of abstraction. There’s no “Model”, no “Repository”, and no assumptions. You write the query. It builds and executes it.

No fluff. No black boxes. Just SQL, made easier.

Basic Usage

use Essentio\Database\Query;

$query = new Query($pdo);

API Reference (with examples)

select(...$columns): static

Set the columns to select.

$query->select('id', 'name');

from(string|Closure $table): static

Set the FROM clause, or provide a subquery via closure.

$query->from('users');

// Subquery example:
$query->from(fn($q) => $q->select('id')->from('users')); // defaults to AS t

subquery(Closure $builder, ?string $alias = null): static

Explicit version of from() with closure for subqueries.

$query->subquery(fn($q) => $q->select('id')->from('users'), 'sub');

join(string $table, ?string $first = null, ?string $op = null, ?string $second = null, string $type = ''): static

Add a JOIN clause. You can be explicit, or let it fill in the blanks using conventions.

$query->join('profiles', 'users.id', '=', 'profiles.user_id', 'LEFT');

union(Closure $builder, string $type = ''): static

UNION another query into this one.

$query
    ->select('id')->from('users')
    ->union(fn($q) => $q->select('id')->from('archived_users'));

where(string|Closure $column, ?string $op = null, mixed $value = null, string $type = 'AND'): static

Add a WHERE condition. Nest with closures.

// Basic equality
$query->where('status', '=', 'active'); // WHERE status = ? ; bound: ['active']

// Operator inference (value-only)
$query->where('status', 'active'); // WHERE status = ? ; bound: ['active']

// NULL-safe condition
$query->where('deleted_at', 'IS NULL'); // WHERE deleted_at IS NULL

// Array value → `IN (...)`
$query->where('id', 'IN', [1, 2, 3]); // WHERE id IN (?, ?, ?) ; bound: [1, 2, 3]

// Closure: nested conditions
$query->where(fn(Query $q) =>
    $q->where('type', '=', 'admin')
      ->orWhere('verified', '=', true)
); // WHERE (type = ? OR verified = ?) ; bound ['admin', true]

// Closure as subquery
$query->where('user_id', 'IN', fn($q) =>
    $q->select('id')->from('users')->where('active', true)
); // WHERE user_id IN (SELECT id FROM users WHERE active = ?) ; bound [true]

orWhere(...)

Same as where() but with an OR.

$query->orWhere('deleted_at', 'IS', 'NULL');

group(...$columns): static

Add a GROUP BY clause.

$query->group('role', 'status');

having(string|Closure $column, ?string $op = null, mixed $value = null, string $type = 'AND'): static

Add a HAVING condition. Works just like where(), but applies after GROUP BY.

$query->group('type')->having('COUNT(id)', '>', 10);

orHaving(...)

Same as having() but with OR.

$query->orHaving('SUM(score)', '>', 100);

order(string $column, string $direction = 'ASC'): static

Add an ORDER BY clause.

$query->order('created_at', 'DESC');

limit(int $limit, ?int $offset = null): static

Apply a LIMIT (and optional OFFSET).

$query->limit(10);              // First 10
$query->limit(10, 20);          // 10 rows, starting from 20

get(): array

Execute the built query and return all rows as an array.

$users = $query->select('id', 'email')->from('users')->get();

first(): ?array

Like get() but returns just the first row (or null).

$user = $query->select('*')->from('users')->where('id', '=', 1)->first();

morph(callable $fn, bool $spread = false): iterable

Transforms each row using a callable. Can spread array values as arguments.

$query->select('name', 'email')->from('users')->morph(fn($row) => strtoupper($row['name']));

$query->morph(fn($name, $email) => "$name <$email>", true);

insert(array $data): ?int

Insert a row and return the last insert ID (if available).

$id = $query->from('users')->insert([
    'name' => 'Jane',
    'email' => 'jane@example.com'
]);

update(array $data): int

Update rows matching the query. Returns affected count.

$affected = $query->from('users')->where('id', '=', 5)->update(['name' => 'New Name']);

delete(): int

Delete rows matching the query. Returns affected count.

$deleted = $query->from('users')->where('banned', '=', true)->delete();

Final Notes

• Every method mutates the query. You can chain as much as you like.
• If something isn’t working, read the compiled SQL.
• Assertions exist for your own safety. Respect them.
• It doesn’t catch your mistakes — and it doesn’t try to.

Why This Exists

I needed a query builder that gave me control and stayed out of the way. This one does. That’s it. It’s stable, fast, and transparent.

If you like it — use it. If not — no hard feelings.