Tuesday, July 31, 2012

Doctrine 2 execute raw sql

    public function foobar($foobar)
    {
      $stmt = $this->getEntityManager()
                   ->getConnection()
                   ->prepare('SELECT COUNT(id) AS num, foo FROM bar WHERE foobar = :foobar GROUP BY foo');
      $stmt->bindValue('foobar ', $foobar);
      $stmt->execute();
      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('
       SELECT
         qo.title as optionname,
         COUNT(qap.answer_id) as answers
      FROM
         quiz_answer_option qap
         INNER JOIN quizoption qo ON qo.id = qap.option_id
         INNER JOIN quizquestion qq ON qq.id = qo.quizquestion_id
      WHERE
         qq.active
         AND qq.id = :quizquestionid
      GROUP BY qap.option_id
      ORDER BY qo.number asc
        ', $rsm)
                        ->setParameter('quizquestionid', $quizquestion->getId())
                        ->getResult();

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

More than 3 requests, I'll translate this to Chinese.
超过3个请求,我就会把这篇文章翻译成中文。

No comments: