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.
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.
// 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
<?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:
$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
public static function instance(?string $as = null): staticParameters
$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
UserRepository::instance(); // FROM users
UserRepository::instance('u'); // FROM users uEvery 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
public function as(string $alias): staticParameters
$alias — the alias. Conditions and column lists refer to it afterwards.
Returns
The same repository.
Example
$repository->as('u')->select('u.name')->where(Qb::eq('u.active', 1));Result
SELECT u.name FROM users u WHERE u.active = :iqb0select()
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
public function select(string $option): staticParameters
$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
UserRepository::instance()
->select('id, name')
->where(Qb::like('name', 'An%'))
->orderBy('name ASC')
->limit(20, 40);Result
SELECT id, name FROM users WHERE name LIKE :iqb1 ORDER BY name ASC LIMIT 20 OFFSET 40The 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
public function from(RepositoryInterface|string $repository): staticParameters
$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
UserRepository::instance()
->with('recent', OrderRepository::instance())
->select('*')
->from('recent');Result
WITH recent AS (SELECT id, user_id, total FROM orders) SELECT * FROM recentwhere(), 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
public function where(?Qb $qb): static
public function andWhere(Qb $qb): static
public function orWhere(Qb $qb): static
public function xorWhere(Qb $qb): staticParameters
$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
UserRepository::instance()
->where(Qb::eq('active', true))
->andWhere(Qb::gt('age', 18))
->orWhere(Qb::eq('role', 'admin'));Result
SELECT id, name, email, active FROM users
WHERE active IS TRUE AND age > :iqb2 OR role = :iqb3The 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
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): staticParameters
$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
UserRepository::instance()
->select('users.name, orders.total')
->joinLeft(OrderRepository::instance(), Qb::raw('orders.user_id = users.id'));Result
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
public function groupBy(string $context): staticParameters
$context — the column list as a string: 'email', 'user_id, status'.
Returns
The same repository.
Example
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
public function having(string $context): staticParameters
$context — the condition as a string. There are no binds here, so user input has no
place in it.
Returns
The same repository.
Example
UserRepository::instance()
->select('email, COUNT(*) AS cnt')
->groupBy('email')
->having('COUNT(*) > 1');Result
SELECT email, COUNT(*) AS cnt FROM users GROUP BY email HAVING COUNT(*) > 1orderBy()
Sets the order of rows in the result.
Signature
public function orderBy(string $context): staticParameters
$context — the columns and direction: 'id DESC', 'name ASC, created_at DESC'.
Returns
The same repository.
Example
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
public function limit(int $limit, int $offset = 0): staticParameters
$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
UserRepository::instance()->orderBy('id')->limit(20, 40); // page three, twenty per pageResult
SELECT id, name, email, active FROM users ORDER BY id LIMIT 20 OFFSET 40Pages 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
public function forBy(string $context): staticParameters
$context — the locking clause: 'UPDATE', 'SHARE', 'UPDATE NOWAIT',
'UPDATE SKIP LOCKED'. What is available depends on the database.
Returns
The same repository.
Example
$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
public function union(RepositoryInterface $repository): static
public function unionAll(RepositoryInterface $repository): staticParameters
$repository — the repository whose result is attached.
Returns
The same repository.
Example
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
public function with(string $name, RepositoryInterface $repository, ?string $modifier = null): static
public function withRecursive(string $name, RepositoryInterface $repository): staticParameters
$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
UserRepository::instance()
->with('recent', OrderRepository::instance())
->select('*')
->from('recent');Result
WITH recent AS (SELECT id, user_id, total FROM orders) SELECT * FROM recentbinding()
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
public function binding(?array $binds): staticParameters
$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.
// 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
public function find(?string $entityClassName = null): ?objectParameters
$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
$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
public function findAll(?string $entityClassName = null): arrayParameters
$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
$users = UserRepository::instance()
->where(Qb::eq('active', true))
->orderBy('name ASC')
->findAll();Result
[{"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
public function findById(string|int $id, ?string $entityClassName = null): ?objectParameters
$id — the primary key value.
$entityClassName — the class to hydrate into.
Returns
An entity object, or null.
Example
$user = UserRepository::instance()->findById(1);Result
{"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
public function findBy(Qb $qb, ?string $entityClassName = null): ?objectParameters
$qb — the condition.
$entityClassName — the class to hydrate into.
Returns
An entity object, or null.
Example
$user = UserRepository::instance()->findBy(Qb::eq('email', $email));findAllBy()
Returns every row matching the condition passed in.
Signature
public function findAllBy(?Qb $qb = null, ?string $entityClassName = null): arrayParameters
$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
$active = UserRepository::instance()->findAllBy(Qb::eq('active', true));
$all = UserRepository::instance()->findAllBy(); // the whole tablefindByIdOrThrow()
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
public function findByIdOrThrow(
string|int $id,
?string $entityClassName = null,
string $message = 'Entity not found',
HttpCode $httpCode = HttpCode::NOT_FOUND,
): objectParameters
$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
#[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
public function findByOrThrow(
Qb $qb,
?string $entityClassName = null,
string $message = 'Entity not found',
HttpCode $httpCode = HttpCode::NOT_FOUND,
): objectParameters
$qb — the search condition.
The rest match findByIdOrThrow().
Returns
An entity object.
Errors
EntityException — when the row was not found.
Example
$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
public function findColumn(int $column = 0): mixedParameters
$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
$name = UserRepository::instance()
->select('name')
->where(Qb::eq('id', 1))
->findColumn();Result
'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
public function count(): intReturns
The number of rows.
Example
// 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
SELECT COUNT(*) FROM users WHERE active IS TRUE
SELECT COUNT(*) FROM (SELECT city FROM users GROUP BY city) AS tmpexists()
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
public function exists(): boolReturns
true when at least one row was found.
Example
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
public function rawFetch(string $sql, array $binds = [], ?string $entityClassName = null): arrayParameters
$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
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
[{"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
public function insert(object|array $entity): mixedParameters
$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
$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
'1' // a string, not a numberinsertBatch()
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
public function insertBatch(iterable|object ...$entities): voidParameters
...$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
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
public function update(object|array $entity, Qb $qb): string|intParameters
$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
$affected = UserRepository::instance()->update(
['name' => 'Anna P.'],
Qb::eq('id', 1),
);Result
1delete()
Removes the rows matching a condition.
Signature
public function delete(Qb $qb): string|intParameters
$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
$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
public function upsert(object|array $entity, array $conflictColumns, ?array $updateColumns = null): mixedParameters
$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
// 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
public function upsertBatch(iterable $entities, array $conflictColumns, ?array $updateColumns = null): voidParameters
$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
// 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.
$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.
$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
public function buildSql(array $ignoreParts = []): stringParameters
$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
$sql = UserRepository::instance()
->where(Qb::eq('id', 42))
->buildSql();Result
SELECT id, name, email FROM users WHERE id = :iqb0getSql()
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
public function getSql(?string $param = null): mixedParameters
$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
$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
SELECT id, name, email FROM users WHERE id = :iqb0
:iqb0 = 42sqlPartsCount()
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
public function sqlPartsCount(): intReturns
The number of parts; 0 on an untouched repository.
Example
$repository = UserRepository::instance();
$repository->sqlPartsCount(); // 0
$repository->where(Qb::eq('id', 1))->sqlPartsCount(); // more than zerocleanCache()
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
public function cleanCache(?string $param = null): voidParameters
$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
$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
public function db(): CDOReturns
The connection object.
Example
$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
public function mapIdentifierColumnName(): stringReturns
The column name as a string; 'id' by default.
Example
/** @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 = :iqb0Details about the repository
Four methods returning what the class’s properties declare. Rarely needed — mostly by tooling such as migrations and generators.
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 setErrors
| 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.
try {
$user = UserRepository::instance()->findByIdOrThrow($id);
} catch (EntityException) {
UserRepository::instance()->insert(['id' => $id, 'name' => 'Guest']);
$user = UserRepository::instance()->findByIdOrThrow($id);
}Next
- Entities — what rows are hydrated into, and how columns are marked up
- Pagination — paging instead of working out
limit()by hand - Database connection — which database the repository talks to
- Connection pool — where
db()’s connection comes from