Database · Repositories

Repositories

A repository is a class bound to a table. The query is assembled by calling methods, values travel as bound parameters, and the result comes back as objects of your entity. Below: how to declare one, how to assemble a query, and every method taken apart — what it accepts, what it returns, and what it does when nothing was found.

Package flytachi/winter-ppaAssembly RepositoryCoreReading RepositoryViewTraitWriting RepositoryCrudTrait

What a repository is

The place where the queries of one table live. Without it SQL spreads through controllers as strings: the same selection is rewritten wherever it is needed, parameters are bound by hand, and a renamed column is discovered at runtime.

php

// without a repository
$stmt = $pdo->prepare('SELECT * FROM users WHERE status = :s ORDER BY id DESC LIMIT 20');
$stmt->execute(['s' => 'active']);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);   // untyped arrays

// with one
$users = UserRepository::instance('u')
  ->where(Qb::eq('u.status', 'active'))
  ->orderBy('u.id DESC')
  ->limit(20)
  ->findAll();                              // User[]

Three things this buys beyond brevity: values always travel as bound parameters rather than concatenation; the result is hydrated into an entity, so the editor knows the fields; and a subquery, a join or a CTE takes another repository rather than a string — which brings its own bound parameters along.

Declaring one

main/Repositories/UserRepository.php

<?php

namespace Main\Repositories;

use Flytachi\Winter\Ppa\Stereotype\Repository;
use Main\Configurations\MainDbConfig;
use Main\Entities\User;

/** @extends Repository<User> */
class UserRepository extends Repository
{
  public static string $table         = 'users';
  protected string $entityClassName   = User::class;
  protected string $dbConfigClassName = MainDbConfig::class;
}

Property	Required	What it sets
`$table`	yes	the table name; `public static`, because it is read without an instance
`$dbConfigClassName`	yes	which database to talk to
`$entityClassName`	in practice	what rows are hydrated into
`$schema`	no	the schema, when the table is not in the default one

`@extends` is not decoration

The line /** @extends Repository<User> */ pins the template parameter to your entity. With it findById() returns ?User and the editor knows the fields. Without it — object, and completion goes quiet; the code runs the same either way.

Stereotypes

Class	What it gives	When to take it
`Repository`	assembly + reading + writing	the ordinary case
`RepositoryView`	assembly + reading	a database view, a report, a read-only table
`RepositoryCrud`	assembly + writing	a log, a queue — written to, not read
`CteRepo`	assembly	a fragment that only ever exists as a CTE or a subquery

The choice is not about style but about what cannot be called by mistake: RepositoryView has no delete(), and that shows in completion.

Getting an instance

`instance()`

php

public static function instance(?string $as = null): static

Argument	Type	Default	What it does
`$as`	`?string`	`null`	the table alias in the query

Returns a new repository instance with clean query state.

php

UserRepository::instance();        // SELECT id, email FROM users
UserRepository::instance('u');     // SELECT u.id, u.email FROM users u

An alias is needed wherever a query has more than one table: without it the columns of a join cannot be told apart. Making a habit of it saves rewriting at the first join.

Every call is a new query

instance() returns a new object, and conditions accumulate on the object. Assembling in one chain is not a matter of style: two instance() calls are two independent queries, and “adding to” an instance you obtained earlier appends to whatever it already carries.

`as()`

php

public function as(string $alias): static

Sets the alias on an instance you already have — the same as the argument of instance(), but mid-assembly.

`forBy()`

php

public function forBy(string $context): static

Appends a FOR … clause — a row lock held for the transaction.

php

UserRepository::instance('u')->where(Qb::eq('u.id', $id))->forBy('UPDATE')->find();
// SELECT u.id, u.email FROM users u WHERE u.id = :iqb0 FOR UPDATE

For when a row is read in order to be changed immediately, and nobody else may change it in between. It works inside a transaction and not on every driver — SQLite has no such syntax.

Method reference

Assembling a query

Every method in this group returns static, so calls chain. None of them goes to the database: the query leaves only when something is read or written.

`select()`

php

public function select(string $option): static

Argument	Type	What it does
`$option`	`string`	the column list, as a string, as in SQL

By default the entity’s columns are selected. A custom list is for aggregates, and for not dragging along what is not needed.

php

UserRepository::instance('u')
  ->select('u.id, COUNT(*) AS n')
  ->groupBy('u.id')
  ->having('COUNT(*) > 3');
// SELECT u.id, COUNT(*) AS n FROM users u GROUP BY u.id HAVING COUNT(*) > 3

A custom `select()` changes the result type

An arbitrary column list may not match the entity’s shape, so hydration falls back to stdClass. That shows in getEntityClassName(), and it means the result carries $row->n rather than typed fields.

`from()`

php

public function from(RepositoryInterface|string $repository): static

Replaces the source: a subquery, or another name, instead of the table.

php

UserRepository::instance('u')->from(OrderRepository::instance('o')->select('o.user_id'));
// SELECT u.id, u.email FROM (SELECT o.user_id FROM orders o) u

A repository brings its bound parameters along; a string is simply substituted.

`where()`, `andWhere()`, `orWhere()`, `xorWhere()`

php

public function where(?Qb $qb): static
public function andWhere(Qb $qb): static
public function orWhere(Qb $qb): static
public function xorWhere(Qb $qb): static

Method	What it does
`where()`	sets the condition
`andWhere()`	appends with `AND`
`orWhere()`	appends with `OR`
`xorWhere()`	appends with `XOR`

Conditions are built by Qb — values inside it become bound parameters rather than part of the query text.

php

UserRepository::instance('u')
  ->where(Qb::eq('u.id', 1))
  ->andWhere(Qb::gt('u.age', 0))
  ->orWhere(Qb::eq('u.id', 2));
// WHERE u.id = :iqb3 AND u.age > :iqb4 OR u.id = :iqb5

null in where() does nothing — it is not a reset. To clear the query there is cleanCache().

`join()`, `joinInner()`, `joinLeft()`, `joinRight()`, `joinCross()`

php

public function join(RepositoryInterface|string $repository, Qb|string $on): static

Argument	Type	What it does
`$repository`	`RepositoryInterface\|string`	what to join: a repository, or `'orders o'` as a string
`$on`	`Qb\|string`	the join condition

joinCross() takes only the first argument — a cartesian product has no condition.

php

UserRepository::instance('u')->joinLeft(OrderRepository::instance('o'), 'o.user_id = u.id');
// SELECT u.id, u.email FROM users u LEFT JOIN orders o ON(o.user_id = u.id)

Joining a repository rather than a string pays for the same reason as in from(): it carries its own table name, schema and bound parameters. The string form is there for when the table has no repository at all.

`with()`, `withRecursive()`

php

public function with(string $name, RepositoryInterface $repository, ?string $modifier = null): static
public function withRecursive(string $name, RepositoryInterface $repository): static

Argument	Type	What it does
`$name`	`string`	the CTE’s name, used to refer to it later
`$repository`	`RepositoryInterface`	the query that becomes the CTE’s body
`$modifier`	`?string`	a dialect hint, such as `MATERIALIZED`

php

UserRepository::instance('u')->with('recent', OrderRepository::instance('o')->where(Qb::gt('o.total', 100)));
// WITH recent AS (SELECT o.id, o.user_id, o.total FROM orders o WHERE o.total > :iqb2)
// SELECT u.id, u.email FROM users u

withRecursive() emits WITH RECURSIVE — for walking trees and graphs.

`union()`, `unionAll()`

php

public function union(RepositoryInterface $repository): static
public function unionAll(RepositoryInterface $repository): static

Combines result sets. union() removes duplicates, unionAll() does not and is therefore cheaper.

php

UserRepository::instance('u')->union(UserRepository::instance('u2')->where(Qb::eq('u2.id', 2)));
// SELECT u.id, u.email FROM users u UNION SELECT u2.id, u2.email FROM users u2 WHERE u2.id = :iqb1

The column lists of both halves must match — that is SQL’s requirement, not the layer’s.

`groupBy()`, `having()`, `orderBy()`

php

public function groupBy(string $context): static
public function having(string $context): static
public function orderBy(string $context): static

They take a SQL fragment as a string, verbatim.

php

->groupBy('u.id')->having('COUNT(*) > 3')->orderBy('u.created_at DESC, u.id DESC')

These are fragments, not values

The string is substituted into the query as it is. Never build one from user input: ordering is code, and escaping does not make code safe. Map the input to a fixed set:

$sort = match ($request->query('sort')) {
    'newest' => 'u.created_at DESC',
    'email'  => 'u.email ASC',
    default  => 'u.id DESC',
};

`limit()`

php

public function limit(int $limit, int $offset = 0): static

Argument	Type	Default	What it does
`$limit`	`int`	—	how many rows to return
`$offset`	`int`	`0`	how many to skip

php

UserRepository::instance('u')->limit(5, 10);
// … LIMIT 5 OFFSET 10

For pages, pagination is easier: it works out the offset itself and returns the meta.

Reading

Every method in this group runs the query. The ?string $entityClassName in all of them replaces the hydration class without touching the column set.

`find()`

php

public function find(?string $entityClassName = null): ?object

The first row of the query.

Returns an entity object, or null when there are no rows. It does not throw — the absence of a result is an answer, not a failure.

php

$user = UserRepository::instance('u')->where(Qb::eq('u.email', $email))->find();

if ($user === null) {
  return ResponseEntity::notFound();
}

`findAll()`

php

public function findAll(?string $entityClassName = null): array

Returns an array of entities; empty when nothing matched.

php

$users = UserRepository::instance('u')->where(Qb::eq('u.status', 'active'))->findAll();

There is no size limit here — the method returns as many rows as it found. For large tables add a limit(), or take pagination.

`findById()`, `findBy()`, `findAllBy()`

php

public function findById(string|int $id, ?string $entityClassName = null): ?object
public function findBy(Qb $qb, ?string $entityClassName = null): ?object
public function findAllBy(?Qb $qb = null, ?string $entityClassName = null): array

Shorthands for the common case — the condition goes straight into the call, without a separate where().

Method	What it does	When nothing matched
`findById($id)`	looks up by primary key	`null`
`findBy($qb)`	the first row matching a condition	`null`
`findAllBy($qb)`	all rows matching; `null` means the whole table	`[]`

php

UserRepository::instance()->findById(1)?->email;     // 'a@x'
UserRepository::instance()->findById(999);            // null
UserRepository::instance()->findAllBy();              // every row

The key column’s name comes from the entity — the same one mapIdentifierColumnName() reports.

`findByIdOrThrow()`, `findByOrThrow()`

php

public function findByIdOrThrow(
  string|int $id,
  ?string $entityClassName = null,
  string $message = 'Entity not found',
  HttpCode $httpCode = HttpCode::NOT_FOUND,
): object

Argument	Type	Default	What it does
`$id` / `$qb`	`string\|int` / `Qb`	—	what to look for
`$entityClassName`	`?string`	`null`	the hydration class
`$message`	`string`	`'Entity not found'`	the exception’s text
`$httpCode`	`HttpCode`	`NOT_FOUND`	the status, should the exception reach the HTTP layer

Returns an object — never null. Throws EntityException when there is no row:

text

FlytachiWinterPpaEntityEntityException: Entity not found

php

// instead of three lines with a check
$user = UserRepository::instance()->findByIdOrThrow($id, message: 'No such user');

The point is not brevity but that the exception carries an HTTP status: the error handler turns it into a 404 without a single if in the controller.

`count()`

php

public function count(): int

Returns the number of rows matching the accumulated conditions.

php

UserRepository::instance()->where(Qb::gt('age', 25))->count();   // 2

This is a separate COUNT query, not the length of a selection: no rows are read and none are hydrated.

`exists()`

php

public function exists(): bool

Returns true when at least one row matches.

php

if (UserRepository::instance()->where(Qb::eq('email', $email))->exists()) {
  throw new Conflict('That address is taken');
}

Cheaper than count() > 0 and more honest than find() !== null: the row never crosses the network.

`findColumn()`

php

public function findColumn(int $column = 0): mixed

Argument	Type	Default	What it does
`$column`	`int`	`0`	the column’s position in the `SELECT` list, counting from zero

Returns a single value from the first row — no hydration, no objects created.

php

UserRepository::instance()->select('email')->findColumn();          // 'a@x'
UserRepository::instance()->select('COUNT(*)')->findColumn();      // '4'

For aggregates and single values this is the shortest path: the result never becomes an entity.

`rawFetch()`

php

public function rawFetch(string $sql, array $binds = [], ?string $entityClassName = null): array

Argument	Type	Default	What it does
`$sql`	`string`	—	the whole query, as it is
`$binds`	`array`	`[]`	values for the placeholders
`$entityClassName`	`?string`	`null`	the hydration class

Returns an array of objects.

php

$rows = UserRepository::instance()->rawFetch(
  'SELECT email FROM users WHERE age > :age ORDER BY id LIMIT 2',
  ['age' => 18],
);

For what the builder cannot express: window functions, dialect specifics, a heavy report. The connection and the hydration still come from the repository — only the assembly is given up.

`$binds` is the only safe way in

Pass values through $binds, never by interpolating them into $sql. Interpolation here is exactly the SQL injection the rest of the layer exists to prevent.

Writing

`insert()`

php

public function insert(object|array $entity): mixed

Argument	Type	What it does
`$entity`	`object\|array`	an entity, or an associative “column => value” array

Returns the identifier of the inserted row. On PostgreSQL and MariaDB through RETURNING on the first column, elsewhere through lastInsertId.

php

UserRepository::instance()->insert(['email' => 'a@x', 'age' => 30]);   // "1"

$user = new User();
$user->email = 'b@x';
UserRepository::instance()->insert($user);                            // "2"

Properties whose value is null are not sent — which is why ?int $id = null on the entity lets the database assign the key itself.

The primary key must be the entity's first property

RETURNING takes the first column, and the column order is the order of the properties in the entity class. If the key is not first, what comes back is not the identifier:

$id = $repo->insert(['name' => 'Alice', 'id' => null]);   // returns 'Alice'

The rule: the key is the entity’s first property, and the first key in the array.

`insertBatch()`

php

public function insertBatch(Traversable|object|array ...$entities): void

Inserts many rows in batches, one query per batch.

php

UserRepository::instance()->insertBatch(
  ['email' => 'c@x', 'age' => 20],
  ['email' => 'd@x', 'age' => 25],
);

// a generator: one batch lives in memory at a time, whatever the total
UserRepository::instance()->insertBatch((function () {
  foreach ($hugeSource as $row) {
      yield ['email' => $row->email, 'age' => $row->age];
  }
})());

Returns nothing — a batch has no identifiers. If you need them, insert one at a time with insert().

`update()`

php

public function update(object|array $entity, Qb $qb): string|int

Argument	Type	What it does
`$entity`	`object\|array`	what to write
`$qb`	`Qb`	which rows to change — required

Returns the number of rows changed: 0 when the condition matched nothing.

php

UserRepository::instance()->update(['age' => 31], Qb::eq('email', 'a@x'));      // 1
UserRepository::instance()->update(['age' => 99], Qb::eq('email', 'nobody'));   // 0

The condition is a required argument rather than a default: updating a whole table has to be written out (Qb::raw('1=1')), not arrived at through a forgotten parameter.

`delete()`

php

public function delete(Qb $qb): string|int

Returns the number of rows deleted.

php

UserRepository::instance()->delete(Qb::eq('email', 'd@x'));           // 1
UserRepository::instance()->delete(Qb::lt('created_at', $cutoff));

The condition is required for the same reason as in update().

`upsert()`

php

public function upsert(
  object|array $entity,
  array $conflictColumns,
  ?array $updateColumns = null,
): mixed

“Insert, and if such a row already exists, update it.”

Argument	Type	Default	What it does
`$entity`	`object\|array`	—	what to insert
`$conflictColumns`	`array`	—	which columns decide “the same row”
`$updateColumns`	`?array`	`null`	a “column => expression” map: what to do on a conflict

$updateColumns is a map, not a list of columns. Two placeholders are available inside an expression: :new — the incoming value, :current — what is already stored.

php

// overwrite with the incoming value
$repo->upsert(['email' => 'a@x', 'age' => 30], ['email'], ['age' => ':new']);

// add to what is stored
$repo->upsert(['sku' => 'ABC', 'qty' => 5], ['sku'], ['qty' => ':current + :new']);

A list instead of a map does not pass silently — CDO answers with the corrected call:

text

updateColumns expects a column => expression map, got a plain list at position 0.
Did you mean ['age' => ':new']? Use ':new' for the incoming value and ':current' for the
stored one; pass an empty array to ignore conflicts.

`null` means do nothing, not update everything

null and [] mean the same thing: the conflict is ignored and the stored row is left as it was. Measured: upsert(['email' => 'a@x', 'age' => 20], ['email']) against an existing a@x leaves age at 10.

For anything to be updated, the columns have to be named.

Returns an identifier, like insert().

`upsertBatch()`

php

public function upsertBatch(
  iterable $entities,
  array $conflictColumns,
  ?array $updateColumns = null,
): void

The same for many rows, in batches. $entities is any iterable, a generator included.

php

UserRepository::instance()->upsertBatch(
  [['email' => 'g1@x', 'age' => 1], ['email' => 'z@x', 'age' => 7]],
  ['email'],
  ['age' => ':current + :new'],
);
// the existing g1@x went from age 111 to 112, z@x was inserted

Returns nothing.

Transactions

A transaction lives on a connection, and a unit of work has one — so every repository inside a request joins the same transaction automatically.

php

$db = UserRepository::instance()->db();

$db->beginTransaction();
try {
  $id = UserRepository::instance()->insert(['email' => $email]);
  OrderRepository::instance()->insert(['user_id' => $id, 'total' => 0]);
  $db->commit();
} catch (Throwable $e) {
  $db->rollBack();
  throw $e;
}

Two different repositories write inside one transaction here, although neither knows about it: db() on both returns the same connection of the current unit of work.

A transaction lives on the connection, not on the object

Two consequences. Do not begin a transaction in one coroutine expecting to close it in another — there will be a different connection there. And do not leave one open: the connection returns to the pool with an unfinished transaction and travels on to the next request.

Debugging

The query reaches the database only when something is read or written, so until then it can be inspected.

`buildSql()`

php

public function buildSql(array $ignoreParts = []): string

Argument	Type	Default	What it does
`$ignoreParts`	`array`	`[]`	which parts to leave out of the assembly

Returns the assembled SQL with placeholders — exactly as it will reach the database.

php

echo UserRepository::instance('u')->where(Qb::gt('u.age', 20))->orderBy('u.id DESC')->limit(5, 10)->buildSql();
// SELECT u.id, u.email, u.age FROM users u WHERE u.age > :iqb11 ORDER BY u.id DESC LIMIT 5 OFFSET 10

The values are not here — look at them separately through getSql('binds').

`getSql()`

php

public function getSql(?string $param = null): mixed

Argument	Type	Default	What it does
`$param`	`?string`	`null`	which part to return: `'where'`, `'limit'`, `'binds'`; `null` is the whole query

php

$q = UserRepository::instance('u')->where(Qb::eq('u.id', 1))->limit(3);

$q->getSql();          // "SELECT u.id, u.email FROM users u WHERE u.id = :iqb7 LIMIT 3"
$q->getSql('where');   // "WHERE u.id = :iqb7"
$q->getSql('binds');   // the values that will be bound

`sqlPartsCount()`, `cleanCache()`

php

public function sqlPartsCount(): int
public function cleanCache(?string $param = null): void

sqlPartsCount() reports how many parts have accumulated — a quick way to check that an instance really is clean. cleanCache() drops one part, or the whole query:

php

$repo->cleanCache('where');   // drop the conditions
$repo->cleanCache();          // reset everything; sqlPartsCount() → 0

The assembled SQL is useful beyond debugging

Write operations are also logged at debug level: with LOG_LEVEL=debug the assembled INSERT, UPDATE and DELETE appear in the output. Selections do not — those are read through buildSql().

It also hands the query to places PPA has no part in: an EXPLAIN, a report easier to run raw, or a test that checks the expected SQL.

Utility

Method	What it returns
`db()`	the `CDO` connection of the current unit of work
`originTable()`	the table name — `"users"`
`getSchema()`	the schema, or `null`
`mapIdentifierColumnName()`	the primary key column — `"id"`
`getDbConfigClassName()`	the database config class
`getEntityClassName()`	the hydration class; `stdClass` under a custom `select()`
`binding(?array $binds)`	merges your own `CDOBind`s into the accumulated ones
`state()`	`protected` — the query state of the current coroutine
`useBind($stmt)`	`protected` — binds the values onto a prepared statement

binding() is for when part of a condition is assembled as a string while its values must still travel as parameters:

php

use Flytachi\Winter\Cdo\CDOBind;

UserRepository::instance('u')
  ->where(Qb::raw('u.created_at > :since'))
  ->binding([new CDOBind('since', $date)]);

Why a query has per-coroutine state

A repository may be a singleton in the container, while where(), join() and the rest accumulate parts on the object. Under Swoole one instance serves several requests at once, and without isolation they would build each other’s conditions.

state() returns the state of the current coroutine: under Swoole an object of its own in the coroutine context, outside a coroutine the repository itself — the previous behaviour exactly, at no cost. A subclass needs this when it adds query parts of its own.

Errors

Exception	When
`EntityException`	an `*OrThrow` found no row; it carries an HTTP status
`RepositoryException`	an execution failure: a constraint violation, invalid SQL, a driver refusal

RepositoryException wraps a CDOException, and the original is available through getPrevious() — that is where the SQLSTATE lives, the thing that tells “duplicate key” from “connection lost”.

Entities — what the result is hydrated into, and where the columns come from
Pagination — pages instead of a hand-written limit()
Migrations — how the described schema reaches the database

Repositories

What a repository is

Declaring one

Stereotypes

Getting an instance

instance()

as()

forBy()

Method reference

Assembling a query

select()

from()

where(), andWhere(), orWhere(), xorWhere()

join(), joinInner(), joinLeft(), joinRight(), joinCross()

with(), withRecursive()

union(), unionAll()

groupBy(), having(), orderBy()

limit()

Reading

find()

findAll()

findById(), findBy(), findAllBy()

findByIdOrThrow(), findByOrThrow()

count()

exists()

findColumn()

rawFetch()