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 rather than 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;
}

Stereotypes

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

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 works too, but instance() is 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): 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

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

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): static

Parameters

$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 = :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

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.

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 40

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): static

Parameters

$repository — another repository, or a name: of a table, a view, or a CTE declared earlier.

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 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

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.

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 = :iqb3

The main condition constructors

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

Attach another table to the query.

All five take a repository rather than a table name. That is not a formality: the attached repository brings its own table name, its schema and — if conditions are already assembled on it — its own binds.

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): static

Parameters

$repository — the repository of the table being attached, or its name as a string.

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

Returns

The same repository.

Which of the five to take

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): static

Parameters

$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): 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

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(*) > 1

orderBy()

Sets the order of rows in the result.

Signature

public function orderBy(string $context): static

Parameters

$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');

limit()

Caps the number of rows and sets an offset.

Signature

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

Parameters

$limit — how many rows to return.

$offset — how many to skip from the start. 0 by default.

Returns

The same repository.

Example

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

Result

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

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): 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

$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): static

Parameters

$repository — the repository whose result is attached.

Returns

The same repository.

Example

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

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): 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

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

Result

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

public function binding(?array $binds): static

Parameters

$binds — an array of binds. null clears the ones added earlier.

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.

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): ?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

$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): 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

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

Result

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

findById()

Returns a row by its primary key.

Shorthand for where(Qb::eq(<primary key>, $id))->find(). The repository works out the key column itself from the entity’s attributes.

Signature

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

$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): ?object

Parameters

$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): 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

$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

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

#[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,
): object

Parameters

$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): 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

$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. Conditions, joins and grouping assembled before the call are taken into account.

Signature

public function count(): int

Returns

The number of rows.

Example

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

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

public function exists(): bool

Returns

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): 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

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}]

Writing

Like the reading methods, all of these execute the query immediately and clear the repository’s state afterwards.

insert()

Adds one row to the table.

Signature

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 of the inserted row exactly as the database gave it — for an auto-incrementing column that is a string, not a number. Cast it to int yourself if an int is what you need.

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 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. It takes an iterable, so a generator works too — and then not every row sits in memory at once.

Signature

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

Parameters

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

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)) !== false) {
      yield ['name' => $row[0], 'email' => $row[1]];
  }
  fclose($handle);
}

UserRepository::instance()->insertBatch(readRows('/tmp/import.csv'));

update()

Changes the rows matching a condition.

Signature

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

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

Result

1

delete()

Removes the rows matching a condition.

Signature

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

$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): 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 — which columns to update on a conflict. null (the default) means update nothing: the row stays as it was and the insert simply does not happen.

Returns

The row’s identifier, as insert() does.

Errors

RepositoryException — on a database error.

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'],
);

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

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): void

Parameters

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

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

$updateColumns — the columns to update; null means “update nothing”.

Returns

Nothing.

Example

UserRepository::instance()->upsertBatch(
  readRows('/tmp/import.csv'),
  conflictColumns: ['email'],
  updateColumns:   ['name'],
);

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.

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 = []): 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

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

Result

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

getSql()

Returns the accumulated query state — its parts and its binds.

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): mixed

Parameters

$param — the name of the part you want. null (the default) returns the whole state.

Returns

The state array, or the value of the requested part.

Example

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

$state = $repository->getSql();          // the whole state
$binds = $repository->getSql('binds');   // the binds only

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

public function sqlPartsCount(): int

Returns

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 zero

cleanCache()

Discards the accumulated query state.

The reading and writing methods call it themselves after executing, 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): void

Parameters

$param — the name of the part to discard. null (the default) discards everything.

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(): CDO

Returns

The connection object.

Example

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

Details 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 set

Errors

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) {
  $user = UserRepository::instance()->insert(['id' => $id, 'name' => 'Guest']);
}

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