Ray.MediaQuery Hands-on Tutorial

This tutorial builds a small blog service and walks through the major Ray.MediaQuery features through 1.1.0.

日本語版はこちら

How to Read This Tutorial

Each chapter follows the same flow.

  1. Goal - what you will be able to do in the chapter
  2. Steps - write SQL, then an interface, then add code to run.php
  3. Run and expected output - execute your working run.php with php mywork/run.php
  4. Explanation - what the framework is doing
  5. Next chapter

The completed source is available under docs/tutorial/src/. Treat it as the answer key when you get stuck.

About the completed run.php: docs/tutorial/src/run.php is an integrated demo that runs the whole tutorial. Expected output sections are labeled either (standalone) or (integrated run.php).

Your own run.php may produce different ids and counts depending on which chapters you have accumulated. Check the type and structure, not just the exact number. The completed run.php executes chapters 1 through 12 plus the appendix. Chapter 13 is a testing-strategy chapter and has no demo code.

This tutorial intentionally rewrites some method declarations as it progresses. For example, add() returns AffectedRows in chapter 3, void in chapter 6, and the final InsertedRow shape from chapter 10 onward. You will experience the intermediate forms first, then converge on the final interface.

The tutorial assumes Ray.MediaQuery 1.1.0 or later. These 1.1.0 features are covered hands-on, so the later chapters do not work unchanged on the 1.0 series.

You do not need to complete everything at once. To get the implementation feel, chapters 0 through 6 are enough. To understand the 1.1 additions, read chapters 9 through 12 and the conclusion.

Return Type Cheat Sheet

In Ray.MediaQuery, the method return type is the contract that tells the framework how to handle the result. The SQL kind is not the only signal.

Return type / docblock Meaning
array Return multiple rows as a list of associative arrays
?array + type: 'row' Return one associative row, or null if no row matches
/** @return array<Article> */ array Hydrate multiple rows into Article objects
?Article + type: 'row' Return one Article object, or null if no row matches
void Execute DML and ignore the result
AffectedRows Return the row count affected by INSERT / UPDATE / DELETE
InsertedRow Return the auto-increment id and the resolved bound values after INSERT
Pages<Article> Return a paginated Article list
PostQueryInterface implementation Build a custom result object from PostQueryContext after execution

The SQL examples use a readable “Holywell-lite” style.

SQL placeholders are the exception: they match PHP argument names and therefore use camelCase, such as :authorName.

Completed Directory Structure

The tree below is the completed answer under docs/tutorial/src/ with namespace Tutorial\Blog\. Your own copied code goes under mywork/ with namespace MyBlog\ (see chapter 0). The namespaces differ, so both trees can coexist in the same repository.

docs/tutorial/src/
|-- run.php                  # Entry point that runs all chapters
|-- schema.sql               # Table definitions
|-- Blog/
|   |-- Article.php
|   |-- ArticleQueryInterface.php
|   |-- Comment.php
|   |-- CommentQueryInterface.php
|   |-- ArticleId.php                # ToScalarInterface implementation
|   |-- ArticleStats.php
|   |-- ArticleStatsFactory.php      # DI factory
|   |-- MarkdownExcerpter.php        # Injected into the factory
|   |-- ArticleSearchResult.php      # SELECT PostQueryInterface
|   `-- CreatedArticle.php           # DML + SELECT PostQueryInterface
`-- sql/
    |-- article_add.sql
    |-- article_create_and_get.sql
    |-- article_item.sql
    |-- article_list.sql
    |-- article_update.sql
    |-- article_delete.sql
    |-- article_paginated.sql
    |-- article_search.sql
    |-- article_stats.sql
    |-- article_stats_paginated.sql
    |-- comment_add.sql
    `-- comment_list.sql

Table of Contents

Chapter Title Feature
Chapter 0 Setup autoload, SQLite :memory:
Chapter 1 First query: listing rows #[DbQuery] / SELECT row list
Chapter 2 Fetching one row #[DbQuery(type: 'row')]
Chapter 3 INSERT and AffectedRows INSERT / AffectedRows
Chapter 4 Automatic entity mapping Constructor promotion / readonly
Chapter 5 Constructor hydration and SELECT column order FetchNewInstance / hydration path
Chapter 6 DateTime and ToScalar DateTimeInterface / ToScalarInterface
Chapter 7 Building derived values with a factory factory: static factory
Chapter 8 Injecting dependencies into a factory factory: DI factory
Chapter 9 UPDATE / DELETE and affected row counts AffectedRows
Chapter 10 Getting id and final values from INSERT InsertedRow
Chapter 11 Pagination #[Pager] / Pages<Article> / factory hydration
Chapter 12 Custom PostQueryInterface SELECT-capable PostQueryInterface::fromContext()
Chapter 13 Testing strategy fake bindings
Appendix Multi-statement DML + SELECT one method for INSERT + SELECT
Conclusion Query-first vs Repository Pattern query contracts and CQRS read models

Chapter 0: Setup

Goal

Step 1. Clone the repository and install dependencies

php -m | grep '^pdo_sqlite$'
git clone https://github.com/ray-di/Ray.MediaQuery.git
cd Ray.MediaQuery
composer install

If pdo_sqlite is printed, you are ready.

Step 2. Create the working tree

Write your code in mywork/ with namespace MyBlog\, separate from the answer tree (docs/tutorial/src/, namespace Tutorial\Blog\). This avoids collisions and mirrors a real project where you install the library and write your own application code.

From the repository root:

mkdir -p mywork/blog mywork/sql

From now on, Blog/Xxx.php means mywork/blog/Xxx.php, sql/xxx.sql means mywork/sql/xxx.sql, and run.php means mywork/run.php.

Step 3. Add autoloading to composer.json

Map the MyBlog\ namespace to mywork/blog/. This is not strictly required for run.php because the bootstrap below calls $loader->addPsr4(), but it is useful for IDE completion and PHPUnit.

{
    "autoload-dev": {
        "psr-4": {
            "MyBlog\\": "mywork/blog/"
        }
    }
}

Apply it with:

composer dump-autoload

The repository’s actual composer.json already contains "Tutorial\\Blog\\": "docs/tutorial/src/Blog/" for the answer code. Leave it as-is. Your code uses the separate MyBlog\ namespace, so the two mappings can coexist.

Step 4. Schema

mywork/schema.sql:

CREATE TABLE IF NOT EXISTS article (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    body TEXT NOT NULL,
    author_name TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'draft',
    published_at TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS comment (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    article_id INTEGER NOT NULL,
    body TEXT NOT NULL,
    posted_at TEXT NOT NULL
);

Explanation

In chapter 1, you start writing query code.

Chapter 1: First Query: Listing Rows

Goal

Step 1. Write SQL

mywork/sql/article_list.sql:

SELECT
    id,
    title,
    body,
    author_name,
    status,
    published_at,
    created_at
FROM article
ORDER BY id;

Step 2. Write the interface

mywork/blog/ArticleQueryInterface.php:

<?php

declare(strict_types=1);

namespace MyBlog;

use Ray\MediaQuery\Annotation\DbQuery;

interface ArticleQueryInterface
{
    #[DbQuery('article_list')]
    public function list(): array;
}

Step 3. Create run.php

mywork/run.php:

<?php

declare(strict_types=1);

namespace MyBlog;

use Aura\Sql\ExtendedPdoInterface;
use Composer\Autoload\ClassLoader;
use Ray\AuraSqlModule\AuraSqlModule;
use Ray\Di\AbstractModule;
use Ray\Di\Injector;
use Ray\MediaQuery\DbQueryConfig;
use Ray\MediaQuery\MediaQueryModule;
use Ray\MediaQuery\Queries;

/** @var ClassLoader $loader */
$loader = require dirname(__DIR__) . '/vendor/autoload.php'; // mywork/run.php -> repository vendor
$loader->addPsr4('MyBlog\\', __DIR__ . '/blog');             // your code, separate from the answer namespace

$sqlDir = __DIR__ . '/sql';
$dsn = 'sqlite::memory:';

$injector = new Injector(new class ($sqlDir, $dsn) extends AbstractModule {
    public function __construct(
        private readonly string $sqlDir,
        private readonly string $dsn,
    ) {
        parent::__construct();
    }

    protected function configure(): void
    {
        $queries = Queries::fromClasses([
            ArticleQueryInterface::class,
        ]);
        $this->install(new MediaQueryModule($queries, [new DbQueryConfig($this->sqlDir)]));
        $this->install(new AuraSqlModule($this->dsn));
    }
});

/** @var ExtendedPdoInterface $pdo */
$pdo = $injector->getInstance(ExtendedPdoInterface::class);
foreach (preg_split('/;\\s*/', trim((string) file_get_contents(__DIR__ . '/schema.sql'))) ?: [] as $stmt) {
    if ($stmt !== '') {
        $pdo->query($stmt);
    }
}

// Seed one row and list articles.
$pdo->perform(
    'INSERT INTO article (title, body, author_name, status, created_at) VALUES (?, ?, ?, ?, ?)',
    ['Hello', 'first body', 'Alice', 'published', '2026-04-01 09:00:00'],
);

/** @var ArticleQueryInterface $articleQuery */
$articleQuery = $injector->getInstance(ArticleQueryInterface::class);

var_dump($articleQuery->list());

Run

php mywork/run.php

The dirname(__DIR__) bootstrap assumes mywork/run.php is one level below the repository root. If you place mywork/ elsewhere, replace that part with the correct path to the repository’s vendor/autoload.php.

Expected Output (chapter 1 / standalone)

array(1) {
  [0]=>
  array(7) {
    ["id"]=>
    int(1)
    ["title"]=>
    string(5) "Hello"
    ["body"]=>
    string(10) "first body"
    ["author_name"]=>
    string(5) "Alice"
    ["status"]=>
    string(9) "published"
    ["published_at"]=>
    NULL
    ["created_at"]=>
    string(19) "2026-04-01 09:00:00"
  }
}

Depending on the environment, id may be returned as string(1) "1" instead of int(1) (for example with older PDO::ATTR_STRINGIFY_FETCHES settings or DSN options). On PHP 8.1+ with standard settings, int(1) is expected.

Explanation

ArticleQueryInterface has no implementation class. Yet $injector->getInstance(ArticleQueryInterface::class) returns an instance. Ray.Aop intercepts methods marked with #[DbQuery], reads article_list.sql, and supplies an automatically generated implementation.

Chapter 2: Fetching One Row

Goal

Step 1. Write SQL

sql/article_item.sql:

SELECT
    id,
    title,
    body,
    author_name,
    status,
    published_at,
    created_at
FROM article
WHERE id = :id;

Step 2. Add a method to the interface

Blog/ArticleQueryInterface.php:

#[DbQuery('article_item', type: 'row')]
public function item(int $id): array|null;

Step 3. Add to run.php

$row = $articleQuery->item(1);
var_dump($row);

Expected Output (chapter 2 / standalone)

array(7) {
  ["id"]=>
  int(1)
  ["title"]=>
  string(5) "Hello"
  ...
}

Explanation

Chapter 3: INSERT and AffectedRows

Goal

Step 1. Write SQL

sql/article_add.sql:

INSERT INTO article (title, body, author_name, status, published_at, created_at)
VALUES (:title, :body, :authorName, :status, :publishedAt, :createdAt);

Step 2. Add a method to the interface

In this chapter, add() returns AffectedRows. Later chapters temporarily change it to void, then to its final InsertedRow form. The completed ArticleQueryInterface.php contains the chapter 10 InsertedRow version.

use Ray\MediaQuery\Result\AffectedRows;

#[DbQuery('article_add')]
public function add(
    string $title,
    string $body,
    string $authorName,
    string $status,
    string|null $publishedAt,
    string $createdAt,
): AffectedRows;

Step 3. Add to run.php

$affected = $articleQuery->add(
    title: 'Second',
    body: 'about SQL and Objects',
    authorName: 'Bob',
    status: 'published',
    publishedAt: '2026-04-02 10:00:00',
    createdAt: '2026-04-02 10:00:00',
);
printf("insert affected=%d\n", $affected->count);
var_dump($articleQuery->list());

Expected Output (chapter 3 / standalone)

insert affected=1
array(2) {
  [0] => array(7) { ... "Hello" ... }
  [1] => array(7) { ... "Second" ... }
}

Explanation

Chapter 4: Automatic Entity Mapping

Goal

Step 1. Write the Entity

Blog/Article.php:

<?php

declare(strict_types=1);

namespace MyBlog;

final class Article
{
    public function __construct(
        public readonly int $id,
        public readonly string $title,
        public readonly string $body,
        public readonly string $authorName,    // receives the 4th SELECT column
        public readonly string $status,
        public readonly string|null $publishedAt,
        public readonly string $createdAt,
    ) {
    }
}

Step 2. Rewrite the interface return types

Change only the return types for list() and item().

/** @return array<Article> */
#[DbQuery('article_list')]
public function list(): array;

#[DbQuery('article_item', type: 'row')]
public function item(int $id): Article|null;

Step 3. Rewrite run.php

About assembling snippets: Each chapter shows fragments to add or replace in run.php; a fragment is not a complete file on its own. When assembling, keep writes such as INSERT before the list() / item() reads that depend on them. The completed docs/tutorial/src/run.php is one possible integrated version, but it is organized for the full demo and will not match your learning file line by line.

Replace the earlier var_dump($articleQuery->list()) and var_dump($row) calls with Entity-aware code.

$articles = $articleQuery->list();
foreach ($articles as $a) {
    printf("[%d] %s by %s\n", $a->id, $a->title, $a->authorName);
}

$first = $articleQuery->item(1);
echo $first?->title, "\n";

Expected Output (chapter 4 / standalone)

[1] Hello by Alice
[2] Second by Bob
Hello

Explanation

Chapter 5: Constructor Hydration and SELECT Column Order

Goal

Key Point

When a returned Entity has a constructor, Ray.MediaQuery uses PDO::FETCH_FUNC and passes each selected column from left to right into the constructor (FetchNewInstance).

-- article_list.sql
SELECT id, title, body, author_name, status, published_at, created_at
FROM article

final class Article
{
    public function __construct(
        public readonly int $id,           // SELECT column 1
        public readonly string $title,     // column 2
        public readonly string $body,      // column 3
        public readonly string $authorName,// column 4; DB column is author_name, but order is what matters
        public readonly string $status,
        public readonly string|null $publishedAt,
        public readonly string $createdAt,
    ) {}
}

This does not work because column names happen to match argument names. It works because the order of SELECT id, title, body, author_name, ... matches the order of __construct(int $id, string $title, string $body, string $authorName, ...). If the SQL is changed to SELECT title, id, ..., $id receives a title string and $title receives an integer id, which produces a TypeError.

Try Breaking It Once

Temporarily change sql/article_list.sql to the broken order below, run it, then change it back.

-- Broken example
SELECT title, id, body, author_name, status, published_at, created_at
FROM article;

TypeError: MyBlog\Article::__construct(): Argument #1 ($id) must be of type int, string given

Entities Without Constructors

When an Entity has no constructor, Ray.MediaQuery uses the FetchClass path (PDO::FETCH_CLASS). That path assigns values to properties with the same names as the columns. The framework does not convert snake_case to camelCase.

// Entity without constructor
final class ArticleBag
{
    public string $id;
    public string $title;
    public string $author_name;        // same as the column name
    public string|null $published_at;  // same as the column name
    // ...
}

If PHP properties should be camelCase, add aliases in SQL.

SELECT id, title, author_name AS authorName, published_at AS publishedAt
FROM article

Modern PHP code often prefers readonly immutable Entities with constructor promotion, which means it usually chooses FetchNewInstance and therefore relies on the practical contract “SELECT column order = constructor argument order.”

Explanation

Chapter 6: DateTime and ToScalar

Goal

Step 1. Write the ArticleId value object

Blog/ArticleId.php:

<?php

declare(strict_types=1);

namespace MyBlog;

use Ray\MediaQuery\ToScalarInterface;

final class ArticleId implements ToScalarInterface
{
    public function __construct(
        public readonly int $value,
    ) {
    }

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

Step 2. Rewrite the interface

In this chapter, change add() to return void so the focus stays on DateTimeInterface conversion. Chapter 10 changes it back to InsertedRow to observe the id and final bound values.

Also change item() from int $id to ArticleId $id.

use DateTimeInterface;

#[DbQuery('article_item', type: 'row')]
public function item(ArticleId $id): Article|null;

#[DbQuery('article_add')]
public function add(
    string $title,
    string $body,
    string $authorName,
    string $status = 'draft',
    DateTimeInterface|null $publishedAt = null,
    DateTimeInterface|null $createdAt = null,
): void;

Step 3. Rewrite run.php

Update earlier calls to match the new signatures.

use DateTimeImmutable;

$articleQuery->add(
    title: 'Third',
    body: 'about DateTime',
    authorName: 'Carol',
    status: 'published',
    publishedAt: new DateTimeImmutable('2026-04-03 11:00:00'),
    createdAt: new DateTimeImmutable('2026-04-03 11:00:00'),
);

$article = $articleQuery->item(new ArticleId(3));
var_dump($article->publishedAt);

Expected Output (chapter 6 / standalone)

string(19) "2026-04-03 11:00:00"

Explanation

SQLite has no native DATETIME type, so values come back as strings. MySQL and PostgreSQL behavior depends on the database type and driver.

Chapter 7: Building Derived Values with a Factory

Goal

Step 1. ArticleStats Entity

Blog/ArticleStats.php:

<?php

declare(strict_types=1);

namespace MyBlog;

final class ArticleStats
{
    public function __construct(
        public readonly int $id,
        public readonly string $title,
        public readonly string $excerpt,
        public readonly int $commentCount,
        public readonly bool $published,
    ) {
    }
}

Step 2. Prepare SQL and comments

sql/article_stats.sql:

SELECT
    a.id,
    a.title,
    a.body,
    (
        SELECT COUNT(*)
        FROM comment AS c
        WHERE c.article_id = a.id
    ) AS comment_count,
    a.status
FROM article AS a
WHERE a.id = :id;

Important: factory method arguments receive values in SELECT column order (PDO::FETCH_FUNC). The order must match; argument names are not the binding contract.

Step 3. Static factory

Start with the simplest static factory. Chapter 8 evolves it into a DI factory.

Later snippets that begin with namespace MyBlog; omit the <?php and declare(strict_types=1); file header. Include them in real files. Also place every factory: class in its own PHP file. Defining it inline inside run.php can cause Cannot redeclare class when Ray.MediaQuery’s generated implementation requires the file again.

namespace MyBlog;

final class ArticleStatsFactory
{
    public static function factory(
        int $id,
        string $title,
        string $body,
        int $commentCount,
        string $status,
    ): ArticleStats {
        $excerpt = mb_strlen($body) <= 60 ? $body : mb_substr($body, 0, 60) . '...';

        return new ArticleStats(
            id: $id,
            title: $title,
            excerpt: $excerpt,
            commentCount: $commentCount,
            published: $status === 'published',
        );
    }
}

Step 4. Add to the interface

#[DbQuery('article_stats', type: 'row', factory: ArticleStatsFactory::class)]
public function stats(ArticleId $id): ArticleStats;

Step 5. Use it in run.php

There are no comments yet, so commentCount is 0 until chapter 8.

$stats = $articleQuery->stats(new ArticleId(1));
var_dump($stats);

Expected Output (chapter 7 / standalone)

object(MyBlog\ArticleStats)#... {
  ["id"]=> int(1)
  ["title"]=> string(5) "Hello"
  ["excerpt"]=> string(...) "..."
  ["commentCount"]=> int(0)
  ["published"]=> bool(true)
}

Explanation

Chapter 8: Injecting Dependencies into a Factory

Goal

Step 1. Service to inject

Blog/MarkdownExcerpter.php:

<?php

declare(strict_types=1);

namespace MyBlog;

use function mb_strlen;
use function mb_substr;
use function strip_tags;
use function trim;

final class MarkdownExcerpter
{
    public function excerpt(string $body, int $length): string
    {
        $plain = trim(strip_tags($body));
        if (mb_strlen($plain) <= $length) {
            return $plain;
        }

        return mb_substr($plain, 0, $length) . '...';
    }
}

Step 2. Rewrite the factory as a DI factory

namespace MyBlog;

final class ArticleStatsFactory
{
    public function __construct(
        private readonly MarkdownExcerpter $excerpter,
    ) {
    }

    public function factory(
        int $id,
        string $title,
        string $body,
        int $commentCount,
        string $status,
    ): ArticleStats {
        return new ArticleStats(
            id: $id,
            title: $title,
            excerpt: $this->excerpter->excerpt($body, 60),
            commentCount: $commentCount,
            published: $status === 'published',
        );
    }
}

Step 3. Bind MarkdownExcerpter in the Module

Add this to the Module’s configure() method in run.php.

$this->bind(MarkdownExcerpter::class);

To make stats() meaningful, add comments. This also revisits Entity hydration through an array<Comment> return type.

Blog/Comment.php:

<?php

declare(strict_types=1);

namespace MyBlog;

final class Comment
{
    public function __construct(
        public readonly int $id,
        public readonly int $articleId,
        public readonly string $body,
        public readonly string $postedAt,
    ) {
    }
}

sql/comment_add.sql:

INSERT INTO comment (article_id, body, posted_at)
VALUES (:articleId, :body, :postedAt);

sql/comment_list.sql:

SELECT
    id,
    article_id,
    body,
    posted_at
FROM comment
WHERE article_id = :articleId
ORDER BY id;

Blog/CommentQueryInterface.php:

<?php

declare(strict_types=1);

namespace MyBlog;

use DateTimeInterface;
use Ray\MediaQuery\Annotation\DbQuery;
use Ray\MediaQuery\Result\InsertedRow;

interface CommentQueryInterface
{
    #[DbQuery('comment_add')]
    public function add(
        int $articleId,
        string $body,
        DateTimeInterface|null $postedAt = null,
    ): InsertedRow;

    /** @return array<Comment> */
    #[DbQuery('comment_list')]
    public function listFor(int $articleId): array;
}

Step 5. Use commentQuery in run.php

Add CommentQueryInterface::class to Queries::fromClasses() and get the instance.

$queries = Queries::fromClasses([
    ArticleQueryInterface::class,
    CommentQueryInterface::class,
]);

/** @var CommentQueryInterface $commentQuery */
$commentQuery = $injector->getInstance(CommentQueryInterface::class);

Add comments, then verify stats() and listFor().

$commentQuery->add(1, 'Great post!', new DateTimeImmutable('2026-04-01 12:00:00'));
$commentQuery->add(1, 'Thanks!',     new DateTimeImmutable('2026-04-01 13:00:00'));

$stats = $articleQuery->stats(new ArticleId(1));
printf("commentCount=%d, excerpt='%s'\n", $stats->commentCount, $stats->excerpt);

$comments = $commentQuery->listFor(1);
printf("comments=%d, first body='%s' (id=%d)\n", count($comments), $comments[0]->body, $comments[0]->id);

Expected Output (chapter 8 / integrated run.php)

From here onward, article text and counts depend on accumulated previous chapters, so the expected output uses the completed integrated run.php.

commentCount=2, excerpt='This is the first post about interface-driven SQL.'
comments=2, first body='Great post!' (id=1)

Explanation

Chapter 9: UPDATE / DELETE and Affected Row Counts

Goal

Step 1. Write SQL

sql/article_update.sql:

UPDATE article
SET
    title = :title,
    body = :body
WHERE id = :id;

sql/article_delete.sql:

DELETE FROM article
WHERE id = :id;

Step 2. Add methods to the interface

use Ray\MediaQuery\Result\AffectedRows;

#[DbQuery('article_update')]
public function update(ArticleId $id, string $title, string $body): AffectedRows;

#[DbQuery('article_delete')]
public function delete(ArticleId $id): AffectedRows;

Step 3. Use them in run.php

$updated = $articleQuery->update(new ArticleId(1), 'Hello (edited)', 'updated body');
printf("updated count=%d, isAffected=%s\n", $updated->count, $updated->isAffected() ? 'yes' : 'no');

$deleted = $articleQuery->delete(new ArticleId(2));
printf("deleted count=%d\n", $deleted->count);

Expected Output (chapter 9 / integrated run.php)

updated count=1, isAffected=yes
deleted count=1

Explanation

Chapter 10: Getting the ID and Final Values from INSERT

Goal

Step 1. Rewrite the interface

Change add() from chapter 3’s AffectedRows and chapter 6’s void to InsertedRow. This is the final add() shape used by the tutorial.

use Ray\MediaQuery\Result\InsertedRow;

#[DbQuery('article_add')]
public function add(
    string $title,
    string $body,
    string $authorName,
    string $status = 'draft',
    DateTimeInterface|null $publishedAt = null,
    DateTimeInterface|null $createdAt = null,
): InsertedRow;

Step 2. Rewrite run.php

Replace the chapter 6 $articleQuery->add(...) call that ignored the return value with code that captures $inserted.

$inserted = $articleQuery->add(
    title: 'Hello',
    body: 'first body',
    authorName: 'Alice',
    status: 'published',
    publishedAt: new DateTimeImmutable('2026-04-01 09:00:00'),
    createdAt:   new DateTimeImmutable('2026-04-01 09:00:00'),
);

printf("id=%s\n", $inserted->id);
var_dump($inserted->values);

With this method declaration, omitting publishedAt or createdAt lets ParamInjector inject a DateTimeInterface value and lets ParamConverter convert it to a SQL string. Omitting does not mean NULL. Pass publishedAt: null explicitly when you want to store NULL.

$draft = $articleQuery->add(
    title: 'Draft',
    body: 'createdAt is injected',
    authorName: 'Dana',
);

var_dump($draft->values['createdAt']);

Expected Output (chapter 10 / integrated run.php)

In the integrated run.php, this add('Hello', ...) is the first INSERT, so id=1. If your own run.php has accumulated earlier INSERTs, the id will be larger.

id=1
array(6) {
  ["title"]=> string(5) "Hello"
  ["body"]=> string(10) "first body"
  ["authorName"]=> string(5) "Alice"
  ["status"]=> string(9) "published"
  ["publishedAt"]=> string(19) "2026-04-01 09:00:00"
  ["createdAt"]=> string(19) "2026-04-01 09:00:00"
}
string(19) "2026-04-25 12:34:56" // Example runtime timestamp

Explanation

Chapter 11: Pagination

Goal

Step 1. SQL

sql/article_paginated.sql can be the same as article_list.sql.

SELECT
    id,
    title,
    body,
    author_name,
    status,
    published_at,
    created_at
FROM article
ORDER BY id;

Step 2. Add to the interface

use Ray\MediaQuery\Annotation\Pager;
use Ray\MediaQuery\Pages;

/** @return Pages<Article> */
#[DbQuery('article_paginated')]
#[Pager(perPage: 10)]
public function paginated(): Pages;

Step 3. Add more data in run.php

for ($i = 3; $i <= 32; $i++) {
    $articleQuery->add(
        title: "Post #{$i}",
        body: "Body for post {$i}.",
        authorName: 'Carol',
        status: 'published',
        publishedAt: new DateTimeImmutable('2026-04-03 00:00:00'),
        createdAt:   new DateTimeImmutable('2026-04-03 00:00:00'),
    );
}

$pages = $articleQuery->paginated();
$page1 = $pages[1];

printf("total items=%d\n", count($pages));
printf("page 1 has %d items, hasNext=%s\n", count($page1->data), $page1->hasNext ? 'yes' : 'no');
echo $page1->data[0]->title, "\n";

Expected Output (chapter 11 / paginated)

total items=31
page 1 has 10 items, hasNext=yes
Hello (edited)

Step 4. Ray.MediaQuery 1.1: Combine Pager and Factory

In 1.1.0, #[DbQuery(factory: ...)] is honored even on a query with #[Pager]. Rows inside $page->data are also built through the factory.

sql/article_stats_paginated.sql:

SELECT
    a.id,
    a.title,
    a.body,
    (
        SELECT COUNT(*)
        FROM comment AS c
        WHERE c.article_id = a.id
    ) AS comment_count,
    a.status
FROM article AS a
ORDER BY a.id;

Add to Blog/ArticleQueryInterface.php:

/** @return Pages<ArticleStats> */
#[DbQuery('article_stats_paginated', factory: ArticleStatsFactory::class)]
#[Pager(perPage: 10)]
public function statsPaginated(): Pages;

Verify it in run.php.

$statsPages = $articleQuery->statsPaginated();
$statsPage1 = $statsPages[1];
$firstStats = $statsPage1->data[0];

printf(
    "first stats row=%s commentCount=%d excerpt='%s'\n",
    $firstStats::class,
    $firstStats->commentCount,
    $firstStats->excerpt,
);

Expected Output (chapter 11 / statsPaginated)

first stats row=MyBlog\ArticleStats commentCount=2 excerpt='Updated body.'

Explanation

Chapter 12: Custom PostQueryInterface

Goal

Step 1. SQL

sql/article_search.sql:

SELECT
    id,
    title,
    body,
    author_name,
    status,
    published_at,
    created_at
FROM article
WHERE
    title LIKE :keyword
    OR body LIKE :keyword
ORDER BY id;

Step 2. Write the result class

First, add a domain-specific exception instead of throwing a generic UnexpectedValueException directly. A specific exception lets callers catch only this intent.

Blog/Exception/UnexpectedRowException.php:

<?php

declare(strict_types=1);

namespace MyBlog\Exception;

use UnexpectedValueException;

final class UnexpectedRowException extends UnexpectedValueException
{
}

Blog/ArticleSearchResult.php:

<?php

declare(strict_types=1);

namespace MyBlog;

use Override;
use Ray\MediaQuery\Result\PostQueryContext;
use Ray\MediaQuery\Result\PostQueryInterface;
use MyBlog\Exception\UnexpectedRowException;

use function count;

/** @template T of Article */
final class ArticleSearchResult implements PostQueryInterface
{
    /** @param list<T> $rows */
    public function __construct(
        public readonly array $rows,
        public readonly int $matched,
        public readonly string $sql,
    ) {
    }

    #[Override]
    public static function fromContext(PostQueryContext $context): static
    {
        $matched = count($context->rows);
        $rows = [];
        foreach ($context->rows as $row) {
            if (! $row instanceof Article) {
                throw new UnexpectedRowException('ArticleSearchResult expects Article rows.');
            }

            $rows[] = $row;
        }

        return new static(
            rows: $rows,
            matched: $matched,
            sql: $context->statement->queryString,
        );
    }
}

Step 3. Add to the interface

/** @return ArticleSearchResult<Article> */
#[DbQuery('article_search')]
public function search(string $keyword): ArticleSearchResult;

The docblock @return ArticleSearchResult<Article> is the Entity hydration hint. It tells Ray.MediaQuery to place a list of Article objects into $context->rows before calling your result class. This is metadata for the framework and static analysis; it is not a PHP runtime generic.

Step 4. Use it in run.php

$result = $articleQuery->search('%Post%');
printf("matched=%d, sql contains 'LIKE'=%s\n", $result->matched, str_contains($result->sql, 'LIKE') ? 'yes' : 'no');
echo "First hit: ", $result->rows[0]->title, "\n";

$result->sql ($context->statement->queryString) is the SQL after it has been rewritten for execution. Aura.Sql rewrites repeated named placeholders, such as a second :keyword, to names like :keyword__1, and multi-statement splitting can remove the trailing ;. Avoid exact string comparison against the SQL file. Use partial checks such as str_contains($result->sql, 'LIKE').

Expected Output (chapter 12 / integrated run.php)

matched=30, sql contains 'LIKE'=yes
First hit: Post #3

Explanation

Chapter 13: Testing Strategy

Goal

Idea

ArticleQueryInterface is the contract. In production, Ray.MediaQuery automatically generates an implementation that talks to SQLite or MySQL. In tests, bind a fake implementation and test logic without a database.

Step 1. Write a fake implementation

For fake methods that are not used in a test, throw a domain-specific exception rather than a generic \LogicException.

This fake implements the final ArticleQueryInterface, including createAndGet() from the appendix. If you have not read the appendix yet, skip that method for now.

Place the fake at mywork/blog/Test/FakeArticleQuery.php with namespace MyBlog\Test, which maps to mywork/blog/Test/ under PSR-4.

Blog/Exception/UnsupportedQueryException.php:

<?php

declare(strict_types=1);

namespace MyBlog\Exception;

use LogicException;

final class UnsupportedQueryException extends LogicException
{
}

namespace MyBlog\Test;

use DateTimeInterface;
use MyBlog\Article;
use MyBlog\ArticleId;
use MyBlog\ArticleQueryInterface;
use MyBlog\ArticleSearchResult;
use MyBlog\ArticleStats;
use MyBlog\CreatedArticle;
use MyBlog\Exception\UnsupportedQueryException;
use Ray\MediaQuery\Pages;
use Ray\MediaQuery\Result\AffectedRows;
use Ray\MediaQuery\Result\InsertedRow;

final class FakeArticleQuery implements ArticleQueryInterface
{
    /** @var array<int, Article> */
    private array $store = [];

    public function list(): array { return array_values($this->store); }
    public function item(ArticleId $id): Article|null { return $this->store[$id->value] ?? null; }

    public function add(string $title, string $body, string $authorName, string $status = 'draft', DateTimeInterface|null $publishedAt = null, DateTimeInterface|null $createdAt = null): InsertedRow
    {
        $id = count($this->store) + 1;
        $this->store[$id] = new Article($id, $title, $body, $authorName, $status, $publishedAt?->format('Y-m-d H:i:s'), $createdAt?->format('Y-m-d H:i:s') ?? '');

        return new InsertedRow(
            values: compact('title', 'body', 'authorName', 'status'),
            id: (string) $id,
        );
    }

    public function update(ArticleId $id, string $title, string $body): AffectedRows { /* ... */ return new AffectedRows(1); }
    public function delete(ArticleId $id): AffectedRows { unset($this->store[$id->value]); return new AffectedRows(1); }
    public function paginated(): Pages { throw new UnsupportedQueryException('not used in this test'); }
    public function statsPaginated(): Pages { throw new UnsupportedQueryException('not used in this test'); }
    public function stats(ArticleId $id): ArticleStats { throw new UnsupportedQueryException('not used'); }
    public function search(string $keyword): ArticleSearchResult { throw new UnsupportedQueryException('not used'); }
    public function createAndGet(string $title, string $body, string $authorName, string $status = 'draft', DateTimeInterface|null $createdAt = null): CreatedArticle
    {
        $inserted = $this->add($title, $body, $authorName, $status, null, $createdAt);

        return new CreatedArticle($this->store[(int) $inserted->id]);
    }
}

Step 2. Replace the binding in a Module

$injector = new Injector(new class extends AbstractModule {
    protected function configure(): void
    {
        $this->bind(ArticleQueryInterface::class)->to(FakeArticleQuery::class)->in(\Ray\Di\Scope::SINGLETON);
    }
});

/** @var ArticleQueryInterface $articleQuery */
$articleQuery = $injector->getInstance(ArticleQueryInterface::class);
$articleQuery->add('T', 'B', 'A');
assert($articleQuery->item(new ArticleId(1))->title === 'T');

In the default PHP CLI configuration (zend.assertions=-1), assert() is removed at compile time and does not verify anything. For a quick manual check, run php -d zend.assertions=1 mywork/run.php. In real projects, use PHPUnit assertions instead.

Explanation

Appendix: Multi-statement DML + SELECT

The main Ray.MediaQuery features have now been covered. One more 1.1 feature is useful: PostQueryInterface can receive SELECT results, so you can express “INSERT, then SELECT the created row” as one method contract.

Goal

Step 1. SQL

A use case such as “create an article and return the created row” is often implemented by hand as INSERT, last insert id, SELECT, hydrate. Ray.MediaQuery can declare the whole interaction with SQL and a return type.

sql/article_create_and_get.sql:

INSERT INTO article (title, body, author_name, status, created_at)
VALUES (:title, :body, :authorName, :status, :createdAt);

SELECT
    id,
    title,
    body,
    author_name,
    status,
    published_at,
    created_at
FROM article
WHERE id = last_insert_rowid();

last_insert_rowid() is SQLite-specific. MySQL uses LAST_INSERT_ID(), and PostgreSQL or SQLite 3.35+ can also use INSERT ... RETURNING. Ray.MediaQuery splits multiple statements by ;, so the final SELECT should also end with ;.

Blog/CreatedArticle.php:

<?php

declare(strict_types=1);

namespace MyBlog;

use Override;
use Ray\MediaQuery\Result\PostQueryContext;
use Ray\MediaQuery\Result\PostQueryInterface;
use MyBlog\Exception\UnexpectedRowException;

/** @template T of Article */
final class CreatedArticle implements PostQueryInterface
{
    /** @param T $article */
    public function __construct(
        public readonly Article $article,
    ) {
    }

    #[Override]
    public static function fromContext(PostQueryContext $context): static
    {
        $article = $context->rows[0] ?? null;
        if (! $article instanceof Article) {
            throw new UnexpectedRowException('CreatedArticle expects the final SELECT to return an Article row.');
        }

        return new static($article);
    }
}

Blog/ArticleQueryInterface.php:

/** @return CreatedArticle<Article> */
#[DbQuery('article_create_and_get')]
public function createAndGet(
    string $title,
    string $body,
    string $authorName,
    string $status = 'draft',
    DateTimeInterface|null $createdAt = null,
): CreatedArticle;

run.php:

$created = $articleQuery->createAndGet(
    title: 'Created and fetched',
    body: 'A multi-statement query can return the row created by its first statement.',
    authorName: 'Eve',
);

printf(
    "created article id=%d title='%s' status=%s\n",
    $created->article->id,
    $created->article->title,
    $created->article->status,
);

Expected Output (appendix / integrated run.php)

created article id=33 title='Created and fetched' status=draft

Explanation

The important point is that createAndGet() is not a convenience method on an ArticleRepository; it is a typed Query contract for “create an article and return the created result.”

Conclusion: Query-first vs Repository Pattern

Throughout this tutorial, queries were expressed with interface + attribute + SQL + return type, without writing repository implementation classes.

This is not just a shorter Repository Pattern. A Repository abstracts a persisted object collection. Ray.MediaQuery abstracts an executable Query contract.

Viewpoint Repository Pattern Ray.MediaQuery
Center Entity / Aggregate Query / UseCase
Main use Write Model; save and restore Aggregates Read Model, Projection, CQRS Query side
Implementation Handwritten Repository class Interface + Attribute + SQL
Result processing Procedures inside Repository implementation factory: / PostQueryInterface
SQL Easy to bury inside implementation Explicit SQL files
Replacement Replace Repository interface with fake / mock Replace Query interface with fake / mock

Repositories are still useful. On the write side, where you restore, mutate, and save Aggregates, they remain a valid abstraction.

On the read side, you often want a projection shaped for a specific use case. If an Entity-centered Repository expands to serve dashboards, search, admin screens, and analytics, too many entry points accumulate in one place.

Ray.MediaQuery splits the read side query-first. Instead of adding methods to UserRepository, define contracts such as UserDashboardQuery, ArticleSearchQuery, and MonthlyStatsQuery for the interactions themselves.

Simplification in this tutorial: to keep the walkthrough compact, list, item, add, update, delete, paginated, statsPaginated, stats, search, and createAndGet are gathered into one ArticleQueryInterface. In a real project, split by use case, such as ArticleSearchQueryInterface, ArticleStatsQueryInterface, and ArticleCommandInterface. Fakes become smaller and responsibilities clearer.

The appendix’s DML + SELECT example pushes this query-first idea further. Use Repositories for write-side Aggregate persistence. Use query-first contracts for read-side projection retrieval and typed results after DML. Ray.MediaQuery exists to make the latter explicit with interfaces and SQL.

You Finished the Walkthrough

You have now experienced the main Ray.MediaQuery features.

This hands-on tutorial focuses on understanding application Query contracts built from interface + SQL + return type. The following advanced features are not implemented here; see the Manual for details.

Next Reading

Community