How to return a data collection using Doctrine Resultsetmapping?

Question

I have the following script that is functional:

SELECT
    r.coluna_1 as Coluna_1,
    e.coluna_1 as Coluna_2,
FROM produto r
INNER JOIN empresa e ON r.id_empresa = e.id_empresa
WHERE  r.id_tipo = 'N'
    AND r.data_devolucao <= :dataDevolucao
    AND e.id_empresa IS NOT NULL
    AND e.id_empresa = :empresa
    AND (select count(*) from produto_nota rn
                               where rn.id_nota_tipo = 14
                               and id_reserva = r.id_reserva) = 0

GROUP BY r.id_produto

I’ve been looking at some examples of how to use [Doctrine ResultSetMapping][1], but due to my low knowledge regarding Doctrine I have not yet succeeded.

Could someone help me?

Answer 1

For your case, you can use addScalarResult to map each column of the script return.

It’s very simple. I recently answered a question that might help you, right here.

In your case it would look like this:

$sqlFile = new \SplFileObject('caminho para o seu arquivo .sql');
$sql =  $sqlFile->openFile()->fread($sqlFile->getSize());

$rsm = new ResultSetMapping();
$rsm->addScalarResult('Coluna_1', 'Coluna_1');
$rsm->addScalarResult('Coluna_2', 'Coluna_1');
$rsm->addScalarResult('Coluna_3', 'Coluna_3'); // uma para cada, lembre-se!

$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter(':dataDevolucao', $data['dataDevolucao']);
$query->setParameter(':empresa', $data['empresa']);

var_dump($query->getResult());