How manage a VIEW with Doctrine 2?

Question

I would like mapping a sql-view with Doctrine2.

This view is a TempTable containing some statistics that would show without rewriting the sql that generates the view

I try to map like a table, but updating schema drop the view and create a table

I try also with NativeSQL...

public function getMessages(\Project\Bundle\MyBundle\Entity\User $user) {
    $rsm = new \Doctrine\ORM\Query\ResultSetMapping();
    $rsm->addEntityResult('MessageCenter', 'v');
    $rsm->addFieldResult('v', 'user_id', 'user_id');
    $rsm->addFieldResult('v', 'tot', 'tot');
    $rsm->addFieldResult('v', 'read', 'read');
    $rsm->addFieldResult('v', 'to_read', 'to_read');
    $rsm->addFieldResult('v', 'stored', 'stored');
    $rsm->addFieldResult('v', 'spam', 'spam');

    $q = "SELECT * FROM message_stats_view WHERE user_id = ?";
    $rsm = new \Doctrine\ORM\Query\ResultSetMapping;
    $query = $this->getEntityManager()->createNativeQuery($q, $rsm);
    $query->setParameter(1, $user->getId());
    echo $query->getSQL();
    var_dump($query->execute());
    exit;
}

I create the entity MessageCenter with getter and setter, but my output is:

SELECT * FROM message_stats_view WHERE user_id = ?
array
  empty

btw: you are overwriting $rsm before using it.. – wolxXx Jul 13 '16 at 12:11 — wolxXx, Jul 13 '16 at 12:11

score 1 · Answer 1 · edited Mar 17 '17 at 10:37

(Answered by the OP in aq question edit. Transcribed to a community wiki answer. See Question with no answers, but issue solved in the comments (or extended in chat) )

The OP wrote:

I SOLVED!!!!

public function getCentroMessaggi(\Project\Bundle\MyBundle\Entity\User $user) {
    $connection = $this->getEntityManager()->getConnection();
    $q = "SELECT * FROM message_stats_view WHERE user_id = :id";
    $stmt = $connection->executeQuery($q, array('id' => $user->getId()));
    return $stmt->fetch();
}

This return an array. PERFECT!

How manage a VIEW with Doctrine 2?

1 Answers1