Queries and transactions
Filter records, paginate results, perform bulk writes, and define transaction boundaries.
On this page
Choose rows or models
Use Connection::table() for rows and Note::query() for hydrated models. Both use the query builder API, but models add attribute casts, scopes, and relationship loading.
use Anvyr\Loom\Database\Connection;
use Notes\Models\Note;
$db = app(Connection::class);
$rows = $db->table('notes')
->select(['id', 'title'])
->where('archived', '=', 0)
->whereNull('deleted_at')
->orderBy('id', 'desc')
->limit(10)
->get();
$models = Note::query()->active()->orderBy('id', 'desc')->get();
where() takes three arguments: column, operator, value. The two-argument shorthand familiar from some ORMs is not the signature here. Row queries do not automatically apply a model's soft-delete scope; add the condition yourself when appropriate.
Paginate a stable ordering
$page = Note::query()->orderBy('id')->paginate(10, 1);
$items = $page['data'];
$total = $page['total'];
$lastPage = $page['last_page'];
Pagination returns an array with data, total, per_page, current_page, and last_page. Model pagination returns a model collection in data; row pagination returns rows. Pass page numbers explicitly and use a deterministic order.
Join and project columns
$authors = $db->table('notes')
->leftJoin('authors', 'notes.author_id', '=', 'authors.id')
->select(['notes.id', 'notes.title', 'authors.name as author_name'])
->whereNull('notes.deleted_at')
->orderBy('notes.id')
->get();
Values are bound as parameters. Table names, columns, and raw SQL fragments remain part of the query structure: choose these in application code instead of accepting arbitrary user input. whereRaw() and selectRaw() accept separate bindings when an expression is necessary.
Write in bulk
$db->table('tags')->upsert(
['name' => 'framework'],
'name',
['name'],
);
$affected = $db->table('notes')->where('title', '=', 'Old title')->update([
'title' => 'New title',
]);
upsert() accepts one associative row, not a list of rows. The conflict key must have a matching database primary or unique constraint. The example uses the unique tags.name schema described in the relationship guide. Builder mutations bypass per-model events, casts, and automatic model timestamps. Set timestamp values explicitly when needed.
Use a transaction for a unit of work
$id = $db->transaction(static function (Connection $connection) {
return $connection->table('notes')->insertGetId([
'title' => 'Committed together',
'body' => '',
'archived' => 0,
]);
});
The callback receives the connection; its return value is returned after commit. The current implementation rolls back a caught Exception. It does not catch all Throwable types, so do not assume Error or TypeError receives the same automatic cleanup. It also does not implement nested transaction savepoints. Keep transaction ownership at one layer and handle wider failure cleanup explicitly if your application requires it.
Start a fresh builder for another query rather than reusing a builder after find(), count(), exists(), or pagination: terminal operations can change its state.
Verified against QueryBuilder, ModelBuilder, and Connection. The executable documentation examples use SQLite; no claim of a live MySQL or PostgreSQL integration test is implied.