README

Lightweight base repository for Doctrine DBAL.

• RepositoryInterface<TModel>: generic contract for CRUD, aggregates, pagination, and transactions.
• BaseRepository<TModel>: ready-to-extend base with criteria parsing, sorting, mapping.
• BaseSoftDeletableRepository<TModel>: optional base class for soft delete functionality with fluent API.

Installation

composer require solophp/base-repository

Requirements

• PHP 8.2+
• Doctrine DBAL (doctrine/dbal ^4.3)

Constructor

__construct(
    protected Connection $connection,
    string $modelClass,
    string $table,
    ?string $tableAlias = null,
    string $mapperMethod = 'fromArray'
)

Configurable Properties

Criteria Syntax

Retrieval Methods

Mutation Methods

Existence and Aggregates

BaseSoftDeletableRepository

Optional base class for repositories requiring soft delete functionality. When extended, it modifies default behavior and adds soft delete methods.

Usage

use Solo\BaseRepository\BaseSoftDeletableRepository;

final class UserRepository extends BaseSoftDeletableRepository
{
    public function __construct(Connection $connection)
    {
        parent::__construct($connection, User::class, 'users');
    }
}

Fluent API

Examples

// Safe behavior by default (only active records)
$users = $repo->getAll();                    // Only active records
$repo->deleteById(1);                        // Soft delete (sets deleted_at)
$repo->deleteBy(['status' => 'inactive']);   // Soft delete (sets deleted_at)

// Include soft-deleted records
$allUsers = $repo->withTrashed()->getAll();      // All including soft-deleted
$allInactive = $repo->withTrashed()->getBy(['status' => 'inactive']); // All inactive including soft-deleted

// Hard delete (physical removal)
$repo->forceDeleteById(1);                   // Physical deletion
$repo->forceDeleteBy(['status' => 'test']);  // Physical deletion

// Restore soft-deleted records
$repo->restoreById(1);                       // Sets deleted_at = NULL

// API filtering still works
$deleted = $repo->getBy(['deleted' => 'only']);     // Only soft-deleted
$all = $repo->getBy(['deleted' => 'with']);         // All including soft-deleted
$active = $repo->getBy(['deleted' => 'without']);   // Only active (default behavior)

Transactions

Example

use Solo\BaseRepository\BaseRepository;
use Doctrine\DBAL\Connection;

final class UserRepository extends BaseRepository
{
    public function __construct(Connection $connection)
    {
        parent::__construct($connection, User::class, 'users');
    }
}

// Usage

// 1) Basic filtering and sorting with pagination
$users = $repo->getBy(
    ['status' => 'active', 'age' => ['>', 18]],
    ['created_at' => 'DESC'],
    20,  // perPage
    1    // page
);

// 2) Criteria variants
$byNull = $repo->getBy(['deleted_at' => null]);
$byIn = $repo->getBy(['id' => [1, 5, 7]]);
$byOp = $repo->getBy(['score' => ['>=', 90]]);

// 3) Search queries (LIKE)
$filtered = $repo->getBy([
    'search' => ['name' => 'john', 'email' => 'example.com']
]);

// 4) Deleted filter (soft-deletes)
$withDeleted = $repo->getBy(['deleted' => 'with']);
$onlyDeleted = $repo->getBy(['deleted' => 'only']);

// 5) Pagination implementation in controller
$criteria = ['status' => 'active'];
$orderBy = ['id' => 'ASC'];
$perPage = 10; $page = 2;

$items = $repo->getBy($criteria, $orderBy, $perPage, $page);
$total = $repo->countBy($criteria);
// Build paginated response: ['items' => $items, 'total' => $total, 'page' => $page, 'perPage' => $perPage]


// 7) Existence and counts
$exists = $repo->existsBy(['email' => 'john@example.com']);
$totalActive = $repo->countBy(['status' => 'active']);

// 8) Inserts and updates
$newId = $repo->insert(['name' => 'John', 'email' => 'john@example.com']);
$affected = $repo->insertBatch([
    ['name' => 'Jane', 'email' => 'jane@example.com'],
    ['name' => 'Bob',  'email' => 'bob@example.com'],
]);
$repo->update($newId, ['name' => 'John Doe']);
$repo->updateBy(['status' => 'pending'], ['status' => 'active']);

// 9) Basic delete operations
$repo->deleteById($newId);               // hard delete (or soft delete if using BaseSoftDeletableRepository)

// 10) Transactions
$repo->withTransaction(function (UserRepository $r) {
    $id = $r->insert(['name' => 'Temp', 'email' => 'temp@example.com']);
    $r->update($id, ['name' => 'Temp Updated']);
    // throw new RuntimeException('rollback'); // Uncomment to rollback
});

Notes

• Validate fields in criteria/orderBy against a whitelist if user-provided.
• Soft-delete timestamp uses {NOW()} (raw). Adapt if you need database-agnostic timestamps.

Extending repositories

You can extend BaseRepository to add domain-specific behavior, scopes, or stricter validation. Below are common patterns.

1. Add domain-specific methods using table() and builder chaining:

use Solo\BaseRepository\BaseRepository;

final class UserRepository extends BaseRepository
{
    public function __construct(Connection $connection)
    {
        parent::__construct($connection, 'users', User::class, 'fromArray');
    }

    // Use table() then chain builder methods and map results
    /** @return list<User> */
    public function findTopActive(int $limit = 10): array
    {
        $rows = $this->table()
            ->andWhere('status = :status')
            ->setParameter('status', 'active')
            ->orderBy('score', 'DESC')
            ->setMaxResults($limit)
            ->executeQuery()
            ->fetchAllAssociative();

        return array_map(fn(array $r) => $this->mapRowToModel($r), $rows);
    }

    // With JOIN and custom select
    /** @return list<User> */
    public function withProfiles(array $userIds): array
    {
        $qb = $this->table()
            ->select('users.*', 'profiles.score as profile_score')
            ->join('users', 'profiles', 'profiles', 'profiles.user_id = users.id')
            ->andWhere($qb->expr()->in('users.id', ':userIds'))
            ->setParameter('userIds', $userIds, \Doctrine\DBAL\ArrayParameterType::STRING);
        
        $rows = $qb->executeQuery()->fetchAllAssociative();

        return array_map(fn(array $r) => $this->mapRowToModel($r), $rows);
    }

    // Convenience wrappers for readability
    public function findActiveByEmail(string $email): ?User
    {
        return $this->getFirstBy(['status' => 'active', 'email' => $email]);
    }
}

1. Extend criteria syntax (e.g., LIKE, BETWEEN) by overriding applyCriteria:

use Doctrine\DBAL\Query\QueryBuilder;

protected function applyCriteria(QueryBuilder $qb, array $criteria): QueryBuilder
{
    $handled = [];

    foreach ($criteria as $field => $value) {
        // LIKE: ['name' => ['like', '%john%']]
        if (is_array($value) && ($value[0] ?? null) === 'like') {
            $qb->andWhere("{$field} LIKE :{$field}")
               ->setParameter($field, $value[1] ?? '');
            $handled[$field] = true;
            continue;
        }

        // BETWEEN: ['created_at' => ['between', ['2024-01-01','2024-12-31']]]
        if (is_array($value) && ($value[0] ?? null) === 'between' && is_array($value[1] ?? null)) {
            [$from, $to] = $value[1];
            $qb->andWhere("{$field} BETWEEN :{$field}_from AND :{$field}_to")
               ->setParameter($field . '_from', $from)
               ->setParameter($field . '_to', $to);
            $handled[$field] = true;
            continue;
        }
    }

    // Delegate remaining keys to the base implementation
    $remaining = array_diff_key($criteria, $handled);
    return parent::applyCriteria($qb, $remaining);
}

1. Whitelist sortable fields by overriding sorting:

protected array $orderWhitelist = ['id', 'created_at', 'name'];

protected function applyOrderBy(QueryBuilder $qb, array $orderBy): void
{
    foreach ($orderBy as $field => $direction) {
        if (!in_array($field, $this->orderWhitelist, true)) {
            continue; // ignore non-whitelisted fields
        }
        $dir = strtoupper($direction) === 'DESC' ? 'DESC' : 'ASC';
        $qb->orderBy($field, $dir);
    }
}

1. Harden identifier safety or add field whitelists by overriding assertSafeIdentifier or validating inputs before calling aggregates/order-by.

License

This library is released under the MIT License. See the LICENSE file for details.