• Global and Scoped Ordering: Easily manage display order across an entire table or within a specific scope.
• Transaction Ownership: Handles its own transactions and locks the necessary scope reliably.
• SQL Identifier Validation: Ensures table and column configurations are safe and properly quoted.
• Soft-Delete Filtering: Optional support for ignoring soft-deleted rows in ordering calculations.
• Scope Isolation: Ensures only the affected range within the configured scope is updated.
• PDO Pagination: Deterministic offset pagination with strict normalization, bounds checking, and safe whitelist-based sorting.

Runtime requirements:

• PHP >= 8.2
• ext-pdo
• maatify/exceptions ^1.0

Database behavior:

• The package behavior is designed and verified against MySQL.

composer require maatify/persistence

use Maatify\Persistence\Pdo\Ordering\ScopedOrderingConfig;
use Maatify\Persistence\Pdo\Ordering\ScopedOrderingManager;

// 1. Configure the ordering behavior for a table
$config = new ScopedOrderingConfig(
    table: 'maa_shipping_rates',
    scopeColumn: 'method_id', // Use null for global ordering
    idColumn: 'id',
    orderColumn: 'display_order',
    deletedAtColumn: 'deleted_at', // Use null if soft-deletes are not used
);

$ordering = new ScopedOrderingManager();

// 2. Get the next position for a new insert
$nextPosition = $ordering->getNextPosition(
    pdo: $pdo,
    config: $config,
    scopeValue: 2, // Use null for global ordering
);

// 3. Move an existing row within its scope
$success = $ordering->moveWithinScope(
    pdo: $pdo,
    config: $config,
    scopeValue: 2, // Use null for global ordering
    id: 15,
    newOrder: 4,
);

use Maatify\Persistence\Pdo\Pagination\PaginationConfig;
use Maatify\Persistence\Pdo\Pagination\PageRequest;
use Maatify\Persistence\Pdo\Pagination\PdoPaginationQueryDescriptor;
use Maatify\Persistence\Pdo\Pagination\PdoPaginator;
use Maatify\Persistence\Pdo\Pagination\SortWhitelist;
use Maatify\Persistence\Pdo\Pagination\SortDirectionEnum;

$config = new PaginationConfig(
    defaultPerPage: 10,
    maxPerPage: 100,
    minPerPage: 1,
    sortWhitelist: new SortWhitelist([
        'id' => 'id',
        'created' => 'created_at',
        'name' => 'user_name',
    ]),
    defaultSortBy: 'created',
    defaultSortDirection: SortDirectionEnum::DESC,
    tieBreakerSortBy: 'id',
    tieBreakerDirection: SortDirectionEnum::DESC
);

$query = new PdoPaginationQueryDescriptor(
    totalSql: 'SELECT COUNT(*) FROM users',
    totalParams: [],
    filteredCountSql: 'SELECT COUNT(*) FROM users WHERE status = :status',
    filteredCountParams: ['status' => 'active'],
    dataSql: 'SELECT id, user_name, created_at FROM users WHERE status = :status',
    dataParams: ['status' => 'active']
);

$request = new PageRequest(page: 2, perPage: 15, sortBy: 'name', sortDirection: 'ASC');

$paginator = new PdoPaginator();
$result = $paginator->paginate(
    pdo: $pdo,
    query: $query,
    request: $request,
    config: $config,
    mapper: fn(array $row) => (object) $row
);

The package currently provides the following public classes for PDO ordering and pagination:

Maatify\Persistence\Pdo\Ordering\ScopedOrderingConfig;
Maatify\Persistence\Pdo\Ordering\ScopedOrderingManager;

Maatify\Persistence\Pdo\Pagination\PageRequest;
Maatify\Persistence\Pdo\Pagination\SortDirectionEnum;
Maatify\Persistence\Pdo\Pagination\SortWhitelist;
Maatify\Persistence\Pdo\Pagination\PaginationConfig;
Maatify\Persistence\Pdo\Pagination\PdoPaginationQueryDescriptor;
Maatify\Persistence\Pdo\Pagination\PageResult;
Maatify\Persistence\Pdo\Pagination\PdoPaginator;

// Exceptions
Maatify\Persistence\Exception\PersistenceException;
Maatify\Persistence\Exception\InvalidOrderingConfigurationException;
Maatify\Persistence\Exception\InvalidOrderingOperationException;
Maatify\Persistence\Exception\OrderingTransactionException;
Maatify\Persistence\Exception\InvalidPaginationConfigurationException;
Maatify\Persistence\Exception\InvalidPaginationQueryException;
Maatify\Persistence\Exception\PaginationExecutionException;

getNextPosition():

• Does not start a transaction.
• Does not lock the applicable scope.
• For concurrent inserts, the host application must provide the transaction and locking mechanism required to serialize position allocation.

moveWithinScope():

• Rejects inconsistent scope usage.
• Rejects id <= 0.
• Rejects newOrder <= 0.
• Rejects caller-owned active PDO transactions.
• Owns its own transaction.
• Locks the applicable active scope using SELECT ... FOR UPDATE.
• Reads the current order from the database within the same transaction.
• Does not trust a current order provided by the caller.
• Returns false if the target row is missing.
• Clamps values higher than the maximum position to the maximum available position.
• Returns true if the movement is a no-op (already at the requested position).
• Moves only the affected range.
• Does not globally normalize pre-existing gaps.
• Rolls back and returns false if the final target update fails.
• Rolls back on any Throwable after starting the transaction.
• Rethrows the original Throwable without arbitrary wrapping.

rowExistsInScope():

• Returns false for id <= 0.
• Returns false if the row is not found within the configured scope.
• Treats soft-deleted rows as non-existent if deletedAtColumn is configured.
• Throws InvalidOrderingOperationException on invalid scope usage.
• External PDO errors propagate unmodified.

PDO Pagination:

• Normalizes page and per-page limits strictly.
• Uses safe whitelist-based sorting.
• Host application owns SQL, scopes, mapping, and filters.
• Package handles count queries and offset calculation.
• Does not alter or require active PDO transactions.

• Standalone Composer package.
• Framework-agnostic.
• Host-agnostic.
• PDO-based.
• No ORM.
• No framework bindings.
• No HTTP endpoints, UI, controllers, or routes.
• No host table ownership.
• No generic application repository abstraction.
• The host provides the PDO connection.
• Trusted SQL identifiers.
• Runtime values use prepared statements.

All package-defined exceptions implement the marker interface Maatify\Persistence\Exception\PersistenceException. However, this interface is not a catch-all. PDOException or other external Throwables may propagate without wrapping and require a separate catch or an outer Throwable boundary if handling is needed.

The ScopedOrderingConfig validates and quotes all configured table and column identifiers. However, these identifiers must still be provided as trusted application configurations (e.g., constants), never as raw user input. All actual runtime values are safely passed using PDO prepared statements.

For a comprehensive guide, please refer to the main technical reference:

• Persistence Package Reference

Other important documentation:

• Changelog
• Security Policy
• Contributing Guide
• Code of Conduct
• Architecture Decision Records
• Package Building Standard
• CI Workflow Standard
• Library Presentation Standard

• PHP 8.2–8.5 verification in CI.
• PHPStan Level Max.
• Unit, Regression, and MySQL Integration tests.
• Lowest dependencies verification.
• Stable CI Gate.

Integration testing:

• Real MySQL is required for Integration tests.
• SQLite is not an Integration substitute.
• MySQL 8.4.10 is the currently verified CI baseline.

composer validate --strict
composer analyse
composer test:unit
composer test:regression
vendor/bin/php-cs-fixer fix --dry-run --diff

composer test:integration and composer test require a real MySQL database. SQLite is explicitly not an integration substitute.

Set the following environment variables for Integration tests:

• PERSISTENCE_TEST_MYSQL_DSN
• PERSISTENCE_TEST_MYSQL_USER
• PERSISTENCE_TEST_MYSQL_PASSWORD

This project is licensed under the MIT License - see the LICENSE file for details.

Engineered by Mohamed Abdulalim (@megyptm)
Backend Lead & Technical Architect
https://www.maatify.dev