Download the PHP package ray/media-query without Composer

Ray.MediaQuery

Database access mapping framework

日本語 (Japanese)

Overview

Ray.MediaQuery provides database query abstraction through interface-based query definitions.

Motivation

• This framework provides database query abstraction through interface-based query definitions.
• Execution objects are generated automatically so you do not need to write procedural code for execution.
• Since usage codes are indifferent to the actual state of external media, storage can be changed later. Easy parallel development and stubbing.

Composer install

$ composer require ray/media-query

For Web API queries, install the separate package:

$ composer require ray/web-query

Note: For migration from older versions, see MIGRATION.md.

Getting Started

Define the interface for media access.

DB

Specify the SQL ID with the attribute DbQuery.

Module

MediaQueryModule binds the execution of SQL to an interface by setting DbQueryConfig.

Note: MediaQueryModule requires AuraSqlModule to be installed.

Request object injection

You do not need to prepare an implementation class. It is generated and injected from the interface.

DbQuery

When the method is called, the SQL specified by the ID is bound with the method argument and executed. For example, if the ID is todo_item, the todo_item.sql SQL statement is bound with ['id => $id] and executed.

• If the result is a row(array<string, scalar>), specify type:'row'. The type is not necessary for row_list(array<int, array<string, scalar>>).
• SQL files can contain multiple SQL statements. In that case, the return value is the last line of the SELECT.

Entity

When the return value of a method is an entity class, the result of the SQL execution is hydrated.

Entity classes should use constructor property promotion (recommended):

For multi-word database columns (snake_case), mix constructor property promotion with explicit assignment:

PHP 8.4+ readonly class:

Usage with database queries:

Benefits:

• Type safety with strict typing
• Immutability with readonly properties
• Better IDE support and refactoring
• No magic method overhead
• Clear snake_case ↔ camelCase mapping

Note: Constructor-less entities (with public properties) are discouraged as they lack type safety and immutability benefits of modern PHP.

Entity factory

To create an entity with a factory class, specify the factory class in the factory attribute.

The factory method of the factory class is called with the fetched data. You can also change the entity depending on the data.

If the factory method is not static, the factory class dependency resolution is performed.

Advanced Factory Usage

Factories enable powerful transformations beyond simple database mapping:

Add computed properties:

Transform data with injected services:

🏗️ Architecture Pattern: Ray.MediaQuery enables the Business Domain Repository Pattern (BDR Pattern) - an approach that transforms simple database queries into rich domain objects through dependency injection and business logic integration.

Web API

Web API functionality has been moved to a separate package.

For Web API queries, please see the ray/web-query package documentation.

Parameters

DateTime

You can pass a value object as a parameter. For example, you can specify a DateTimeInterface object like this.

The value will be converted to a date formatted string at SQL execution time.

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

Test clock

When testing, you can also use a single time binding for the DateTimeInterface, as shown below.

VO

If a value object other than DateTime is passed, the return value of the toScalar() method that implements the ToScalar interface or the __toString() method will be the argument.

Parameter Injection

Note that the default value of null for the value object argument is never used in SQL. If no value is passed, the scalar value of the value object injected with the parameter type will be used instead of null.

`

Input Object Flattening

When using Ray.InputQuery, objects with the #[Input] attribute are automatically flattened to SQL parameters. This allows for structured input while maintaining flat database queries.

The nested structure is flattened for SQL binding:

This feature provides:

• Type-safe input structures while keeping SQL simple
• Resilience to refactoring - object structure changes don't break SQL bindings
• Automatic parameter conversion - DateTime and other value objects are converted as usual

Note: Only objects with #[Input] attributes on their constructor parameters are flattened. Regular objects are passed through to the existing ParamConverter.

Pagination

The #[Pager] annotation allows paging of SELECT queries.

You can get the number of pages with count(), and you can get the page object with array access by page number. Pages is a SQL lazy execution object.

The number of items per page is specified by perPage, but for dynamic values, specify a string with the name of the argument representing the number of pages as follows

Use @return to specify hydration to the entity class.

SqlQuery

SqlQuery executes SQL by specifying the ID of the SQL file. It is used when detailed implementations with an implementation class.

Get* Method

To get the SELECT result, use get* method depending on the result you want to get.

Ray.MediaQuery contains the Ray.AuraSqlModule. If you need more lower layer operations, you can use Aura.Sql's Query Builder or Aura.Sql which extends PDO. doctrine/dbal is also available.

Profiler

Media accesses are logged by a logger. By default, a memory logger is bound to be used for testing.

Implement your own MediaQueryLoggerInterface and run You can also implement your own MediaQueryLoggerInterface to benchmark each media query and log it with the injected PSR logger.

SQL Template Configuration

You can customize the SQL logging format using the MediaQuerySqlTemplateModule. This module allows you to define a template for how SQL queries are formatted in logs.

Available template variables:

• {{ id }}: The identifier for the SQL query
• {{ sql }}: The SQL query string itself

Example output with custom template:

PerformSql Interface

For advanced SQL execution control, you can inject the PerformSqlInterface which provides direct access to the SQL execution layer.

Example)

• Injecting the ID of a logged-in user and leaving it as a comment statement in SQL.
• Leave bound values in comments or logs during development

Attributes

Testing Ray.MediaQuery

Here's how to install Ray.MediaQuery from the source and run the unit tests and demos.

PHP 8.4 Support and Aura.Sql

This library supports PHP 8.1 to 8.4. Aura.Sql has different major versions for different PHP versions:

• Aura.Sql v5.x: Recommended for PHP 8.1 - 8.3.
• Aura.Sql v6.x: Recommended for PHP 8.4 and newer.

Our composer.json specifies aura/sql: "^5 || ^6" to allow flexibility.

Important for PHP 8.4 users:

If you are using PHP 8.4, it is highly recommended to ensure Aura.Sql v6.x is installed. Due to how Composer resolves dependencies with --prefer-lowest, Aura.Sql v5.x (specifically older patch versions like 5.0.0 whose composer.json might not have an upper PHP bound like <8.4) might be installed on PHP 8.4 if you explicitly use --prefer-lowest or if other constraints lead to it. While our CI tests for PHP 8.4 with lowest dependencies are configured to force Aura.Sql v6, your local environment or specific project setup might differ.

To ensure Aura.Sql v6 is used on PHP 8.4, you can:

1. Run composer require aura/sql:"^6.0" in your project.
2. If aura/sql is already in your composer.json, ensure its constraint points to ^6.0 or a similar range that selects v6.