PHPackages                             bancer/native-sql-mapper - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. bancer/native-sql-mapper

ActiveLibrary[Database & ORM](/categories/database)

bancer/native-sql-mapper
========================

A small CakePHP ORM-based library for hydrating raw SQL query results into entity graphs.

1.0.0(5mo ago)08MITPHPPHP >=7.4.0CI passing

Since Nov 28Pushed 5mo agoCompare

[ Source](https://github.com/bancer/native-sql-mapper)[ Packagist](https://packagist.org/packages/bancer/native-sql-mapper)[ RSS](/packages/bancer-native-sql-mapper/feed)WikiDiscussions main Synced 1mo ago

READMEChangelog (1)Dependencies (6)Versions (6)Used By (0)

native-sql-mapper
=================

[](#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:

```
Articles__id,
Articles__title,
Comments__id,
Comments__article_id,
Comments__content

```

will be converted into a fully hydrated entity objects.

---

🚀 Features
----------

[](#-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
--------------

[](#-installation)

Install via Composer:

```
composer require bancer/native-sql-mapper
```

---

🔧 Setup & Usage
-------------------

[](#-setup--usage)

### 1. Import the trait in your Table class

[](#1-import-the-trait-in-your-table-class)

```
use Bancer\NativeQueryMapper\ORM\NativeSQLMapperTrait;
```

### 2. Use the trait

[](#2-use-the-trait)

```
use NativeSQLMapperTrait;
```

### 3. Example usage

[](#3-example-usage)

```
$ArticlesTable = $this->fetchTable(ArticlesTable::class);
$stmt = $ArticlesTable->prepareNativeStatement("
    SELECT
        id     AS Articles__id,
        title  AS Articles__title
    FROM articles
    WHERE title = :title
");
$stmt->bindValue('title', 'My Article Title');
/** @var \App\Model\Entity\Article[] $entities */
$entities = $ArticlesTable->mapNativeStatement($stmt)->all();
```

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

---

🔁 hasMany Example Using Minimalistic SQL
----------------------------------------

[](#-hasmany-example-using-minimalistic-sql)

```
$stmt = $ArticlesTable->prepareNativeStatement("
    SELECT
        a.id        AS Articles__id,
        title       AS Articles__title,
        c.id        AS Comments__id,
        article_id  AS Comments__article_id,
        content     AS Comments__content
    FROM articles AS a
    LEFT JOIN comments AS c
        ON a.id=c.article_id
");
$entities = $ArticlesTable->mapNativeStatement($stmt)->all();
```

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

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

```
$entities = $ArticlesTable->find()
    ->select(['Articles.id', 'Articles.title'])
    ->contain([
        'Comments' => [
            'fields' => ['Comments.id', 'Comments.article_id', 'Comments.content'],
        ],
    ])
    ->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
-----------------------

[](#-belongstomany-example)

```
$ArticlesTable = $this->fetchTable(ArticlesTable::class);
$stmt = $ArticlesTable->prepareNativeStatement("
    SELECT
        Articles.id     AS Articles__id,
        Articles.title  AS Articles__title,
        Tags.id         AS Tags__id,
        Tags.name       AS Tags__name
    FROM articles AS Articles
    LEFT JOIN articles_tags AS ArticlesTags
        ON Articles.id=ArticlesTags.article_id
    LEFT JOIN tags AS Tags
        ON Tags.id=ArticlesTags.tag_id
");
$entities = $ArticlesTable->mapNativeStatement($stmt)->all();
```

You can find more examples in tests - .

### Mapping

[](#mapping)

---

🧠 How It Works
--------------

[](#-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
-----------------------------------------------

[](#-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

[](#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

[](#basic-example)

```
use Bancer\NativeQueryMapper\Database\InPlaceholders;

$statuses = new InPlaceholders('status', [1, 5, 9]);
$sql = mapNativeStatement($stmt)->all();
```

---

⚠️ Requirements
---------------

[](#️-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
-------------------------

[](#-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
----------

[](#️-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.

---

###  Health Score

32

—

LowBetter than 72% of packages

Maintenance72

Regular maintenance activity

Popularity4

Limited adoption so far

Community6

Small or concentrated contributor base

Maturity39

Early-stage or recently created project

 Bus Factor1

Top contributor holds 100% of commits — single point of failure

How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window.

**Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores.

**Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement.

**Maturity (30%)** — Project age, version count, PHP version support, and release stability.

###  Release Activity

Cadence

Every ~3 days

Total

2

Last Release

159d ago

### Community

Maintainers

![](https://www.gravatar.com/avatar/6ebb49f7218c6a5e34f840d066b8820cc054b6b7f43a7b2edbde9464f3df8289?d=identicon)[bancer](/maintainers/bancer)

---

Top Contributors

[![bancer](https://avatars.githubusercontent.com/u/3830106?v=4)](https://github.com/bancer "bancer (34 commits)")

###  Code Quality

TestsPHPUnit

Static AnalysisPHPStan

Code StylePHP\_CodeSniffer

Type Coverage Yes

### Embed Badge

![Health badge](/badges/bancer-native-sql-mapper/health.svg)

```
[![Health](https://phpackages.com/badges/bancer-native-sql-mapper/health.svg)](https://phpackages.com/packages/bancer-native-sql-mapper)
```

###  Alternatives

[robmorgan/phinx

Phinx makes it ridiculously easy to manage the database migrations for your PHP app.

4.5k46.2M405](/packages/robmorgan-phinx)[josegonzalez/cakephp-upload

CakePHP plugin to handle file uploading sans ridiculous automagic

5451.3M9](/packages/josegonzalez-cakephp-upload)[cakephp/migrations

Database Migration plugin for CakePHP

13912.0M222](/packages/cakephp-migrations)[muffin/trash

Adds soft delete support to CakePHP ORM tables.

851.3M11](/packages/muffin-trash)[muffin/webservice

Simplistic webservices for CakePHP

88191.0k13](/packages/muffin-webservice)[admad/cakephp-sequence

Sequence plugin for CakePHP to maintain ordered list of records

46489.9k6](/packages/admad-cakephp-sequence)

PHPackages © 2026

[Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)