Doctrine: Hydrate models from raw SQL

Question

I have the following custom query. I know its a simple one, so it can also be used as a DQL, but I have more complex ones too. But I want to know the way, how to do it even with more complex queries.

select j.*
from `shop`.`jobs` j
-- 2 joins
where j.`active` = true 
order by j.`priority` desc, j.`created` asc

Sure have a Job model.

What I want:

An array of instances from the class Job, using the a raw sql. Like this:

array (6) {
    [0] => Job#12 (8) {
      ...
    }
    [1] => Job#13 (8) {
      ...
    }
    [2] => Job#14 (8) {
      ...
    }
    [3] => Job#16 (8) {
      ...
    }
    [4] => Job#17 (8) {
      ...
    }
    [5] => Job#18 (8( {
      ...
    }
}

It is not clear to me what you are asking us here! Maybe is you showed us what this produces and then say what you actually want produced — RiggsFolly, Sep 02 '18 at 12:43
to improve your experience on SO please read how to ask an [On Topic question](https://stackoverflow.com/help/on-topic), and the [Question Check list](https://meta.stackoverflow.com/questions/260648/stack-overflow-question-checklist) and [the perfect question](http://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/) and how to create a [Minimal, Complete and Verifiable Example](http://stackoverflow.com/help/mcve) — RiggsFolly, Sep 02 '18 at 12:44

score 3 · Accepted Answer · edited Jul 14 '22 at 16:04

Found it! Through the EntityManager's createNativeQuery function. And for the RSM I need to use the ResultSetMappingBuilder class.

$rsm = new \Doctrine\ORM\Query\ResultSetMappingBuilder($this->getEntityManager());
$rsm->addRootEntityFromClassMetadata(\Path\To\Model::class, 'alias');

$nativeQuery = $this->getEntityManager()->createNativeQuery('-- query--', $rsm);

$nativeQuery->getResult();

Doctrine: Hydrate models from raw SQL

1 Answers1