Tuesday, July 31, 2012

Doctrine 2 execute raw sql

    public function foobar($foobar)
      $stmt = $this->getEntityManager()
                   ->prepare('SELECT COUNT(id) AS num, foo FROM bar WHERE foobar = :foobar GROUP BY foo');
      $stmt->bindValue('foobar ', $foobar);
      return $stmt->fetchAll();

Something like this is also doable - maybe a bit more complicated, but I like it better (notice how, via mapping, I can get "answers" as "count" and "optionname" as "option", which wouldn't work in the query itself as they're reserved words):

$rsm = new ResultSetMapping;
        $rsm->addScalarResult('optionname', 'option');
        $rsm->addScalarResult('answers', 'count');

        return $this->_em->createNativeQuery('
         qo.title as optionname,
         COUNT(qap.answer_id) as answers
         quiz_answer_option qap
         INNER JOIN quizoption qo ON qo.id = qap.option_id
         INNER JOIN quizquestion qq ON qq.id = qo.quizquestion_id
         AND qq.id = :quizquestionid
      GROUP BY qap.option_id
      ORDER BY qo.number asc
        ', $rsm)
                        ->setParameter('quizquestionid', $quizquestion->getId())

symfony framework forum • View topic - Doctrine 2: execute raw sql?

More than 3 requests, I'll translate this to Chinese.

No comments: