SELECT CASE with DQL - Doctrine + Symfony 3

Question

Actually i have an table with some columns, but i need to filter some columns, those 3 i`ll need:

id | tipodemonstrativo | anoreferencia
---+-------------------+--------------
01 | AN                | 2015
02 | AN                | 2016
03 | SE                | 2014
04 | PB                | 2015

I did this with raw SQL

SELECT anoreferencia, 
    CASE tipodemonstrativo
        WHEN 'PB' THEN CONCAT(anoreferencia, '-03-31')
        WHEN 'SE' THEN CONCAT(anoreferencia, '-06-30')
        WHEN 'SB' THEN CONCAT(anoreferencia, '-09-30')
        WHEN 'AN' THEN CONCAT(anoreferencia, '-12-31')
    END AS referencia
FROM demonstrativofinanceiro
ORDER BY referencia DESC
LIMIT 4

It results something link this:

id | referencia
---+------------
01 | 2015-12-31
02 | 2016-12-31
03 | 2014-06-30
04 | 2015-03-31

I`m trying to reach same result with DQL approach, but with no success. I did this in raw SQL but gives an array:

    $em = $this->getDoctrine()->getManager();
    $query = "SELECT *,
        CASE tipodemonstrativo
            WHEN 'PB' THEN CONCAT(anoreferencia, '-03-31')
            WHEN 'SE' THEN CONCAT(anoreferencia, '-06-30')
            WHEN 'SB' THEN CONCAT(anoreferencia, '-09-30')
            WHEN 'AN' THEN CONCAT(anoreferencia, '-12-31')
        END AS referencia
    FROM demonstrativofinanceiro
    ORDER BY referencia DESC
    LIMIT 4";

    $stmt = $em->getConnection()->prepare($query);
    $stmt->execute();
    $result = $stmt->fetchAll();

But this result in an Array, and all my views was object ready... I`ll need change lots of pages if i query an array.

Can someone help me with this? Thanks!!

Answer 1

Shouldn't it be:

$query = $em->createQuery("SELECT *,
        CASE tipodemonstrativo
            WHEN 'PB' THEN CONCAT(anoreferencia, '-03-31')
            WHEN 'SE' THEN CONCAT(anoreferencia, '-06-30')
            WHEN 'SB' THEN CONCAT(anoreferencia, '-09-30')
            WHEN 'AN' THEN CONCAT(anoreferencia, '-12-31')
        END AS referencia
    FROM demonstrativofinanceiro
    ORDER BY referencia DESC
    LIMIT 4");
$result = $query->getResult();

Try that please.