How to return a collection of data using Doctrine ResultSetMapping?

2

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 have been looking at some examples of how to use [Doctrine ResultSetMapping][1] , but due to my lack of knowledge about doctrine I have not yet succeeded.

Could anyone help me?

    
asked by anonymous 12.09.2018 / 18:53

1 answer

0

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

It's very simple. I recently answered a question that might help you, well 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());
    
12.09.2018 / 19:04