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, not only 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 + reading a one-off query with no table of its own: a report across tables, a read from a CTE

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

CteRepo differs from the rest: it is a ready final class, not a base to extend. It has no table or entity of its own — it receives the database configuration in its constructor, and you name the table through from(). Its reading methods are the same as RepositoryView’s (find(), findAll(), count() and the rest); rows are hydrated into stdClass by default. Create it directly, or with the shortcut on the database configuration:

php
$repo = new CteRepo(MainDbConfig::class);   // the same thing: MainDbConfig::cte()

$rows = $repo->from('orders o')
  ->where(Qb::eq('o.status', 'new'))
  ->findAll();                             // list<stdClass>

Reference

Assembling a query

The methods in this group never reach the database. They accumulate parts of a future query inside the repository object and return it again, so the calls form a chain. The query travels to the database only when one of the reading or writing methods is called.

instance()

Creates a repository instance with a clean query state.

The ordinary constructor creates a repository too, but only instance() fills its #[Autowired] and #[Inject] properties from the container. It is also shorter in a chain and takes the table alias straight away — and an alias is needed anywhere a second table appears in the query.

Signature

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

Parameters

$as — the table’s alias in the query. null by default, meaning the table takes part under its own name.

Returns

A fresh repository instance with an empty query state.

Example

php
UserRepository::instance();       // FROM users
UserRepository::instance('u');    // FROM users u

Every call is a separate query

Conditions accumulate in the object. Two calls to instance() produce two independent queries, while adding to an instance obtained earlier appends conditions to whatever it already holds.

That is why a query is assembled in a single chain rather than in pieces across a method.

as()

Sets the alias of the main table on a repository that already exists.

The same thing the argument of instance() does, but usable on an instance you obtained some other way — one injected through the container, for example.

Signature

php
public function as(string $alias): static

Parameters

$alias — the alias. Conditions and column lists refer to it afterwards.

Returns

The same repository.

Example

php
$repository->as('u')->select('u.name')->where(Qb::eq('u.active', 1));

Result

text
SELECT u.name FROM users u WHERE u.active = :iqb0

select()

Sets the list of columns instead of the one used by default.

Without this call the repository selects the entity’s fields — those declared in the class named by $entityClassName. That suits rows being hydrated into an entity; for aggregates and for picking a couple of columns the list is given by hand.

Signature

php
public function select(string $option): static

Parameters

$option — the column list as a string, exactly as it would look in SQL: 'id, name', 'COUNT(*) AS cnt', 'u.name, o.total'.

Returns

The same repository. From this point on rows are hydrated into stdClass rather than the repository’s entity: an arbitrary column list may not match its fields. That also shows in getEntityClassName() — after select() it returns stdClass. If you need your own class, pass it to the reading method, for example findAll(UserCard::class).

Example

php
UserRepository::instance()
  ->select('id, name')
  ->where(Qb::like('name', 'An%'))
  ->orderBy('name ASC')
  ->limit(20, 40);

Result

text
SELECT id, name FROM users WHERE name LIKE :iqb1 ORDER BY name ASC LIMIT 20 OFFSET 40

The column list is the one place that takes SQL text

Values do not go here: binds exist for them. If user input ever appears in this list, that is a defect, and it needs rewriting as a condition through Qb.

from()

Changes the table the selection reads from.

Needed in two cases: selecting from a CTE declared with with(), and making another repository the main table.

Signature

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

Parameters

$repository — another repository, or a name: of a table, a view, or a CTE declared earlier. A repository becomes a FROM (…) subquery, and its binds move into the current query. A subquery needs a name in SQL, so the current repository is given an alias before from() — through instance() or as().

Returns

The same repository.

Errors

RepositoryException — when the source is already set: from() is called once per query. The same exception when a repository is passed and the current one has no alias.

Example

php
UserRepository::instance()
  ->with('recent', OrderRepository::instance())
  ->select('*')
  ->from('recent');

Result

text
WITH recent AS (SELECT id, user_id, total FROM orders) SELECT * FROM recent

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

Add a condition to the query.

where() states a condition; the other three attach the next one with a logical connective: AND, OR, XOR. Conditions are built by the Qb object, which also binds the values — so user input never lands in the query text, however it is written.

Signature

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

Parameters

$qb — the condition. where() also accepts null, which is convenient when a filter is optional and assembled conditionally: null simply adds nothing.

A second where() replaces the condition entirely — together with its values. To add a condition to one already set, use andWhere().

Returns

The same repository.

Example

php
UserRepository::instance()
  ->where(Qb::eq('active', true))
  ->andWhere(Qb::gt('age', 18))
  ->orWhere(Qb::eq('role', 'admin'));

Result

text
SELECT id, name, email, active FROM users
WHERE active IS TRUE AND age > :iqb2 OR role = :iqb3

The main condition constructors

Call SQL
Qb::eq('id', 42) id = :bind
Qb::neq('status', 'draft') status != :bind
Qb::gt, gte, lt, lte >, >=, <, <=
Qb::in('id', [1, 2, 3]) id IN (:b1, :b2, :b3)
Qb::notIn('id', [4, 5]) id NOT IN (…)
Qb::like('name', 'An%') name LIKE :bind
Qb::between('id', 1, 100) id BETWEEN :b1 AND :b2
Qb::isNull('deleted_at') deleted_at IS NULL
Qb::and(...), Qb::or(...) grouping conditions in brackets
Qb::raw('orders.user_id = users.id') text as-is
Qb::raw('views > :v', ['v' => 100]) text as-is, :v bound to 100

`Qb::raw()` text goes into the query as given

This constructor inserts its text into the query unchanged. Values go in the second argument, array $binds — name-to-value pairs or CDOBind objects — and are bound as in the other constructors. Without binds it suits conditions that hold no values at all — comparing two columns in a join, for instance. Nothing that came from a user may be written into the text itself: that is a direct route to SQL injection.

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

Attach another table to the query.

All five take a repository or a string. A string carries the name of a table, a CTE or a view together with an alias — 'recent_buyers rb'; it goes into the query as is. A repository brings its own table name and schema. If conditions or other query parts are already assembled on it, it is attached as a (SELECT …) subquery together with its binds. A subquery needs a name in SQL, so such a repository must be named through as() or instance('alias').

Signature

php
public function join(RepositoryInterface|string $repository, Qb|string $on): static
public function joinInner(RepositoryInterface|string $repository, Qb|string $on): static
public function joinLeft(RepositoryInterface|string $repository, Qb|string $on): static
public function joinRight(RepositoryInterface|string $repository, Qb|string $on): static
public function joinCross(RepositoryInterface|string $repository): static

Parameters

$repository — the repository of the table being attached, or a string with a name and an alias.

$on — the join condition. joinCross() has none: a cross join by definition has no condition.

Returns

The same repository.

Errors

RepositoryException when the joined repository carries a query of its own — conditions, select(), ordering, limit() and so on — but has no alias. Such a repository becomes a subquery, and only the caller knows the alias the ON condition refers to; silently dropping its query or building a nameless subquery would be worse. A bare repository with no query of its own joins by table name and needs no alias.

Which of the five to take

Method SQL What it does
join() JOIN as the database decides; in practice the same as INNER
joinInner() INNER JOIN only rows for which a match was found
joinLeft() LEFT JOIN every row of the left table; NULL in the right one’s columns where there is no match
joinRight() RIGHT JOIN the mirror image of the above
joinCross() CROSS JOIN every combination of rows from both tables

Example

php
UserRepository::instance()
  ->select('users.name, orders.total')
  ->joinLeft(OrderRepository::instance(), Qb::raw('orders.user_id = users.id'));

Result

text
SELECT users.name, orders.total FROM users
LEFT JOIN orders ON(orders.user_id = users.id)

groupBy()

Groups rows by the value of some columns — for aggregates such as COUNT, SUM, AVG.

Signature

php
public function groupBy(string $context): static

Parameters

$context — the column list as a string: 'email', 'user_id, status'.

Returns

The same repository.

Example

php
UserRepository::instance()
  ->select('email, COUNT(*) AS cnt')
  ->groupBy('email');

having()

Filters rows that have already been grouped.

It differs from where() in when it applies: WHERE discards rows before grouping, HAVING discards the resulting groups. So an aggregate (COUNT(*) > 1) can only be tested here.

Signature

php
public function having(string $context): static

Parameters

$context — the condition as a string. There are no binds here, so user input has no place in it.

Returns

The same repository.

Example

php
UserRepository::instance()
  ->select('email, COUNT(*) AS cnt')
  ->groupBy('email')
  ->having('COUNT(*) > 1');

Result

text
SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1

orderBy()

Sets the order of rows in the result.

Signature

php
public function orderBy(string $context): static

Parameters

$context — the columns and direction: 'id DESC', 'name ASC, created_at DESC'.

Returns

The same repository.

Example

php
UserRepository::instance()->orderBy('created_at DESC, id DESC');

An order is needed wherever there are pages

Without ORDER BY a database is not obliged to return rows in the same order between queries. In practice that shows up like this: one and the same record lands on page one and on page two, while another lands nowhere.

limit()

Caps the number of rows and sets an offset.

Signature

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

Parameters

$limit — how many rows to return. At least 1.

$offset — how many to skip from the start. 0 by default; it cannot be negative.

Returns

The same repository.

Errors

ValueError — when $limit is below 1 or $offset is below 0. Thrown right at the call, before the database is involved. The part of the query assembled so far is discarded: a half-built query cannot be run, and it does not stick to the next call.

A second limit() replaces the offset too: limit(10) after limit(10, 20) starts from the first row again.

Example

php
UserRepository::instance()->orderBy('id')->limit(20, 40);   // page three, twenty per page

Result

text
SELECT id, name, email, active FROM users ORDER BY id LIMIT 20 OFFSET 40

Pages have a tool of their own

Working offsets out by hand is rarely necessary: paging with a page counter and with a cursor is covered on Pagination.

forBy()

Adds row locking at the end of the query — FOR UPDATE and its relatives.

Needed when the selected rows are about to be changed inside the same transaction and nobody else may change them meanwhile.

Signature

php
public function forBy(string $context): static

Parameters

$context — the locking clause: 'UPDATE', 'SHARE', 'UPDATE NOWAIT', 'UPDATE SKIP LOCKED'. What is available depends on the database.

Returns

The same repository.

Example

php
$order = OrderRepository::instance()
  ->where(Qb::eq('id', $id))
  ->forBy('UPDATE')
  ->find();

union(), unionAll()

Combine the result with the result of another repository.

union() removes duplicate rows; unionAll() keeps everything as it is and is therefore faster.

Signature

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

Parameters

$repository — the repository whose result is attached.

Returns

The same repository.

Example

php
ActiveUserRepository::instance()->select('name')
  ->unionAll(ArchivedUserRepository::instance()->select('name'));

The column lists have to match

Both halves of a union must return the same number of columns of compatible types — SQL requires it, and the repository cannot check it for you. The practical consequence: give both sides an explicit select(), or each will supply the fields of its own entity.

with(), withRecursive()

Declare a common table expression — a CTE, a named subquery that can then be referred to as a table.

withRecursive() declares a recursive expression, one that refers to itself. That is how trees are walked: categories, comments, reporting lines.

Signature

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

Parameters

$name — the expression’s name. from() and joins refer to it by this name.

$repository — the repository whose query becomes the body of the expression.

$modifier — an extra instruction to the database, MATERIALIZED for instance. Support depends on the database.

Returns

The same repository.

Example

php
UserRepository::instance()
  ->with('recent', OrderRepository::instance())
  ->select('*')
  ->from('recent');

Result

text
WITH recent AS (SELECT id, user_id, total FROM orders) SELECT * FROM recent

binding()

Adds value binds by hand.

Needed in a rare case: when the query holds a named placeholder that did not come from Qb — inside an expression passed to select() or having(), for example.

Signature

php
public function binding(?array $binds): static

Parameters

$binds — an array of CDOBind bind objects. A bind with a name that already exists replaces the earlier one. null and an empty array do nothing — binds added earlier stay.

Returns

The same repository.


Reading

The methods in this group execute the assembled query. Afterwards the state is cleared — the same instance can be assembled again, and accumulated conditions do not stick to the next query. This holds when the query failed too: the database was unavailable, the SQL had an error. A query is used once; repeating it is the caller’s job, not something left over in the repository. For a repository injected into a process or a daemon this matters most: it lives as long as the process.

Assemble a query and execute it in one expression

The reset happens when a method from this group runs. If a query is assembled but never executed — an exception in your code between where() and findAll(), or a write or rawFetch() called instead of a read, neither of which uses the assembled query — the conditions stay on the repository and reach its next query. Inside an HTTP request under Swoole this lasts until the request ends; in a process or a daemon, longer.

The framework cannot learn about such an exception, so the rule is simple: the chain from the first condition to the execution is one expression, with no code in between.

php
// like this: assembled and executed at once
$users = $this->users->where(Qb::eq('active', true))->orderBy('id')->findAll();

// not like this: code that can throw sits between assembling and executing
$this->users->where(Qb::eq('active', true));
$this->checkQuota();          // an exception here leaves where() on the repository
$users = $this->users->findAll();

If a query has to be assembled step by step, assemble it on a separate instance — instance(): it does not outlive that code.

find()

Executes the query and returns the first matching row.

A limit of one row is added automatically, so the database does not fetch more than needed even when thousands of rows match.

Signature

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

Parameters

$entityClassName — the class to hydrate the row into. Defaults to the repository’s $entityClassName.

Returns

An entity object, or null when nothing was found.

Errors

RepositoryException — on a database error: unreachable, invalid query syntax, a constraint violated.

Example

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

if ($user === null) {
  return ResponseEntity::notFound(['message' => 'User not found']);
}

findAll()

Executes the query and returns every matching row.

Signature

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

Parameters

$entityClassName — the class to hydrate into. Defaults to the repository’s entity.

Returns

An array of objects. An empty array when nothing matched — not null — so the result can be iterated straight away without a check.

Errors

RepositoryException — on a database error.

Example

php
$users = UserRepository::instance()
  ->where(Qb::eq('active', true))
  ->orderBy('name ASC')
  ->findAll();

Result

text
[{"id":1,"name":"Anna P.","email":"anna@example.com","active":1}]

Set the limit yourself

findAll() without limit() returns every row matching the condition, and all of them end up in the process’s memory. On a table of a million records that ends in exhausted memory.

findById()

Returns a row by its primary key.

Shorthand for where(Qb::eq(<key column>, $id))->find(). The key column name comes from the mapIdentifierColumnName() method and is 'id' by default. If the table’s key is named differently, override that method in your repository.

Signature

php
public function findById(string|int $id, ?string $entityClassName = null): ?object

Parameters

$id — the primary key value.

$entityClassName — the class to hydrate into.

Returns

An entity object, or null.

Example

php
$user = UserRepository::instance()->findById(1);

Result

text
{"id":1,"name":"Anna","email":"anna@example.com","active":1}

findBy()

Returns the first row matching the condition passed in.

It differs from find() in that the condition comes as an argument rather than being assembled in a chain. Convenient for one-line lookups.

Signature

php
public function findBy(Qb $qb, ?string $entityClassName = null): ?object

Parameters

$qb — the condition.

$entityClassName — the class to hydrate into.

Returns

An entity object, or null.

Example

php
$user = UserRepository::instance()->findBy(Qb::eq('email', $email));

findAllBy()

Returns every row matching the condition passed in.

Signature

php
public function findAllBy(?Qb $qb = null, ?string $entityClassName = null): array

Parameters

$qb — the condition. null (the default) means “no condition” — every row of the table comes back.

$entityClassName — the class to hydrate into.

Returns

An array of objects, possibly empty.

Example

php
$active = UserRepository::instance()->findAllBy(Qb::eq('active', true));
$all    = UserRepository::instance()->findAllBy();          // the whole table

findByIdOrThrow()

Returns a row by its primary key, or throws when there is none.

It exists to remove the repeated “found it — did not — return a 404” check. The exception surfaces and the framework turns it into an HTTP response, so the layers in between need neither to check nor to pass the result along.

Signature

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

Parameters

$id — the primary key value.

$entityClassName — the class to hydrate into.

$message — the exception’s message. It reaches the client, so write it the way you are prepared to show it.

$httpCode — the status code the exception becomes. 404 by default.

Returns

An entity object. It never returns null.

Errors

EntityException — when the row was not found. The status code comes from $httpCode.

Example

php
#[GetMapping('users/{id}')]
public function show(#[PathVariable] int $id): ResponseEntity
{
  $user = UserRepository::instance()->findByIdOrThrow($id, message: 'User not found');

  return ResponseEntity::ok($user);   // reached only when the user exists
}

findByOrThrow()

The same as findByIdOrThrow(), but by an arbitrary condition.

Signature

php
public function findByOrThrow(
  Qb $qb,
  ?string $entityClassName = null,
  string $message = 'Entity not found',
  HttpCode $httpCode = HttpCode::NOT_FOUND,
): object

Parameters

$qb — the search condition.

The rest match findByIdOrThrow().

Returns

An entity object.

Errors

EntityException — when the row was not found.

Example

php
$order = OrderRepository::instance()->findByOrThrow(
  Qb::and(Qb::eq('number', $number), Qb::eq('user_id', $userId)),
  message: 'Order not found',
);

findColumn()

Returns the value of one column of the first row.

For cases where no object is wanted: one name, one total, one identifier.

Signature

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

Parameters

$column — the column’s position in the selection list, counting from zero. The first by default.

Returns

The value as the database gave it, or false when there are no rows.

Example

php
$name = UserRepository::instance()
  ->select('name')
  ->where(Qb::eq('id', 1))
  ->findColumn();

Result

text
'Anna'

count()

Returns the number of rows matching the assembled query.

The database does the counting — the rows never reach memory. The conditions assembled before the call are kept, while orderBy(), limit() and forBy() take no part: for a page query count() returns the total number of rows, not the size of the page.

A plain filter is counted directly — SELECT COUNT(*) FROM users WHERE …. A query whose rows are not plain table rows (its own select(), groupBy(), having(), a union) is counted as a subquery: SELECT COUNT(*) FROM (…) AS tmp. So a grouped query gives the number of groups, a union the rows of both parts, DISTINCT the number of distinct values.

Signature

php
public function count(): int

Returns

The number of rows.

Example

php
// how many active users — whatever the page
$repo  = UserRepository::instance()->where(Qb::eq('active', true));
$total = $repo->count();

// how many different cities the users live in
$cities = UserRepository::instance()->select('city')->groupBy('city')->count();

Result

sql
SELECT COUNT(*) FROM users WHERE active IS TRUE
SELECT COUNT(*) FROM (SELECT city FROM users GROUP BY city) AS tmp

exists()

Reports whether at least one row matches the assembled query.

It differs from count() > 0 in that the database stops at the first match instead of counting the rest. On a large table the difference between “is there one” and “how many are there” is substantial.

Signature

php
public function exists(): bool

Returns

true when at least one row was found.

Example

php
if (UserRepository::instance()->where(Qb::eq('email', $email))->exists()) {
  throw new ResponseException('That email is already taken', HttpCode::CONFLICT);
}

rawFetch()

Runs arbitrary SQL and returns hydrated objects.

The way out for queries the assembly cannot express: window functions, database-specific constructs, heavy reports. State assembled in a chain is not used — exactly the SQL you passed is executed.

Signature

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

Parameters

$sql — the query text with named placeholders.

$binds — an array of CDOBind objects, one per placeholder.

$entityClassName — the class to hydrate rows into. Defaults to the repository’s entity.

Returns

An array of objects.

Errors

RepositoryException — on a database error.

Example

php
final class EmailStat
{
  public string $email = '';
  public int $cnt = 0;
}

$stats = UserRepository::instance()->rawFetch(
  'SELECT email, COUNT(*) AS cnt FROM users GROUP BY email',
  entityClassName: EmailStat::class,
);

Result

text
[{"email":"a@example.com","cnt":2}]

State the hydration class explicitly

Without the third argument the rows are hydrated into the repository’s entity. For a query whose columns do not match it, that yields an object half-filled with defaults, while the surplus columns become dynamic properties — which since PHP 8.2 raises an E_DEPRECATED notice.

Pass a class shaped like your result, as in the example, or stdClass::class when the shape is not known in advance.


Writing

Like the reading methods, all of these execute the query immediately. They do not use the conditions, joins and other query parts assembled in the chain: everything they need is passed as arguments — the row data and, for update() and delete(), the selection condition.

insert()

Adds one row to the table.

Signature

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

Parameters

$entity — an entity object, or an associative array of column to value. The array is handy when only some fields are filled and building an object for it is not worth it.

Returns

The identifier the database generated for the inserted row, exactly as the database gave it. On SQLite and MySQL it is taken from the connection (lastInsertId()), and it is a string, not a number — cast it to int yourself if an int is what you need. On PostgreSQL and MariaDB the query is extended with RETURNING <column>, where the column is the first field of the entity or array passed in. When the database reports no identifier (MySQL inserting into a table without auto-increment, for example), null is returned. SQLite, on the other hand, always gives the internal row number (rowid) — even when the key was passed explicitly and is not numeric.

Errors

RepositoryException — on a database error: a unique constraint violated, a required column missing, no connection available.

Example

php
$user = new User();
$user->name  = 'Anna';
$user->email = 'anna@example.com';

$id = UserRepository::instance()->insert($user);

// or as an array, without building an object
$id = UserRepository::instance()->insert(['name' => 'Boris', 'email' => 'boris@example.com']);

Result

text
'1'   // a string, not a number

insertBatch()

Adds many rows in one pass.

It differs from a loop of insert() calls in that rows travel to the database in batches rather than one at a time: over a thousand records that is the difference between a thousand round trips and a handful.

The method takes a variable number of arguments, and every argument is read by one rule: an array is one row, a traversable object (Traversable, a generator for instance) is a stream of rows, and any other object is one entity. A generator hands rows over one at a time, and the database receives them in batches as each fills up, so no more than one batch sits in memory at once, however many rows there are in total.

Signature

php
public function insertBatch(iterable|object ...$entities): void

Parameters

...$entities — the rows to insert: entity objects, column-to-value arrays (each array is one row) and streams of rows (Traversable), in any mix. A ready list of rows is passed unpacked: insertBatch(...$rows). Passing the list as one argument — insertBatch($rows) — makes it a single row with columns 0, 1, …, and the database returns an error.

Returns

Nothing. The identifiers of the inserted rows are not returned — when they are needed, insert one at a time with insert().

Errors

RepositoryException — on a database error.

Example

php
function readRows(string $path): Generator
{
  $handle = fopen($path, 'rb');
  while (($row = fgetcsv($handle, escape: '')) !== false) {
      yield ['name' => $row[0], 'email' => $row[1]];
  }
  fclose($handle);
}

// a stream: rows are read from the file as they are inserted
UserRepository::instance()->insertBatch(readRows('/tmp/import.csv'));

// a ready list: unpack it so that each array becomes its own row
$rows = [
  ['name' => 'Anna',  'email' => 'anna@example.com'],
  ['name' => 'Boris', 'email' => 'boris@example.com'],
];
UserRepository::instance()->insertBatch(...$rows);

update()

Changes the rows matching a condition.

Signature

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

Parameters

$entity — the new values: an entity object, or an array of column to value. An array updates some columns while leaving the rest alone.

$qb — the condition selecting the rows. The argument is required: updating a whole table has to be written out, not arrived at by accident.

Returns

The number of rows changed.

Errors

RepositoryException — on a database error.

Example

php
$affected = UserRepository::instance()->update(
  ['name' => 'Anna P.'],
  Qb::eq('id', 1),
);

Result

text
1

delete()

Removes the rows matching a condition.

Signature

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

Parameters

$qb — the condition selecting the rows. Required for the same reason as in update().

Returns

The number of rows removed.

Errors

RepositoryException — on a database error, a foreign key violation included when something still refers to the row.

Example

php
$deleted = UserRepository::instance()->delete(Qb::eq('id', 1));

upsert()

Inserts a row, or updates it when one already exists.

What counts as “already exists” is decided by the columns the database checks for a conflict: usually the primary key or a unique index. It happens in a single query, so no other process can slip in between the check and the insert — unlike a pairing of exists() and then an insert.

Signature

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

Parameters

$entity — the row’s data: an entity object or an array.

$conflictColumns — the columns a conflict is decided by. They need a unique index, or the database will not understand the query.

$updateColumns — what to do with the columns on a conflict: a column-to-expression array. In an expression ':new' stands for the incoming value and ':current' for the value already stored in the row: ['name' => ':new'] writes the new name, ['visits' => ':current + 1'] increments a counter. null (the default) and an empty array [] mean the same thing — the conflict is ignored: the row stays as it was and the insert simply does not happen. A plain list of columns (['name']) is not accepted — an exception is thrown with a hint showing how to write the call correctly.

Returns

The row’s identifier, with per-database caveats. On PostgreSQL the query is extended with RETURNING, and the real key of the inserted or updated row comes back (null when the conflict was ignored). On SQLite, MySQL and MariaDB the connection’s lastInsertId() is returned — on a conflict this is not the key of the conflicting row but whatever the last insert on this connection produced. Where the key of the existing row is needed, find the row with a separate query.

Errors

RepositoryException — on a database error, when $conflictColumns is empty, and when $updateColumns is passed as a plain list of columns.

Example

php
// Update the name when a user with that email already exists
UserRepository::instance()->upsert(
  ['email' => 'anna@example.com', 'name' => 'Anna P.'],
  conflictColumns: ['email'],
  updateColumns:   ['name' => ':new'],
);

// Insert when missing; leave it alone when present
UserRepository::instance()->upsert(
  ['email' => 'anna@example.com', 'name' => 'Anna'],
  conflictColumns: ['email'],
);

`null` in the third argument updates nothing

The default reads easily as “update every column” — and it means the opposite. When a conflict should change the row, list the columns explicitly — with an expression for each.

upsertBatch()

The same as upsert(), but for many rows in one pass.

Like insertBatch(), it takes an iterable and sends the rows in batches.

Signature

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

Parameters

$entities — an array, a traversable or a generator of rows.

$conflictColumns — the conflict columns, shared by every row.

$updateColumns — a column-to-expression array with ':new' and ':current', as in upsert(); null or [] — conflicts are ignored.

Returns

Nothing.

Example

php
// readRows() is the generator from the insertBatch() example
UserRepository::instance()->upsertBatch(
  readRows('/tmp/import.csv'),
  conflictColumns: ['email'],
  updateColumns:   ['name' => ':new'],
);

Transactions

A transaction opens on a connection, and a unit of work has one connection — so every repository inside a single 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, though neither knows about it: db() on both hands back the same connection of the current unit of work.

The same can be written with transaction(): it calls commit() itself when the callback completes and rollBack() when it throws — and then rethrows the exception.

php
$db->transaction(function () use ($email) {
  $id = UserRepository::instance()->insert(['email' => $email]);
  OrderRepository::instance()->insert(['user_id' => $id, 'total' => 0]);
});

A transaction lives on the connection, not on the object

Two consequences follow. 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: when the connection returns to the pool it will be rolled back, everything uncommitted is lost, and an ERROR appears in the log.

In a process or a daemon there is no safety net

The rollback on return works where the connection goes back to the pool — in an HTTP request under Swoole. A process and a daemon hold one connection for their whole life and never return it, so a transaction left without rollBack() stays open — until the process restarts:

  • PostgreSQL refuses every later query: SQLSTATE[25P02]: current transaction is aborted;
  • MySQL reports no error, but nothing is committed any more: everything written afterwards is lost when the process stops.

Most often it happens when an exception leaves a loop iteration between beginTransaction() and commit(). In processes and daemons open transactions only through transaction(): it rolls back on any exception.


Debugging and housekeeping

buildSql()

Assembles the query text without executing it.

The first thing to reach for when the result is not what you expected: it shows what the chain of calls turned into.

Signature

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

Parameters

$ignoreParts — the parts to leave out of the assembly, ['limit'] for instance. Everything is assembled by default.

Returns

The query text with bind names in place of values. The values themselves are seen through getSql().

Example

php
$sql = UserRepository::instance()
  ->where(Qb::eq('id', 42))
  ->buildSql();

Result

text
SELECT id, name, email FROM users WHERE id = :iqb0

getSql()

Returns the text of the assembled query, or one of its accumulated parts.

Without an argument it is the same as buildSql(). With a part name it returns that part’s content: needed when seeing the text is not enough — to find out which value went into a particular bind, for example.

Signature

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

Parameters

$param — the name of the part you want. null (the default) builds and returns the whole query. Part names: 'binds' (the binds), 'option' (the column list from select()), 'from', 'as', 'join', 'where', 'group', 'having', 'union', 'order', 'limit', 'offset', 'for', 'with'.

Returns

An SQL string when $param is null. Otherwise the part’s value: for 'binds' an array of bind name to CDOBind object, for 'limit' and 'offset' a number, for the rest an SQL fragment as a string. When the part is not set or the name is unknown — null.

Errors

RepositoryException — when the query cannot be built (only with $param = null).

Example

php
$repository = UserRepository::instance()->where(Qb::eq('id', 42));

$sql   = $repository->getSql();          // the whole query as a string
$binds = $repository->getSql('binds');   // the binds only

echo $sql, PHP_EOL;
foreach ($binds as $name => $bind) {
  echo $name, ' = ', var_export($bind->getValue(), true), PHP_EOL;
}

Result

text
SELECT id, name, email FROM users WHERE id = :iqb0
:iqb0 = 42

sqlPartsCount()

Returns the number of accumulated query parts.

It has one practical use: checking that the state is empty. A fresh instance answers 0, and if it answers more where you expected a clean repository, something has already been assembled on that object.

Signature

php
public function sqlPartsCount(): int

Returns

The number of parts; 0 on an untouched repository.

Example

php
$repository = UserRepository::instance();

$repository->sqlPartsCount();                         // 0
$repository->where(Qb::eq('id', 1))->sqlPartsCount(); // more than zero

cleanCache()

Discards the accumulated query state.

The reading methods call it themselves after executing the query — and when the query failed, too — so by hand it is needed in one case: a query was started, then abandoned, and the same instance is to be reused.

Signature

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

Parameters

$param — the name of the part to discard. null (the default) discards everything. The part’s values go with it: cleanCache('where') removes the condition and everything bound to it.

Example

php
$repository = UserRepository::instance()->where(Qb::eq('active', true));

if ($skipFilter) {
  $repository->cleanCache();     // the condition is no longer wanted
}

db()

Returns the database connection the repository works on.

Transactions are opened through it and, when necessary, the database is addressed directly. It is the same connection every other repository of the same configuration uses within one request.

Signature

php
public function db(): CDO

Returns

The connection object.

Example

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

mapIdentifierColumnName()

Returns the name of the primary key column.

It is used by findById() and findByIdOrThrow(). By default it is 'id'. The method is public and not final: if the table’s key is named differently, override it in your repository.

Signature

php
public function mapIdentifierColumnName(): string

Returns

The column name as a string; 'id' by default.

Example

php
/** @extends Repository<Country> */
final class CountryRepository extends Repository
{
  public static string $table = 'countries';
  protected string $dbConfigClassName = MainDbConfig::class;
  protected string $entityClassName = Country::class;

  public function mapIdentifierColumnName(): string
  {
      return 'code';
  }
}

$country = CountryRepository::instance()->findById('UZ');   // WHERE code = :iqb0

Details about the repository

Four methods returning what the class’s properties declare. Rarely needed — mostly by tooling such as migrations and generators.

php
public function getDbConfigClassName(): string   // the database configuration class
public function getEntityClassName(): string     // the entity class
public function getSchema(): ?string             // the schema, or null
public function originTable(): string            // the table name, with the schema when one is set

Errors

Exception When What the framework does with it
RepositoryException a database error: unreachable, invalid SQL, a constraint violated becomes a 500 response
EntityException the row was not found in findByIdOrThrow() / findByOrThrow() becomes a response with the code passed in, 404 by default

Both surface on their own, so catching them in a controller is usually unnecessary: the framework builds the response. Catch them where there is a sensible reaction — a retry, a fallback source, marking a job as failed.

php
try {
  $user = UserRepository::instance()->findByIdOrThrow($id);
} catch (EntityException) {
  UserRepository::instance()->insert(['id' => $id, 'name' => 'Guest']);
  $user = UserRepository::instance()->findByIdOrThrow($id);
}

Next