Advanced Repository Patterns

This guide covers advanced features of the Repository layer, including complex queries, UUID handling, transactions, and custom query methods.

Overview
BaseRepository Features
Advanced Querying
UUID Handling
Custom Query Methods
Transaction Management
Performance Optimization
ActiveRecord Pattern

Overview

The BaseRepository class provides a foundation for data access with support for:

CRUD operations
Pagination and filtering
UUID generation and conversion
Query builder integration
Transaction support
Custom query methods

Location: ByJG\Gluo\Repository\BaseRepository (byjg/gluo-core)

BaseRepository Features

Basic Methods

All repositories extending BaseRepository have these methods:

// Get single entity by ID
$project = $repository->get($id);

// Get the underlying ByJG Repository
$ormRepository = $repository->getRepository();

// Get the Mapper
$mapper = $repository->getMapper();

// Get read executor
$executor = $repository->getExecutor();

// Get write executor (for master/slave setups)
$executorWrite = $repository->getExecutorWrite();

// Create empty model instance
$model = $repository->model();

Example Repository

<?php

namespace RestReferenceArchitecture\Repository;

use ByJG\AnyDataset\Db\DatabaseExecutor;
use ByJG\MicroOrm\Repository;
use RestReferenceArchitecture\Model\Project;

class ProjectRepository extends BaseRepository
{
    public function __construct(DatabaseExecutor $executor)
    {
        $this->repository = new Repository($executor, Project::class);
    }
}

Filtering and Ordering

BaseRepository::list() and listQuery() both accept orderBy and filter parameters. Both end up in the SQL as written, so they are for values your code builds, never for text that comes from a request.

`orderBy`

A string or array of ORDER BY clauses:

// Single column
$repository->list(page: 0, size: 20, orderBy: 'name ASC');

// Multiple columns
$repository->list(page: 0, size: 20, orderBy: ['created_at DESC', 'name ASC']);

`filter`

An array of [$condition, $params] tuples that are appended as WHERE clauses:

$filter = [
    ['status = :status', ['status' => 'active']],
    ['created_at >= :since', ['since' => '2024-01-01']],
];

$repository->list(page: 0, size: 20, filter: $filter);

Ordering from a query string

The generated list endpoints accept ?orderBy=, and ProjectController shows how:

#[OA\Parameter(name: "orderBy", in: "query", required: false, schema: new OA\Schema(type: "string"))]
public function listProject(HttpResponse $response, HttpRequest $request): void
{
    $result = $this->projectService->list(
        (int)($request->queryString('page') ?? 0),
        (int)($request->queryString('size') ?? 50),
        OrderBy::fromQueryString($request->queryString('orderBy'), Project::class)
    );
    $response->write($result);
}

ByJG\Gluo\Util\OrderBy::fromQueryString() is what makes the client's value safe to use. It accepts one or more fields separated by commas, each optionally followed by asc or desc, and returns the ORDER BY terms with the column names the model declares:

Request	Result
`?orderBy=name`	`['name']`
`?orderBy=createdAt desc,name`	`['created_at DESC', 'name']`
`?orderBy=password`	400: unknown field
`?orderBy=name; drop table project`	400: invalid

A field can be named by its property (createdAt) or by its column (created_at), and only fields of the model are accepted. Nothing from the request reaches the SQL.

An ActiveRecord controller does the same with a query:

$query = Query::getInstance()
    ->table(Note::tableName())
    ->orderBy(OrderBy::fromQueryString($request->queryString('orderBy'), Note::class))
    ->limit($page * $size, $size);

$response->write(Note::query($query));

warning

There is no equivalent for filter. The generated endpoints do not accept one, because a filter needs a format of its own. To filter by a request value, add a named query parameter to the endpoint and build the condition yourself, with the value bound as a parameter: ['status = :status', ['status' => $request->queryString('status')]].

Advanced Querying

The listQuery() Method

The listQuery() method provides powerful filtering, pagination, and ordering capabilities:

Location: ByJG\Gluo\Repository\BaseRepository (byjg/gluo-core)

public function listQuery(
    $tableName = null,
    $fields = [],
    $page = 0,
    $size = 20,
    $orderBy = null,
    $filter = null
): Query

Parameters

tableName: Override the default table (useful for joins)
fields: Array of fields to select (default: all fields)
page: Page number (0-indexed)
size: Results per page
orderBy: String or array of ORDER BY clauses
filter: Array of WHERE conditions

Example: Basic Pagination

$query = $repository->listQuery(
    page: 0,      // First page
    size: 50,     // 50 results
    orderBy: 'name ASC'
);

$results = $repository->getRepository()->getByQuery($query);

Example: Multiple Order By

$query = $repository->listQuery(
    page: 2,
    size: 25,
    orderBy: ['created_at DESC', 'name ASC']
);

Example: Filtering

// Filter format: [field_expression, value]
$filter = [
    ['name = [[name]]', ['name' => 'John']],
    ['age > [[min_age]]', ['min_age' => 18]],
    ['status = [[status]]', ['status' => 'active']]
];

$query = $repository->listQuery(
    page: 0,
    size: 20,
    orderBy: 'created_at DESC',
    filter: $filter
);

Example: Custom Fields

// Only select specific fields
$query = $repository->listQuery(
    fields: ['id', 'name', 'email'],
    page: 0,
    size: 100
);

The listGeneric() Method

Query arbitrary tables dynamically without a mapper:

Location: ByJG\Gluo\Repository\BaseRepository (byjg/gluo-core)

public function listGeneric(
    $tableName,
    $fields = [],
    $page = 0,
    $size = 20,
    $orderBy = null,
    $filter = null
)

// Query a join table or related table
$userRoles = $repository->listGeneric(
    tableName: 'user_roles',
    fields: ['user_id', 'role_id', 'granted_at'],
    filter: [
        ['user_id = [[uid]]', ['uid' => $userId]]
    ],
    orderBy: 'granted_at DESC'
);

// Returns array of associative arrays (not model objects)
foreach ($userRoles as $row) {
    echo $row['role_id'];
}

Example: Aggregation Query

// Count records by status
$statusCounts = $repository->listGeneric(
    tableName: 'orders',
    fields: ['status', 'COUNT(*) as count'],
    filter: [
        ['created_at > [[date]]', ['date' => '2024-01-01']]
    ],
    orderBy: 'count DESC'
);

Custom Query with getByQuery()

Build completely custom queries using the Query builder:

use ByJG\MicroOrm\Query;

// Complex query with joins
$query = Query::getInstance()
    ->table('project', 'd')
    ->join('user', 'u', 'd.user_id = u.id')
    ->fields(['d.*', 'u.name as user_name'])
    ->where('d.status = :status', ['status' => 'active'])
    ->where('u.role = :role', ['role' => 'admin'])
    ->orderBy(['d.created_at DESC'])
    ->limit(0, 50);

$results = $repository->getByQuery($query);

Advanced Filter Examples

IN Clause

$filter = [
    ['status IN (:statuses)', ['statuses' => ['active', 'pending']]]
];

LIKE Search

$filter = [
    ['name LIKE :search', ['search' => '%john%']]
];

Multiple Conditions with OR

use ByJG\MicroOrm\Query;

$query = Query::getInstance()
    ->table($repository->getMapper()->getTable())
    ->where('(status = :status1 OR status = :status2)', [
        'status1' => 'active',
        'status2' => 'pending'
    ])
    ->where('created_at > :date', ['date' => '2024-01-01']);

$results = $repository->getByQuery($query);

Date Range

$filter = [
    ['created_at >= :start', ['start' => '2024-01-01']],
    ['created_at <= :end', ['end' => '2024-12-31']]
];

UUID Handling

Binary UUIDs offer a 16-byte storage footprint without losing usability. The reference architecture wires everything for you.

Generate a UUID on Demand

$uuid = BaseRepository::getUuid(); // "550E8400-E29B-41D4-A716-446655440000"

Use this helper when you need a UUID string outside of ORM inserts (e.g., building fixtures).

Attribute-Based Binary Columns

Use TableUuidPKAttribute and FieldUuidAttribute to keep repositories tiny while persisting binary UUIDs:

use ByJG\MicroOrm\Attributes\FieldAttribute;
use ByJG\MicroOrm\Attributes\FieldUuidAttribute;
use ByJG\MicroOrm\Attributes\TableUuidPKAttribute;
use ByJG\MicroOrm\Literal\HexUuidLiteral;
use ByJG\MicroOrm\Literal\Literal;

#[TableUuidPKAttribute("task")]
class Task
{
    #[FieldUuidAttribute(primaryKey: true)]
    protected string|HexUuidLiteral|null $id = null;

    #[FieldAttribute(fieldName: "uuid", syncWithDb: false)]
    protected ?string $uuid = null;

    public function setId(string|Literal|null $id): static
    {
        if ($id instanceof Literal) {
            $id = new HexUuidLiteral($id);
        }
        $this->id = $id;
        return $this;
    }
}

TableUuidPKAttribute generates a UUID (version 7, in PHP) automatically when inserting rows, unless the model already has one, while FieldUuidAttribute handles binary ⇄ string conversion on selects/updates.

The repository stays simple:

use ByJG\AnyDataset\Db\DatabaseExecutor;
use ByJG\MicroOrm\Repository;

class TaskRepository extends BaseRepository
{
    public function __construct(DatabaseExecutor $executor)
    {
        $this->repository = new Repository($executor, Task::class);
    }
}

Querying by UUID

BaseRepository::get() passes the id to micro-orm's Repository::get(), which applies the primary key's update function (FieldUuidAttribute turns a UUID string into a HexUuidLiteral), so passing a UUID string “just works”:

$entity = $repository->get('550e8400-e29b-41d4-a716-446655440000');

When you need to build queries manually, wrap UUIDs with HexUuidLiteral::create() to generate the correct binary literal for the database driver.

Custom Query Methods

Add domain-specific query methods to your repositories or models.

In Repository Pattern

class ProjectRepository extends BaseRepository
{
    public function findByStatus(string $status): array
    {
        $query = Query::getInstance()
            ->table($this->repository->getMapper()->getTable())
            ->where('status = :status', ['status' => $status])
            ->orderBy(['created_at DESC']);

        return $this->repository->getByQuery($query);
    }

    public function findByDateRange(string $start, string $end): array
    {
        $query = Query::getInstance()
            ->table($this->repository->getMapper()->getTable())
            ->where('created_at >= :start', ['start' => $start])
            ->where('created_at <= :end', ['end' => $end]);

        return $this->repository->getByQuery($query);
    }

    public function countByStatus(string $status): int
    {
        $result = $this->listGeneric(
            tableName: $this->repository->getMapper()->getTable(),
            fields: ['COUNT(*) as count'],
            filter: [
                ['status = :status', ['status' => $status]]
            ]
        );

        return (int) $result[0]['count'];
    }

    public function findRecent(int $limit = 10): array
    {
        $query = Query::getInstance()
            ->table($this->repository->getMapper()->getTable())
            ->orderBy(['created_at DESC'])
            ->limit(0, $limit);

        return $this->repository->getByQuery($query);
    }
}

In ActiveRecord Pattern

Add static query methods directly to your model:

Location: src/Model/Note.php

class Note
{
    use ActiveRecord;

    /**
     * Find records by task id (the binary(16) foreign key to task)
     *
     * @param mixed $taskId
     * @return null|Note[]
     */
    public static function getByTaskId($taskId): ?array
    {
        $query = Query::getInstance()
            ->table(self::$repository->getMapper()->getTable(), 'alias')
            ->where('alias.task_id = :value', ['value' => $taskId]);

        return self::query($query);
    }

    /**
     * Find recent notes
     *
     * @return null|Note[]
     */
    public static function findRecent(): ?array
    {
        $query = Query::getInstance()
            ->table(self::$repository->getMapper()->getTable())
            ->orderBy(['created_at DESC']);

        return self::query($query);
    }

    /**
     * Get single record by body
     *
     * @param string $body
     * @return Note|null
     */
    public static function getByBody(string $body): ?Note
    {
        $query = Query::getInstance()
            ->table(self::$repository->getMapper()->getTable())
            ->where('body = :body', ['body' => $body]);

        $results = self::query($query);
        return $results[0] ?? null;
    }
}

Usage

// Call static methods directly on the model
$notes = Note::getByTaskId('550e8400-e29b-41d4-a716-446655440000');
$recent = Note::findRecent();
$note = Note::getByBody('Remember to follow up');

Transaction Management

Using the Executor

$executor = $repository->getExecutorWrite();

try {
    $executor->beginTransaction();

    // Perform multiple operations
    $repository->save($model1);
    $repository->save($model2);
    $repository->save($model3);

    $executor->commitTransaction();
} catch (\Exception $e) {
    $executor->rollbackTransaction();
    throw $e;
}

Multi-Repository Transactions

use ByJG\Config\Config;

$projectRepo = Config::get(ProjectRepository::class);
$userRepo = Config::get(UserRepository::class);

// Ensure both use the same executor
$executor = $projectRepo->getExecutorWrite();

try {
    $executor->beginTransaction();

    $user = $userRepo->save($userData);
    $project = $projectRepo->save($projectData);

    // Link them
    $project->setUserId($user->getId());
    $projectRepo->save($project);

    $executor->commitTransaction();
} catch (\Exception $e) {
    $executor->rollbackTransaction();
    throw $e;
}

Transaction Wrapper

Create a helper for cleaner transaction handling:

trait TransactionHelper
{
    protected function transaction(callable $callback)
    {
        $executor = $this->getExecutorWrite();

        try {
            $executor->beginTransaction();
            $result = $callback($executor);
            $executor->commitTransaction();
            return $result;
        } catch (\Exception $e) {
            $executor->rollbackTransaction();
            throw $e;
        }
    }
}

// Usage in repository
class ProjectRepository extends BaseRepository
{
    use TransactionHelper;

    public function createWithRelated($projectData, $relatedData)
    {
        return $this->transaction(function($executor) use ($projectData, $relatedData) {
            $project = $this->save($projectData);
            // Save related data
            return $project;
        });
    }
}

Performance Optimization

Eager Loading with Joins

public function listWithUser(): array
{
    $query = Query::getInstance()
        ->table($this->repository->getMapper()->getTable(), 'd')
        ->join('user', 'u', 'd.user_id = u.id')
        ->fields([
            'd.*',
            'u.name as user_name',
            'u.email as user_email'
        ]);

    return $this->repository->getByQuery($query);
}

Selective Field Loading

// Only load fields you need
$query = Query::getInstance()
    ->table($this->repository->getMapper()->getTable())
    ->fields(['id', 'name', 'status'])  // Don't load large text fields
    ->where('status = :status', ['status' => 'active']);

Batch Operations

public function bulkInsert(array $models): void
{
    $executor = $this->getExecutorWrite();

    try {
        $executor->beginTransaction();

        foreach ($models as $model) {
            $this->repository->save($model);
        }

        $executor->commitTransaction();
    } catch (\Exception $e) {
        $executor->rollbackTransaction();
        throw $e;
    }
}

Caching Results

use Psr\SimpleCache\CacheInterface;

class ProjectRepository extends BaseRepository
{
    protected CacheInterface $cache;

    public function findByIdCached($id)
    {
        $key = "project:{$id}";

        if ($this->cache->has($key)) {
            return $this->cache->get($key);
        }

        $project = $this->get($id);
        $this->cache->set($key, $project, 3600); // 1 hour

        return $project;
    }

    public function save($model, $updateConstraint = null): mixed
    {
        $result = parent::save($model, $updateConstraint);

        // Invalidate cache
        $key = "project:{$result->getId()}";
        $this->cache->delete($key);

        return $result;
    }
}

ActiveRecord Pattern

The ActiveRecord pattern provides an alternative to the Repository pattern where models handle their own persistence.

Basic ActiveRecord Usage

use RestReferenceArchitecture\Model\Note;

// Create
$note = new Note();
$note->setTaskUuid('550e8400-e29b-41d4-a716-446655440000');
$note->setBody('Test');
$note->save();

// Read
$note = Note::get(1);

// Update
$note->setBody('Updated');
$note->save();

// Delete
$note->delete();

// Query
$results = Note::getByTaskId('550e8400-e29b-41d4-a716-446655440000');

Adding Custom Methods

See Custom Query Methods section above.

When to Use ActiveRecord vs Repository

Use ActiveRecord when:

Simple CRUD operations dominate
Models are independent
Rapid development is priority
Small to medium projects

Use Repository when:

Complex business logic
Multiple data sources
Need for testing isolation
Large enterprise applications

See Architecture Decision Guide for detailed comparison.

Advanced Examples

Full-Text Search

public function search(string $term): array
{
    $query = Query::getInstance()
        ->table($this->repository->getMapper()->getTable())
        ->where(
            'MATCH(name, description) AGAINST (:term IN BOOLEAN MODE)',
            ['term' => $term . '*']
        );

    return $this->repository->getByQuery($query);
}

Hierarchical Queries

public function getWithChildren(int $parentId): array
{
    $query = Query::getInstance()
        ->table($this->repository->getMapper()->getTable(), 'parent')
        ->join(
            $this->repository->getMapper()->getTable(),
            'child',
            'parent.id = child.parent_id'
        )
        ->where('parent.id = :id', ['id' => $parentId])
        ->fields(['parent.*', 'child.id as child_id', 'child.name as child_name']);

    return $this->repository->getByQuery($query);
}

Soft Deletes

public function softDelete($model): void
{
    $model->setDeletedAt(date('Y-m-d H:i:s'));
    $this->repository->save($model);
}

public function listActive($page = 0, $size = 20): array
{
    $query = $this->listQuery(
        page: $page,
        size: $size,
        filter: [
            ['deleted_at IS NULL', []]
        ]
    );

    return $this->repository->getByQuery($query);
}

Best Practices

Use Type Hints: Always type-hint return values and parameters
Document Complex Queries: Add PHPDoc comments explaining query logic
Cache Expensive Queries: Use PSR-16 cache for slow queries
Index Database Fields: Ensure WHERE/ORDER BY fields are indexed
Validate Before Save: Check constraints before database operations
Use Transactions: Group related operations in transactions
Pagination: Always paginate list queries
Avoid N+1 Queries: Use joins for related data

Table of Contents​

Overview​

BaseRepository Features​

Basic Methods​

Example Repository​

Filtering and Ordering​

orderBy​

filter​

Ordering from a query string​

Advanced Querying​

The listQuery() Method​

Parameters​

Example: Basic Pagination​

Example: Multiple Order By​

Example: Filtering​

Example: Custom Fields​

The listGeneric() Method​

Example: Query Related Table​

Example: Aggregation Query​

Custom Query with getByQuery()​

Advanced Filter Examples​

IN Clause​

LIKE Search​

Multiple Conditions with OR​

Date Range​

UUID Handling​

Generate a UUID on Demand​

Attribute-Based Binary Columns​

Querying by UUID​

Custom Query Methods​

In Repository Pattern​

In ActiveRecord Pattern​

Usage​

Transaction Management​

Using the Executor​

Multi-Repository Transactions​

Transaction Wrapper​

Performance Optimization​

Eager Loading with Joins​

Selective Field Loading​

Batch Operations​

Caching Results​

ActiveRecord Pattern​

Basic ActiveRecord Usage​

Adding Custom Methods​

When to Use ActiveRecord vs Repository​

Advanced Examples​

Full-Text Search​

Hierarchical Queries​

Soft Deletes​

Best Practices​

Related Documentation​

Table of Contents

Overview

BaseRepository Features

Basic Methods

Example Repository

Filtering and Ordering

`orderBy`

`filter`

Ordering from a query string

Advanced Querying

The listQuery() Method

Parameters

Example: Basic Pagination

Example: Multiple Order By

Example: Filtering

Example: Custom Fields

The listGeneric() Method

Example: Query Related Table

Example: Aggregation Query

Custom Query with getByQuery()

Advanced Filter Examples

IN Clause

LIKE Search

Multiple Conditions with OR

Date Range

UUID Handling

Generate a UUID on Demand

Attribute-Based Binary Columns

Querying by UUID

Custom Query Methods

In Repository Pattern

In ActiveRecord Pattern

Usage

Transaction Management

Using the Executor

Multi-Repository Transactions

Transaction Wrapper

Performance Optimization

Eager Loading with Joins

Selective Field Loading

Batch Operations

Caching Results

ActiveRecord Pattern

Basic ActiveRecord Usage

Adding Custom Methods

When to Use ActiveRecord vs Repository

Advanced Examples

Full-Text Search

Hierarchical Queries

Soft Deletes

Best Practices

Related Documentation