Query builder

A deliberately thin fluent builder — with two safety rules that will stop you at least once.

The builder composes SQL with bound placeholders so common cases read well. It is not an ORM, and it does not try to be: hand-written SQL through Connection::select() is the escape hatch and the better tool for joins, window functions and CTEs. For a typed, per-table view built on top of this builder — Note::find(1), $note->save() — see Models.

PHP
<?php
$articles = $database->query('articles')
    ->select('id', 'title', 'created_at')
    ->where('published', '=', true)
    ->where('author_id', '=', $authorId)
    ->orderBy('created_at', Direction::Descending)
    ->limit(20)
    ->get();

Reading

PHP
<?php
$query = $database->query('articles');

$query->get();                    // list<array<string, scalar|null>>
$query->first();                  // array|null — adds LIMIT 1
$query->value('title');           // a single column from the first row
$query->count();                  // int
$query->exists();                 // bool

Conditions

PHP
<?php
$database->query('articles')
    ->where('title', 'LIKE', '%orbit%')
    ->where('views', '>=', 100)
    ->whereNotNull('published_at')
    ->whereIn('status', ['live', 'featured'])
    ->get();

Conditions combine with AND. Permitted operators: =, !=, <>, <, <=, >, >=, LIKE, NOT LIKE.

An operator is concatenated into the SQL, so it comes from a whitelist rather than being passed through:

Output
InvalidArgumentException: Operator "IS" is not permitted.
  Use one of: =, !=, <>, <, <=, >, >=, LIKE, NOT LIKE.

Nulls

PHP
<?php
$database->query('articles')->where('published_at', '=', null);
// InvalidArgumentException: Comparing to null never matches.
//   Use whereNull() or whereNotNull().

$database->query('articles')->whereNull('published_at')->get();
$database->query('articles')->whereNotNull('published_at')->get();

= NULL is never true in SQL. Callers almost always mean IS NULL, so the builder says so instead of quietly returning nothing.

Empty sets

PHP
<?php
$database->query('articles')->whereIn('id', [])->get();   // []

An empty whereIn becomes a condition that matches nothing. IN () is a syntax error, and dropping the condition would turn “none of these” into “all rows” — the more dangerous of the two readings.

Ordering and paging

PHP
<?php
use PhpOrbit\Database\Direction;

$database->query('articles')
    ->orderBy('created_at', Direction::Descending)
    ->orderBy('id')                          // Ascending by default
    ->limit(20)
    ->offset(40)
    ->get();

Direction is an enum, so "ASC"/"DESC" is never a caller-supplied string concatenated into SQL.

Writing

PHP
<?php
// Returns the generated id
$id = $database->query('articles')->insert([
    'title' => $title,
    'body' => $body,
    'created_at' => gmdate('c'),
]);

// Returns rows changed
$changed = $database->query('articles')
    ->where('id', '=', $id)
    ->update(['title' => $newTitle]);

$removed = $database->query('articles')
    ->where('id', '=', $id)
    ->delete();

The two rules that will stop you

1. An unqualified UPDATE or DELETE throws

PHP
<?php
$database->query('articles')->delete();
// UnsafeQuery: This DELETE has no conditions and would affect every row of
//   "articles". Add a where(), or call affectingEveryRow() if that is genuinely
//   the intent.

// When you do mean it, say so:
$database->query('sessions')->affectingEveryRow()->delete();

A forgotten where() is one of the most expensive mistakes available in a few keystrokes, and it looks identical to a deliberate whole-table change unless the caller states which they meant.

2. Identifiers are whitelisted

PHP
<?php
$database->query('articles')->orderBy($request->uri->queryParam('sort') ?? 'id');
// InvalidArgumentException: Identifier "title; DROP TABLE users" is not a plain
//   table or column name. Identifiers cannot be bound as parameters, so only
//   letters, digits and underscores are accepted.

No driver can bind an identifier, so it is always concatenated. A whitelist has one answer; escaping invites the question “escaped well enough?”

Sorting by user input is still fine — map it yourself:

PHP
<?php
$sortable = ['title', 'created_at', 'views'];
$requested = $request->uri->queryParam('sort') ?? 'created_at';
$column = in_array($requested, $sortable, true) ? $requested : 'created_at';

$database->query('articles')->orderBy($column, Direction::Descending)->get();

Immutability

Every method returns a new builder, so a base query is safe to reuse:

PHP
<?php
$published = $database->query('articles')->where('published', '=', true);

$recent = $published->orderBy('created_at', Direction::Descending)->limit(5)->get();
$total = $published->count();     // unaffected by the ordering above

Inspecting the SQL

PHP
<?php
$query = $database->query('articles')
    ->select('id', 'title')
    ->where('published', '=', true)
    ->limit(10);

$query->toSql();
// SELECT "id", "title" FROM "articles" WHERE "published" = :p0 LIMIT 10

$query->bindings();
// ['p0' => true]

Useful in tests, and for confirming what a chain actually produced.

When to drop to SQL

There are no joins or aggregates beyond count(). That is deliberate — a half-built join API is worse than none, because it looks like it should handle the next case and does not.

PHP
<?php
$rows = $database->select(
    'SELECT a.id, a.title, u.name AS author, COUNT(c.id) AS comments
       FROM articles a
       JOIN users u ON u.id = a.author_id
       LEFT JOIN comments c ON c.article_id = a.id
      WHERE a.published = :published
      GROUP BY a.id
      ORDER BY comments DESC
      LIMIT 10',
    ['published' => true],
);

Still fully parameterised, still typed rows. Reach for it without hesitation.