Download the PHP package bancer/native-sql-mapper without Composer

native-sql-mapper

A lightweight extension for the CakePHP ORM that converts native SQL queries (executed through prepared PDO statements) into fully hydrated CakePHP entity graphs.

This library allows you to execute raw SQL while still benefiting from CakePHP’s entity system, associations, nested structures, and conventions. It supports deep associations, belongsToMany relations, junction data, nested mapping, and strict alias validation.

native-sql-mapper is ideal when:

• You need SQL performance or features that exceed the ORM’s query builder
• You want complex joins, window functions, CTEs, subqueries, aggregates
• You do not want to spend time on converting your SQL statements to query objects using CakePHP's query builder
• But still want CakePHP entities, patch-like hydration, and nested association graphs automatically built from the result set

Aliases such as:

will be converted into a fully hydrated entity objects.

🚀 Features

• Native SQL → real CakePHP entities
• Deep association support (belongsTo, hasMany, hasOne, belongsToMany)
• Automatic nested entity graph building
• Strict alias validation based on your ORM associations
• No configuration required — conventions are inferred
• Works with any SQL (CTEs, window functions, unions, etc.)

📦 Installation

Install via Composer:

🔧 Setup & Usage

1. Import the trait in your Table class

2. Use the trait

3. Example usage

$entities now contains hydrated Article entities based on the SQL result.

🔁 hasMany Example Using Minimalistic SQL

$entities now contains an array of Article objects with Comment objects as children.

Same as the result of reqular ->find()...->toArray():

Notice that FROM and JOIN clauses may use short or long aliases or no aliases at all (if the query does not use 'hasMany' or 'belongsToMany' associations) but all fields in SELECT clause must use aliases according to CakePHP naming convention {Alias}__{field_name}.

🔁 belongsToMany Example

You can find more examples in tests - https://github.com/bancer/native-sql-mapper/tree/develop/tests/TestCase/ORM.

Mapping

🧠 How It Works

• Aliases are parsed using CakePHP’s Alias__field naming convention
• Mapping is validated against real your ORM associations
• Deep nested associations are built recursively
• Only entities and associations that exist in your ORM are allowed

➕ BONUS: IN() placeholder helper for native SQL

When working with native SQL queries in CakePHP, PDO does not support binding arrays directly to IN (…) clauses. Each value must be expanded into its own placeholder and bound individually.

The InPlaceholders class provides a small, explicit helper that removes this boilerplate while keeping native SQL fully transparent and predictable.

What it does

InPlaceholders is a value object that:

• Generates named placeholders for use inside SQL IN (…) clauses
• Binds all values to a prepared statement safely
• Infers the correct PDO parameter type automatically (or accepts one explicitly)
• Fails fast on invalid input (empty prefix or empty value list)

There is no ORM magic involved — this works purely at the native SQL / PDO level.

Basic example

⚠️ Requirements

• Cake ORM 4.x or 5.x (or CakePHP 4.x or 5.x)
• PHP 7.4+ or 8.0+
• PDO database driver

📝 Notes & Limitations

• Aliases must follow CakePHP-style naming: Model__field.
• If SQL retrieves data from 'hasMany' or 'belongsToMany' associations then all primary columns must be present in SELECT clause
• Fields without valid aliases throw exceptions
• Associations must exist in the Table class, incorrect aliases throw exceptions
• Pagination must be handled manually
• This library is not a replacement of CakePHP query builder but a useful addition to it.

✔️ Summary

native-sql-mapper gives you the freedom of native SQL with the structure of CakePHP entities.
It fills the gap between raw PDO statements and the ORM — allowing complex SQL while preserving the integrity of your entity graphs.