Ray.MediaQuery

Ray.MediaQuery generates and injects query execution objects from database query interfaces.

• Clarifies the boundary between domain layer and infrastructure layer.
• Reduces boilerplate code.
• Since it’s independent of the actual external media, storage can be changed later. Enables easy parallel development and stub creation.

Installation

composer require ray/media-query

Note: For the same interface-driven approach over Web APIs, see ray/web-query.

Usage

Define an interface for database access.

Interface Definition

Specify the SQL ID with the #[DbQuery] attribute.

use Ray\MediaQuery\Annotation\DbQuery;

interface TodoAddInterface
{
    #[DbQuery('todo_add')]
    public function add(string $title): void;
}

Module Configuration

Specify SQL directory and interface directory with MediaQuerySqlModule.

use Ray\AuraSqlModule\AuraSqlModule;
use Ray\MediaQuery\MediaQuerySqlModule;

protected function configure(): void
{
    $this->install(
        new MediaQuerySqlModule(
            interfaceDir: '/path/to/query/interfaces',
            sqlDir: '/path/to/sql'
        )
    );
    $this->install(new AuraSqlModule(
        'mysql:host=localhost;dbname=test',
        'username',
        'password'
    ));
}

MediaQuerySqlModule requires AuraSqlModule to be installed.

Injection

Objects are generated directly from interfaces and injected. No implementation class coding is required.

class Todo
{
    public function __construct(
        private TodoAddInterface $todoAdd
    ) {}

    public function add(string $title): void
    {
        $this->todoAdd->add($title);
    }
}

DbQuery

SQL execution is mapped to methods, binding the SQL specified by ID with method arguments for execution. For example, with ID todo_item, it executes todo_item.sql SQL statement bound with ['id' => $id].

• Prepare SQL files in the $sqlDir directory.
• SQL files can contain multiple SQL statements. The last SELECT statement becomes the return value.

The two basic shapes are Entity (single row hydrated to an object) and Entity list (rowlist hydrated to an array of objects). Other shapes — raw assoc arrays, custom collections, pagination, DML results — build on these.

Entity (single row)

When you specify an entity class as the return type, the SQL result is automatically converted (hydrated) into an instance of that class.

interface TodoItemInterface
{
    #[DbQuery('todo_item')]
    public function getItem(string $id): Todo;
}

Constructor Property Promotion (Recommended)

Using constructor property promotion creates type-safe and immutable entities.

final class Todo
{
    public function __construct(
        public readonly string $id,
        public readonly string $title
    ) {}
}

Values are bound to constructor arguments by position, in the order of the columns in the SELECT clause. Column names (e.g. user_name) do not need to match property names (e.g. $userName) — just make sure the column order matches the constructor argument order.

Entity|null is also supported and returns null when no row matches.

Note: When the entity has no constructor, Ray.MediaQuery falls back to PDO’s FETCH_CLASS and maps column name → property name (no snake_case conversion). This avoids any dependency on column ordering, which is useful for wide read-only DTOs or PHP 8.4 readonly class declarations.

Entity list (rowlist)

Declare array as the return type to receive multiple rows. Tell the framework which entity to hydrate each row into via a @return list<Entity> docblock or the factory: parameter on #[DbQuery].

interface TodoListInterface
{
    /** @return list<Todo> */
    #[DbQuery('todo_list')]
    public function list(): array;

    #[DbQuery('todo_list', factory: TodoFactory::class)]
    public function listByFactory(): array;
}

Without @return list<Entity> or factory:, rows are returned as associative arrays — see type: ‘row’ below for the single-row equivalent.

type: ‘row’ (raw associative array)

By default, an array return type yields a rowlist ([['id' => '1', 'title' => 'run'], ...]). To receive a single row — for example an aggregate result such as ['total' => 10, 'active' => 5] — directly as an associative array, specify type: 'row'. Without it, that row is returned at $result[0].

interface TodoItemInterface
{
    #[DbQuery('todo_stats', type: 'row')]
    public function getStats(string $id): array;  // ['total' => 10, 'active' => 5]
}

AffectedRows (UPDATE / DELETE row count)

Declare AffectedRows as the return type to receive the affected row count of an UPDATE / DELETE as a typed value rather than a bare int.

use Ray\MediaQuery\Result\AffectedRows;

interface TodoRepositoryInterface
{
    #[DbQuery('todo_delete')]
    public function delete(string $id): AffectedRows;
}

$affected = $todoRepo->delete($id);
$affected->count;        // int — number of affected rows
$affected->isAffected(); // bool — true when count > 0

When a SQL file contains multiple statements, AffectedRows reflects the last executed statement only.

Executable examples: TodoAffectedInterface and DbQueryAffectedRowsTest.

InsertedRow (INSERT resolved values and id)

Use InsertedRow to recover the values the framework injected on the caller’s behalf (UUIDs, timestamps, DateTime → SQL strings, ToScalarInterface reductions) together with the auto-increment id reported by the driver. The same SQL id can be reused with a different return type — the framework switches behaviour (execute-only / affected count / inserted id) based on the declared return type alone.

use Ray\MediaQuery\Result\InsertedRow;

interface TodoAddInterface
{
    #[DbQuery('todo_add')]
    public function add(string $title): void;

    #[DbQuery('todo_add')]
    public function addReturning(string $title): InsertedRow;
}

$inserted = $todoAdd->addReturning('Write docs');
$inserted->values;  // array<string, mixed> — resolved values bound to the driver
$inserted->id;      // ?string — auto-increment id, null when none was assigned

$inserted->id is normalised to null when the driver returns false / '' / '0'.

PostQueryInterface (custom typed results)

Sometimes you want to wrap a SELECT result in your own collection type — exposing domain methods like published() / titles() instead of returning a plain array<Article>. Declare a class that implements PostQueryInterface as the return type, and the framework collects the post-execution state into a PostQueryContext, passes it to the static fromContext() factory, and lets the class decide how to assemble itself.

interface PostQueryInterface
{
    public static function fromContext(PostQueryContext $context): static;
}

PostQueryContext provides four readonly properties:

use Ray\MediaQuery\Result\PostQueryContext;
use Ray\MediaQuery\Result\PostQueryInterface;

/** @implements IteratorAggregate<int, Article> */
final class Articles implements PostQueryInterface, IteratorAggregate, Countable
{
    /** @param list<Article> $rows */
    public function __construct(public readonly array $rows) {}

    public static function fromContext(PostQueryContext $context): static
    {
        /** @var list<Article> $rows */
        $rows = $context->rows;
        return new static($rows);
    }

    public function getIterator(): ArrayIterator { return new ArrayIterator($this->rows); }
    public function count(): int { return count($this->rows); }
}

interface ArticleRepositoryInterface
{
    #[DbQuery('article_list', factory: ArticleFactory::class)]
    public function list(): Articles;
}

Hydration of each row is configured the same way as for an Entity list: via a generic @return YourWrapper<Entity> docblock or factory:. The wrapper uses composition rather than inheritance, so it can hold any internal collection — Laravel Collection, Doctrine ArrayCollection, or a custom one — without coupling to any specific library.

Executable examples in Ray.MediaQuery:

• Articles — collection wrapper around PostQueryContext::$rows
• ArticlesInterface — declaring assoc rows, docblock-hydrated rows, and factory: hydrated rows

AffectedRows and InsertedRow are themselves implementations of PostQueryInterface. If you need a custom DML result type — e.g. one that bundles audit logging or aggregate counters — you can build it through the same mechanism.

Return type cheat sheet

For richer return types:

• MyColl implementing PostQueryInterface — custom typed collection wrappers
• PagesInterface + #[Pager] — pagination
• AffectedRows — DML affected row count
• InsertedRow — DML insert id + resolved values
• void — DML execute only

Parameters

DateTime

You can pass value objects as parameters. For example, DateTimeInterface objects can be specified like this:

interface TaskAddInterface
{
    #[DbQuery('task_add')]
    public function __invoke(string $title, DateTimeInterface $createdAt = null): void;
}

Values are converted to date-formatted strings during SQL execution.

INSERT INTO task (title, created_at) VALUES (:title, :createdAt); # 2021-2-14 00:00:00

If no value is passed, the bound current time is injected. This eliminates the need to hard-code NOW() in SQL or pass current time every time.

Test Time

For testing, you can bind DateTimeInterface to a single time like this:

$this->bind(DateTimeInterface::class)->to(UnixEpochTime::class);

Value Objects (VO)

When value objects other than DateTime are passed, the return value of the toScalar() method implementing ToScalarInterface, or the __toString() method becomes the argument.

interface MemoAddInterface
{
    #[DbQuery('memo_add')]
    public function __invoke(string $memo, UserId $userId = null): void;
}

class UserId implements ToScalarInterface
{
    public function __construct(
        private readonly LoginUser $user
    ) {}

    public function toScalar(): int
    {
        return $this->user->id;
    }
}

INSERT INTO memo (user_id, memo) VALUES (:user_id, :memo);

Parameter Injection

Note that the default value null for value object arguments is never used in SQL or Web requests. When no value is passed, the scalar value of the value object injected by parameter type is used instead of null.

public function __invoke(Uuid $uuid = null): void; // UUID is generated and passed

Pagination

You can paginate SELECT queries with the #[Pager] attribute.

use Ray\MediaQuery\Annotation\DbQuery;
use Ray\MediaQuery\Annotation\Pager;
use Ray\MediaQuery\PagesInterface;

interface TodoList
{
    #[DbQuery('todo_list'), Pager(perPage: 10, template: '/{?page}')]
    public function __invoke(): PagesInterface;
}

You can get the count with count(), and get page objects with array access by page number. PagesInterface is a SQL lazy execution object.

$pages = ($todoList)();
$cnt = count($pages);    // Count SQL is generated and queried when count() is called
$page = $pages[2];       // DB query for that page is executed when array access is made

// $page->data           // sliced data
// $page->current;       // current page number
// $page->total          // total count
// $page->hasNext        // whether next page exists
// $page->hasPrevious    // whether previous page exists
// $page->maxPerPage;    // maximum items per page
// (string) $page        // pager HTML

SqlQuery

SqlQuery executes SQL by specifying the SQL file ID. Used when preparing implementation classes for detailed implementation.

class TodoItem implements TodoItemInterface
{
    public function __construct(
        private SqlQueryInterface $sqlQuery
    ) {}

    public function __invoke(string $id): array
    {
        return $this->sqlQuery->getRow('todo_item', ['id' => $id]);
    }
}

get* Methods

Use appropriate get* methods to retrieve SELECT results based on the expected result type.

$sqlQuery->getRow($queryId, $params);        // Result is single row
$sqlQuery->getRowList($queryId, $params);    // Result is multiple rows
$statement = $sqlQuery->getStatement();       // Get PDO Statement
$pages = $sqlQuery->getPages();              // Get pager

Ray.MediaQuery includes Ray.AuraSqlModule. For lower-level operations, use Aura.Sql’s Query Builder or PDO-extended Aura.Sql. doctrine/dbal is also available.

Like parameter injection, passing DateTimeInterface objects converts them to date-formatted strings.

$sqlQuery->exec('memo_add', [
    'memo' => 'run',
    'created_at' => new DateTime()
]);

Other objects are converted to toScalar() or __toString() values.

Integration with Ray.InputQuery

When using Ray.InputQuery with BEAR.Resource, Input classes can be passed directly as MediaQuery parameters.

use Ray\InputQuery\Attribute\Input;

final class UserCreateInput
{
    public function __construct(
        #[Input] public readonly string $name,
        #[Input] public readonly string $email,
        #[Input] public readonly int $age
    ) {}
}

interface UserCreateInterface
{
    #[DbQuery('user_create')]
    public function add(UserCreateInput $input): void;
}

Input object properties are automatically expanded to SQL parameters.

-- user_create.sql
INSERT INTO users (name, email, age) VALUES (:name, :email, :age);

This integration enables consistent type-safe data flow from ResourceObject to MediaQuery.

Profiler

Media access is logged by loggers. By default, a memory logger for testing is bound.

public function testAdd(): void
{
    $this->sqlQuery->exec('todo_add', $todoRun);
    $this->assertStringContainsString(
        'query: todo_add({"id":"1","title":"run"})',
        (string) $this->log
    );
}

You can implement your own MediaQueryLoggerInterface to benchmark each media query or log with injected PSR loggers.

PerformSqlInterface

By implementing PerformSqlInterface, you can completely customize the SQL execution layer. Replace the default execution process with your own implementation to achieve advanced logging, performance monitoring, security controls, and more.

use Exception;
use Ray\MediaQuery\PerformSqlInterface;

final class CustomPerformSql implements PerformSqlInterface
{
    public function __construct(
        private LoggerInterface $logger
    ) {}

    #[Override]
    public function perform(ExtendedPdoInterface $pdo, string $sqlId, string $sql, array $values): PDOStatement
    {
        $startTime = microtime(true);

        // Custom logging
        $this->logger->info("Executing SQL: {$sqlId}", [
            'sql' => $sql,
            'params' => $values
        ]);

        try {
            /** @var array<string, mixed> $values */
            $statement = $pdo->perform($sql, $values);

            // Execution time logging
            $executionTime = microtime(true) - $startTime;
            $this->logger->info("SQL executed successfully", [
                'sqlId' => $sqlId,
                'execution_time' => $executionTime
            ]);

            return $statement;
        } catch (Exception $e) {
            $this->logger->error("SQL execution failed: {$sqlId}", [
                'error' => $e->getMessage(),
                'sql' => $sql
            ]);
            throw $e;
        }
    }
}

To use your custom implementation, bind it in the DI container:

use Ray\MediaQuery\PerformSqlInterface;

protected function configure(): void
{
    $this->bind(PerformSqlInterface::class)->to(CustomPerformSql::class);
}

SQL Template

You can customize SQL log formatting to include query IDs in the executed SQL, making it easier to identify which queries are running when analyzing slow logs.

Use MediaQuerySqlTemplateModule to customize the SQL log format.

use Ray\MediaQuery\MediaQuerySqlTemplateModule;

protected function configure(): void
{
    $this->install(new MediaQuerySqlTemplateModule("-- App: .sql\n"));
}

Available template variables:

• {{ id }}: Query ID
• {{ sql }}: The actual SQL statement

Default template: -- {{ id }}.sql\n{{ sql }}

This feature includes the query ID as a comment in the executed SQL, making it easy to identify which application query was executed when analyzing database slow logs.

-- App: todo_item.sql
SELECT * FROM todo WHERE id = :id