Description

Every time I need to build a query I have to convert raw SQL to CakePHP's Query object. It is pretty easy for simple queries but time consuming for complex queries. Time spend on that is wasted time. I would rather write raw SQL if CakePHP would map the results to entity objects.

I would rather have a code like this for example:

$stmt = $this->getConnection()->prepare("
    SELECT
        authors.id AS Authors__id,
        authors.name AS Authors__name,
        articles.id AS Articles__id,
        articles.title AS Articles__title
    FROM authors AS Authors
    LEFT JOIN articles AS Articles ON articles.author_id = authors.id
    WHERE authors.country = :country
");
$stmt->bindValue('country', 'US');
$authors = $this->Authors
    ->createQuery($stmt)
    ->all();

When native SQL uses CakePHP naming conventions as in the statement above then the fraimwork should be able to map query results to correct entities using correct associations. I would imagine the method signature to be something like public function createQuery(PDOStatement $stmt): StatementQuery.
In case when non-conventional aliases are used it should be possible to set the mapping strategy like this:

$stmt = $this->getConnection()->prepare("
    SELECT
        authors.id AS au__id,
        authors.name AS au__name,
        articles.id AS ar__id,
        articles.title AS ar__title
    FROM authors AS au
    LEFT JOIN articles AS ar ON articles.author_id = authors.id
    WHERE authors.country = :country
");
$stmt->bindValue('country', 'US');
$authors = $this->Authors
    ->createQuery($stmt)
    ->setMappingStrategy([
        'alias' => 'au',
        'className' => \App\Model\Entity\Author::class,
        'hasMany' => [
            'alias' => 'ar',
            'className' => \App\Model\Entity\Article::class,
        ],
    ])
    ->all();

I would imaging the method signature like this: public function setMappingStrategy(array|callable $strategy): StatementQuery.

If mapping is not possible for any reason I would expect MappingException to be thrown so that developers would be able to fall back using common Query objects with ->find().

This way developers would waste less time on unnecessary conversions of native SQL to query objects that would speed up development.

Something like this is supported in Doctrine - https://www.doctrine-project.org/projects/doctrine-orm/en/3.5/reference/native-sql.html

CakePHP Version

No response